Data Migration

R

Roger

I have a situation where I have developed an Access application in which I
moved large datasets from an excel spreadsheet (I have several spanning over
an accounting period - 9-12 months).

The data comes in blocks of various sizes - so I can get the first block
with fields f1 to f12 or f1 to f20 and f1 to fn ( where n can be any whole
number).

The situation is this - I have written VBA code which loops through all the
blocks that I imported/pasted into the Access database and appends them to
one main table (via SQL code embedded in VBA code).

However as the blocks of data comes in various sizes the SQL code asks for a
field heading which doesn't exist in the particular block of data(e.g it
might search for field f10,but that block ranges from f1 to f9),the resulting
effect is that the SQL treats it as a Paramater query and asks for a suitable
entry.

My burning question is - How do I get rid of the dialog box (Paramater
query) which keeps popping up - I would like to run the code overnight but
that is difficult as I haven't found a way to get rid of the dialog box.

(Note:the workaround that I am using is to keep the "Enter key depressed"
with a solid object such as a stapler - but this leads to the code running
endlessly and leaves "huge " spaces in the VBA IDE ).

Any help out of my malaise will be gratefully appreciated.

Please refer to the generic code that I wrote :

'=====================================================
Function Import_Regulator



Dim obj As AccessObject, dbs As Object


Dim i As Integer
Dim x As Integer



Dim strB2U1 As String
Dim strB2U2 As String
Dim strB2U3 As String
Dim strB2U4 As String


Dim table1 As String
Dim table2 As String
Dim table3 As String
Dim table4 As String


Set dbs = Application.CurrentData

i = 0
x = 0





table1 = "ProuctTable1"
table2 = "ProuctTable2"
table3 = "ProuctTable3"
table4 = "ProuctTable4"

For i = 1 To 21




For Each obj In dbs.AllTables









strB2U1 = "INSERT INTO" & " " & "[" & table1
& "]" & " " & "( [Tracking Number], [Box No], [User Name], Date_Out )" _
& " " & " SELECT" & " " & "F" & i & "," & "
'" & obj.Name & "' " & " AS BoxNo, UserName() AS UName, IssueDate " _
& " " & " FROM" & " " & "[" & obj.Name & "]" _
& " " & " WHERE " & " " & "F" & i & " " &
"Like 'ab*';"


strB2U2 = "INSERT INTO" & " " & "[" &
table2 & "]" & " " & "( [Tracking Number], [Box No], [User Name], Date_Out )"
_
& " " & " SELECT" & " " & "F" & i & "," & "
'" & obj.Name & "' " & " AS BoxNo, UserName() AS UName, IssueDate " _
& " " & " FROM" & " " & "[" & obj.Name & "]" _
& " " & " WHERE " & " " & "F" & i & " " &
"Like 'xf*';"

strB2U3 = "INSERT INTO" & " " & "[" &
table3 & "]" & " " & "( [Tracking Number], [Box No], [User Name], Date_Out )"
_
& " " & " SELECT" & " " & "F" & i & "," & "
'" & obj.Name & "' " & " AS BoxNo, UserName() AS UName, IssueDate" _
& " " & " FROM" & " " & "[" & obj.Name & "]" _
& " " & " WHERE " & " " & "F" & i & " " &
"Like 'qw*';"

strB2U4 = "INSERT INTO" & " " & "[" &
table4 & "]" & " " & "( [Tracking Number], [Box No], [User Name], Date_Out )"
_
& " " & " SELECT" & " " & "F" & i & "," & "
'" & obj.Name & "' " & " AS BoxNo, UserName() AS UName, IssueDate " _
& " " & " FROM" & " " & "[" & obj.Name & "]" _
& " " & " WHERE " & " " & "F" & i & " " &
"Like 'up*';"




With DoCmd
.SetWarnings (False)

'

.RunSQL strB2U1
.RunSQL strB2U2
.RunSQL strB2U3
.RunSQL strB2U4



.SetWarnings (True)

End With







Next obj

Next i



End Function


'=====================================================


Regrds

Roger
 
J

Jeff Boyce

Roger

Is there a reason you are not able to simply link to the Excel data and use
a query in Access to "parse" it into well-normalized, more permanent Access
tables?

Regards

Jeff Boyce
Microsoft Access MVP


--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Roger said:
I have a situation where I have developed an Access application in which I
moved large datasets from an excel spreadsheet (I have several spanning
over
an accounting period - 9-12 months).

The data comes in blocks of various sizes - so I can get the first block
with fields f1 to f12 or f1 to f20 and f1 to fn ( where n can be any whole
number).

The situation is this - I have written VBA code which loops through all
the
blocks that I imported/pasted into the Access database and appends them to
one main table (via SQL code embedded in VBA code).

However as the blocks of data comes in various sizes the SQL code asks for
a
field heading which doesn't exist in the particular block of data(e.g it
might search for field f10,but that block ranges from f1 to f9),the
resulting
effect is that the SQL treats it as a Paramater query and asks for a
suitable
entry.

My burning question is - How do I get rid of the dialog box (Paramater
query) which keeps popping up - I would like to run the code overnight but
that is difficult as I haven't found a way to get rid of the dialog box.

(Note:the workaround that I am using is to keep the "Enter key depressed"
with a solid object such as a stapler - but this leads to the code running
endlessly and leaves "huge " spaces in the VBA IDE ).

Any help out of my malaise will be gratefully appreciated.

Please refer to the generic code that I wrote :

'=====================================================
Function Import_Regulator



Dim obj As AccessObject, dbs As Object


Dim i As Integer
Dim x As Integer



Dim strB2U1 As String
Dim strB2U2 As String
Dim strB2U3 As String
Dim strB2U4 As String


Dim table1 As String
Dim table2 As String
Dim table3 As String
Dim table4 As String


Set dbs = Application.CurrentData

i = 0
x = 0





table1 = "ProuctTable1"
table2 = "ProuctTable2"
table3 = "ProuctTable3"
table4 = "ProuctTable4"

For i = 1 To 21




For Each obj In dbs.AllTables









strB2U1 = "INSERT INTO" & " " & "[" &
table1
& "]" & " " & "( [Tracking Number], [Box No], [User Name], Date_Out )" _
& " " & " SELECT" & " " & "F" & i & "," & "
'" & obj.Name & "' " & " AS BoxNo, UserName() AS UName, IssueDate " _
& " " & " FROM" & " " & "[" & obj.Name &
"]" _
& " " & " WHERE " & " " & "F" & i & " " &
"Like 'ab*';"


strB2U2 = "INSERT INTO" & " " & "[" &
table2 & "]" & " " & "( [Tracking Number], [Box No], [User Name],
Date_Out )"
_
& " " & " SELECT" & " " & "F" & i & "," & "
'" & obj.Name & "' " & " AS BoxNo, UserName() AS UName, IssueDate " _
& " " & " FROM" & " " & "[" & obj.Name &
"]" _
& " " & " WHERE " & " " & "F" & i & " " &
"Like 'xf*';"

strB2U3 = "INSERT INTO" & " " & "[" &
table3 & "]" & " " & "( [Tracking Number], [Box No], [User Name],
Date_Out )"
_
& " " & " SELECT" & " " & "F" & i & "," & "
'" & obj.Name & "' " & " AS BoxNo, UserName() AS UName, IssueDate" _
& " " & " FROM" & " " & "[" & obj.Name &
"]" _
& " " & " WHERE " & " " & "F" & i & " " &
"Like 'qw*';"

strB2U4 = "INSERT INTO" & " " & "[" &
table4 & "]" & " " & "( [Tracking Number], [Box No], [User Name],
Date_Out )"
_
& " " & " SELECT" & " " & "F" & i & "," & "
'" & obj.Name & "' " & " AS BoxNo, UserName() AS UName, IssueDate " _
& " " & " FROM" & " " & "[" & obj.Name &
"]" _
& " " & " WHERE " & " " & "F" & i & " " &
"Like 'up*';"




With DoCmd
.SetWarnings (False)

'

.RunSQL strB2U1
.RunSQL strB2U2
.RunSQL strB2U3
.RunSQL strB2U4



.SetWarnings (True)

End With







Next obj

Next i



End Function


'=====================================================


Regrds

Roger
Click to expand...
 
D

Dale Fye

Agree with Jeff,

When I've done this in the past, I've linked each Excel file, then processed
its information, then moved it to another folder, then moved onto the next
Excel file.

With this method, you know how many fields were "linked", and check to see
if there is data in a particular column before trying to normalize it.

----
HTH
Dale



Roger said:
I have a situation where I have developed an Access application in which I
moved large datasets from an excel spreadsheet (I have several spanning over
an accounting period - 9-12 months).

The data comes in blocks of various sizes - so I can get the first block
with fields f1 to f12 or f1 to f20 and f1 to fn ( where n can be any whole
number).

The situation is this - I have written VBA code which loops through all the
blocks that I imported/pasted into the Access database and appends them to
one main table (via SQL code embedded in VBA code).

However as the blocks of data comes in various sizes the SQL code asks for a
field heading which doesn't exist in the particular block of data(e.g it
might search for field f10,but that block ranges from f1 to f9),the resulting
effect is that the SQL treats it as a Paramater query and asks for a suitable
entry.

My burning question is - How do I get rid of the dialog box (Paramater
query) which keeps popping up - I would like to run the code overnight but
that is difficult as I haven't found a way to get rid of the dialog box.

(Note:the workaround that I am using is to keep the "Enter key depressed"
with a solid object such as a stapler - but this leads to the code running
endlessly and leaves "huge " spaces in the VBA IDE ).

Any help out of my malaise will be gratefully appreciated.

Please refer to the generic code that I wrote :

'=====================================================
Function Import_Regulator



Dim obj As AccessObject, dbs As Object


Dim i As Integer
Dim x As Integer



Dim strB2U1 As String
Dim strB2U2 As String
Dim strB2U3 As String
Dim strB2U4 As String


Dim table1 As String
Dim table2 As String
Dim table3 As String
Dim table4 As String


Set dbs = Application.CurrentData

i = 0
x = 0





table1 = "ProuctTable1"
table2 = "ProuctTable2"
table3 = "ProuctTable3"
table4 = "ProuctTable4"

For i = 1 To 21




For Each obj In dbs.AllTables









strB2U1 = "INSERT INTO" & " " & "[" & table1
& "]" & " " & "( [Tracking Number], [Box No], [User Name], Date_Out )" _
& " " & " SELECT" & " " & "F" & i & "," & "
'" & obj.Name & "' " & " AS BoxNo, UserName() AS UName, IssueDate " _
& " " & " FROM" & " " & "[" & obj.Name & "]" _
& " " & " WHERE " & " " & "F" & i & " " &
"Like 'ab*';"


strB2U2 = "INSERT INTO" & " " & "[" &
table2 & "]" & " " & "( [Tracking Number], [Box No], [User Name], Date_Out )"
_
& " " & " SELECT" & " " & "F" & i & "," & "
'" & obj.Name & "' " & " AS BoxNo, UserName() AS UName, IssueDate " _
& " " & " FROM" & " " & "[" & obj.Name & "]" _
& " " & " WHERE " & " " & "F" & i & " " &
"Like 'xf*';"

strB2U3 = "INSERT INTO" & " " & "[" &
table3 & "]" & " " & "( [Tracking Number], [Box No], [User Name], Date_Out )"
_
& " " & " SELECT" & " " & "F" & i & "," & "
'" & obj.Name & "' " & " AS BoxNo, UserName() AS UName, IssueDate" _
& " " & " FROM" & " " & "[" & obj.Name & "]" _
& " " & " WHERE " & " " & "F" & i & " " &
"Like 'qw*';"

strB2U4 = "INSERT INTO" & " " & "[" &
table4 & "]" & " " & "( [Tracking Number], [Box No], [User Name], Date_Out )"
_
& " " & " SELECT" & " " & "F" & i & "," & "
'" & obj.Name & "' " & " AS BoxNo, UserName() AS UName, IssueDate " _
& " " & " FROM" & " " & "[" & obj.Name & "]" _
& " " & " WHERE " & " " & "F" & i & " " &
"Like 'up*';"




With DoCmd
.SetWarnings (False)

'

.RunSQL strB2U1
.RunSQL strB2U2
.RunSQL strB2U3
.RunSQL strB2U4



.SetWarnings (True)

End With







Next obj

Next i



End Function


'=====================================================


Regrds

Roger
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Rename forms into external DB 5
How to rename Modules ? 2
Reading Tag value of Forms 0
Can't delete table "You do not have the necessary permisions to us 2
Using .OpenAccessProject() 0
Data Type Conversion error 5
Delete an OLE Check Box in COde when a deleting a row 5
Programmatically renaming reports 3

Top