Appending a Group of Data.

[Guest]

What I am trying to do is take a large number of entries spread out in some 5
or 6 thousand databases stored in one folder, and put that data into a single
Table in my primary database. I've gotten as far as getting the form to
collect the data from the Databases and then it adds them in tables named
"Name, Name1, Name2, Etc." Can anyone tell me what I'm doing wrong? I
assume its something wrong with the Syntax of the DoCmd.TransferDatabase
comand at the end. Basically the form this goes in should ask me for a
location where the Databases are then ask me for a table name, and then just
append the data from the databases to the table, creating the table if needed.


Private Sub Command0_Click()
Dim InputDir, ImportFile As String
Dim tblName, Tableloc As String
Dim InputMsg As String
Dim PromMsg As String

InputMsg = "Type the pathname of the folder that contains "
InputMsg = InputMsg & "the files you want to import."
PromMsg = "type the table name to Append files To."
InputDir = InputBox(InputMsg)
Tableloc = InputBox(PromMsg)
' Change the file extension on the next line for the
' type of file you want to import.
ImportFile = Dir(InputDir & "\*.dbf")

Do While Len(ImportFile) > 0
' Use the import file name without its extension as the table
' name.
tblName = Tableloc
'tblName = Left(ImportFile, (InStr(1, ImportFile, ".") - 1))'
' Change dBase III on the next line to the type of file you
' want to import.
DoCmd.TransferDatabase acImport, "dBase III", InputDir, _
acTable, ImportFile, tblName
ImportFile = Dir
Loop
End Sub

 

[John Nurick]

Hi Dynath,

Are all the dbf files (which are not databases, but tables) in the
folder guaranteed to have exactly the same structure (field names and
types)?

 

[Guest]

Yes they are all structured the same, they are all produced by the
Government Printing Office from a single template. There are about 10 of them
released every week which is why I am trying to consolodate them. You are
correct they are simply tables, still there are to many of them to
practically link and I have to add new ones to regularly to do the proccess
manually. Each table has 5 feilds all are text with a limit of 255
characters.

Any ideas of where I went wrong??

 

[John Nurick]

I seldom need to use dbf files and don't know the ins and outs of why
new tables are created when there's a perfectly good existing one. But
the simplest thing is to avoid the problem by creating and executing a
SQL append query for each source table. Do this by replacing the
TransferDatabase call in your Do While Len(ImportFile) > 0 with
something like this:

strSQL = "INSERT INTO " & tblName & " SELECT * FROM " _
& "[dBASE III;Database=" & ImportDir & ";]." & ImportFile & ";"
DBEngine(0).Workspaces(0).Execute strSQL, dbFailOnError

The assembled SQL statemetn should look like this:

INSERT INTO ExistingTable
SELECT * FROM [dBASE III;Database=C:\Temp\;].File#dbf;

Note the # separating extension from file name; I'm not certain that
this is needed with DBF files.

 

[Guest]

So after applying your advice the VBA looks like this:

Private Sub Command0_Click()
Dim InputDir, ImportFile As String
Dim tblName, Tableloc As String
Dim InputMsg As String
Dim PromMsg As String

InputMsg = "Type the pathname of the folder that contains "
InputMsg = InputMsg & "the files you want to import."
PromMsg = "type the table name to Append files To."
InputDir = InputBox(InputMsg)
Tableloc = InputBox(PromMsg)
' Change the file extension on the next line for the
' type of file you want to import.
ImportFile = Dir(InputDir & "\*.dbf")

Do While Len(ImportFile) > 0
'Use table name variable to add the needed tables.
tblName = Tableloc
'tblName = Left(ImportFile, (InStr(1, ImportFile, ".") - 1))'
' Change dBase III on the next line to the type of file you
' want to import.
strSQL = "INSERT INTO " & tblName & " SELECT * FROM " _
& "[dBASE III;Database=" & ImportDir & ";]." & ImportFile & ";"
DBEngine.Workspaces(0).Execute strSQL, dbFailOnError
Loop
End Sub

Sadly though it fails on ".Execute" saying method or datamember not found.
Is there anything I have missed? I am not very familiar with running VBA to
produce SQL queries. And in all honesty I was avoiding SQL initially because
I know less about it than I do most other parts of databases.

I seldom need to use dbf files and don't know the ins and outs of why
new tables are created when there's a perfectly good existing one. But
the simplest thing is to avoid the problem by creating and executing a
SQL append query for each source table. Do this by replacing the
TransferDatabase call in your Do While Len(ImportFile) > 0 with
something like this:

strSQL = "INSERT INTO " & tblName & " SELECT * FROM " _
& "[dBASE III;Database=" & ImportDir & ";]." & ImportFile & ";"
DBEngine(0).Workspaces(0).Execute strSQL, dbFailOnError

The assembled SQL statemetn should look like this:

INSERT INTO ExistingTable
SELECT * FROM [dBASE III;Database=C:\Temp\;].File#dbf;

Note the # separating extension from file name; I'm not certain that
this is needed with DBF files.

Yes they are all structured the same, they are all produced by the
Government Printing Office from a single template. There are about 10 of them
released every week which is why I am trying to consolodate them. You are
correct they are simply tables, still there are to many of them to
practically link and I have to add new ones to regularly to do the proccess
manually. Each table has 5 feilds all are text with a limit of 255
characters.

Any ideas of where I went wrong??

 

[John Nurick]

There's something missing from the Execute line. It should be

DBEngine(0).Workspaces(0).Execute strSQL, dbFailOnError

So after applying your advice the VBA looks like this:

Private Sub Command0_Click()
Dim InputDir, ImportFile As String
Dim tblName, Tableloc As String
Dim InputMsg As String
Dim PromMsg As String

InputMsg = "Type the pathname of the folder that contains "
InputMsg = InputMsg & "the files you want to import."
PromMsg = "type the table name to Append files To."
InputDir = InputBox(InputMsg)
Tableloc = InputBox(PromMsg)
' Change the file extension on the next line for the
' type of file you want to import.
ImportFile = Dir(InputDir & "\*.dbf")

Do While Len(ImportFile) > 0
'Use table name variable to add the needed tables.
tblName = Tableloc
'tblName = Left(ImportFile, (InStr(1, ImportFile, ".") - 1))'
' Change dBase III on the next line to the type of file you
' want to import.
strSQL = "INSERT INTO " & tblName & " SELECT * FROM " _
& "[dBASE III;Database=" & ImportDir & ";]." & ImportFile & ";"
DBEngine.Workspaces(0).Execute strSQL, dbFailOnError
Loop
End Sub

Sadly though it fails on ".Execute" saying method or datamember not found.
Is there anything I have missed? I am not very familiar with running VBA to
produce SQL queries. And in all honesty I was avoiding SQL initially because
I know less about it than I do most other parts of databases.

I seldom need to use dbf files and don't know the ins and outs of why
new tables are created when there's a perfectly good existing one. But
the simplest thing is to avoid the problem by creating and executing a
SQL append query for each source table. Do this by replacing the
TransferDatabase call in your Do While Len(ImportFile) > 0 with
something like this:

strSQL = "INSERT INTO " & tblName & " SELECT * FROM " _
& "[dBASE III;Database=" & ImportDir & ";]." & ImportFile & ";"
DBEngine(0).Workspaces(0).Execute strSQL, dbFailOnError

The assembled SQL statemetn should look like this:

INSERT INTO ExistingTable
SELECT * FROM [dBASE III;Database=C:\Temp\;].File#dbf;

Note the # separating extension from file name; I'm not certain that
this is needed with DBF files.

Yes they are all structured the same, they are all produced by the
Government Printing Office from a single template. There are about 10 of them
released every week which is why I am trying to consolodate them. You are
correct they are simply tables, still there are to many of them to
practically link and I have to add new ones to regularly to do the proccess
manually. Each table has 5 feilds all are text with a limit of 255
characters.

Any ideas of where I went wrong??

:

Hi Dynath,

Are all the dbf files (which are not databases, but tables) in the
folder guaranteed to have exactly the same structure (field names and
types)?

On Tue, 1 Mar 2005 12:25:02 -0800, Dynath

What I am trying to do is take a large number of entries spread out in some 5
or 6 thousand databases stored in one folder, and put that data into a single
Table in my primary database. I've gotten as far as getting the form to
collect the data from the Databases and then it adds them in tables named
"Name, Name1, Name2, Etc." Can anyone tell me what I'm doing wrong? I
assume its something wrong with the Syntax of the DoCmd.TransferDatabase
comand at the end. Basically the form this goes in should ask me for a
location where the Databases are then ask me for a table name, and then just
append the data from the databases to the table, creating the table if needed.


Private Sub Command0_Click()
Dim InputDir, ImportFile As String
Dim tblName, Tableloc As String
Dim InputMsg As String
Dim PromMsg As String

InputMsg = "Type the pathname of the folder that contains "
InputMsg = InputMsg & "the files you want to import."
PromMsg = "type the table name to Append files To."
InputDir = InputBox(InputMsg)
Tableloc = InputBox(PromMsg)
' Change the file extension on the next line for the
' type of file you want to import.
ImportFile = Dir(InputDir & "\*.dbf")

Do While Len(ImportFile) > 0
' Use the import file name without its extension as the table
' name.
tblName = Tableloc
'tblName = Left(ImportFile, (InStr(1, ImportFile, ".") - 1))'
' Change dBase III on the next line to the type of file you
' want to import.
DoCmd.TransferDatabase acImport, "dBase III", InputDir, _
acTable, ImportFile, tblName
ImportFile = Dir
Loop
End Sub

 

[Guest]

When I leave the 0 after DBEngine the script fails sooner.

There's something missing from the Execute line. It should be

DBEngine(0).Workspaces(0).Execute strSQL, dbFailOnError

So after applying your advice the VBA looks like this:

Private Sub Command0_Click()
Dim InputDir, ImportFile As String
Dim tblName, Tableloc As String
Dim InputMsg As String
Dim PromMsg As String

InputMsg = "Type the pathname of the folder that contains "
InputMsg = InputMsg & "the files you want to import."
PromMsg = "type the table name to Append files To."
InputDir = InputBox(InputMsg)
Tableloc = InputBox(PromMsg)
' Change the file extension on the next line for the
' type of file you want to import.
ImportFile = Dir(InputDir & "\*.dbf")

Do While Len(ImportFile) > 0
'Use table name variable to add the needed tables.
tblName = Tableloc
'tblName = Left(ImportFile, (InStr(1, ImportFile, ".") - 1))'
' Change dBase III on the next line to the type of file you
' want to import.
strSQL = "INSERT INTO " & tblName & " SELECT * FROM " _
& "[dBASE III;Database=" & ImportDir & ";]." & ImportFile & ";"
DBEngine.Workspaces(0).Execute strSQL, dbFailOnError
Loop
End Sub

Sadly though it fails on ".Execute" saying method or datamember not found.
Is there anything I have missed? I am not very familiar with running VBA to
produce SQL queries. And in all honesty I was avoiding SQL initially because
I know less about it than I do most other parts of databases.

I seldom need to use dbf files and don't know the ins and outs of why
new tables are created when there's a perfectly good existing one. But
the simplest thing is to avoid the problem by creating and executing a
SQL append query for each source table. Do this by replacing the
TransferDatabase call in your Do While Len(ImportFile) > 0 with
something like this:

strSQL = "INSERT INTO " & tblName & " SELECT * FROM " _
& "[dBASE III;Database=" & ImportDir & ";]." & ImportFile & ";"
DBEngine(0).Workspaces(0).Execute strSQL, dbFailOnError

The assembled SQL statemetn should look like this:

INSERT INTO ExistingTable
SELECT * FROM [dBASE III;Database=C:\Temp\;].File#dbf;

Note the # separating extension from file name; I'm not certain that
this is needed with DBF files.


On Wed, 2 Mar 2005 13:37:06 -0800, Dynath

Yes they are all structured the same, they are all produced by the
Government Printing Office from a single template. There are about 10 of them
released every week which is why I am trying to consolodate them. You are
correct they are simply tables, still there are to many of them to
practically link and I have to add new ones to regularly to do the proccess
manually. Each table has 5 feilds all are text with a limit of 255
characters.

Any ideas of where I went wrong??

:

Hi Dynath,

Are all the dbf files (which are not databases, but tables) in the
folder guaranteed to have exactly the same structure (field names and
types)?

On Tue, 1 Mar 2005 12:25:02 -0800, Dynath

What I am trying to do is take a large number of entries spread out in some 5
or 6 thousand databases stored in one folder, and put that data into a single
Table in my primary database. I've gotten as far as getting the form to
collect the data from the Databases and then it adds them in tables named
"Name, Name1, Name2, Etc." Can anyone tell me what I'm doing wrong? I
assume its something wrong with the Syntax of the DoCmd.TransferDatabase
comand at the end. Basically the form this goes in should ask me for a
location where the Databases are then ask me for a table name, and then just
append the data from the databases to the table, creating the table if needed.


Private Sub Command0_Click()
Dim InputDir, ImportFile As String
Dim tblName, Tableloc As String
Dim InputMsg As String
Dim PromMsg As String

InputMsg = "Type the pathname of the folder that contains "
InputMsg = InputMsg & "the files you want to import."
PromMsg = "type the table name to Append files To."
InputDir = InputBox(InputMsg)
Tableloc = InputBox(PromMsg)
' Change the file extension on the next line for the
' type of file you want to import.
ImportFile = Dir(InputDir & "\*.dbf")

Do While Len(ImportFile) > 0
' Use the import file name without its extension as the table
' name.
tblName = Tableloc
'tblName = Left(ImportFile, (InStr(1, ImportFile, ".") - 1))'
' Change dBase III on the next line to the type of file you
' want to import.
DoCmd.TransferDatabase acImport, "dBase III", InputDir, _
acTable, ImportFile, tblName
ImportFile = Dir
Loop
End Sub

 

[John Nurick]

Sorry, I was asleep. it should be

DBEngine.Workspaces(0).Databases(0).Execute strSQL, dbFailOnError

or in short form

DBEngine(0)(0).Execute

When I leave the 0 after DBEngine the script fails sooner.

There's something missing from the Execute line. It should be

DBEngine(0).Workspaces(0).Execute strSQL, dbFailOnError

So after applying your advice the VBA looks like this:

Private Sub Command0_Click()
Dim InputDir, ImportFile As String
Dim tblName, Tableloc As String
Dim InputMsg As String
Dim PromMsg As String

InputMsg = "Type the pathname of the folder that contains "
InputMsg = InputMsg & "the files you want to import."
PromMsg = "type the table name to Append files To."
InputDir = InputBox(InputMsg)
Tableloc = InputBox(PromMsg)
' Change the file extension on the next line for the
' type of file you want to import.
ImportFile = Dir(InputDir & "\*.dbf")

Do While Len(ImportFile) > 0
'Use table name variable to add the needed tables.
tblName = Tableloc
'tblName = Left(ImportFile, (InStr(1, ImportFile, ".") - 1))'
' Change dBase III on the next line to the type of file you
' want to import.
strSQL = "INSERT INTO " & tblName & " SELECT * FROM " _
& "[dBASE III;Database=" & ImportDir & ";]." & ImportFile & ";"
DBEngine.Workspaces(0).Execute strSQL, dbFailOnError
Loop
End Sub

Sadly though it fails on ".Execute" saying method or datamember not found.
Is there anything I have missed? I am not very familiar with running VBA to
produce SQL queries. And in all honesty I was avoiding SQL initially because
I know less about it than I do most other parts of databases.

:

I seldom need to use dbf files and don't know the ins and outs of why
new tables are created when there's a perfectly good existing one. But
the simplest thing is to avoid the problem by creating and executing a
SQL append query for each source table. Do this by replacing the
TransferDatabase call in your Do While Len(ImportFile) > 0 with
something like this:

strSQL = "INSERT INTO " & tblName & " SELECT * FROM " _
& "[dBASE III;Database=" & ImportDir & ";]." & ImportFile & ";"
DBEngine(0).Workspaces(0).Execute strSQL, dbFailOnError

The assembled SQL statemetn should look like this:

INSERT INTO ExistingTable
SELECT * FROM [dBASE III;Database=C:\Temp\;].File#dbf;

Note the # separating extension from file name; I'm not certain that
this is needed with DBF files.


On Wed, 2 Mar 2005 13:37:06 -0800, Dynath

Yes they are all structured the same, they are all produced by the
Government Printing Office from a single template. There are about 10 of them
released every week which is why I am trying to consolodate them. You are
correct they are simply tables, still there are to many of them to
practically link and I have to add new ones to regularly to do the proccess
manually. Each table has 5 feilds all are text with a limit of 255
characters.

Any ideas of where I went wrong??

:

Hi Dynath,

Are all the dbf files (which are not databases, but tables) in the
folder guaranteed to have exactly the same structure (field names and
types)?

On Tue, 1 Mar 2005 12:25:02 -0800, Dynath

What I am trying to do is take a large number of entries spread out in some 5
or 6 thousand databases stored in one folder, and put that data into a single
Table in my primary database. I've gotten as far as getting the form to
collect the data from the Databases and then it adds them in tables named
"Name, Name1, Name2, Etc." Can anyone tell me what I'm doing wrong? I
assume its something wrong with the Syntax of the DoCmd.TransferDatabase
comand at the end. Basically the form this goes in should ask me for a
location where the Databases are then ask me for a table name, and then just
append the data from the databases to the table, creating the table if needed.


Private Sub Command0_Click()
Dim InputDir, ImportFile As String
Dim tblName, Tableloc As String
Dim InputMsg As String
Dim PromMsg As String

InputMsg = "Type the pathname of the folder that contains "
InputMsg = InputMsg & "the files you want to import."
PromMsg = "type the table name to Append files To."
InputDir = InputBox(InputMsg)
Tableloc = InputBox(PromMsg)
' Change the file extension on the next line for the
' type of file you want to import.
ImportFile = Dir(InputDir & "\*.dbf")

Do While Len(ImportFile) > 0
' Use the import file name without its extension as the table
' name.
tblName = Tableloc
'tblName = Left(ImportFile, (InStr(1, ImportFile, ".") - 1))'
' Change dBase III on the next line to the type of file you
' want to import.
DoCmd.TransferDatabase acImport, "dBase III", InputDir, _
acTable, ImportFile, tblName
ImportFile = Dir
Loop
End Sub

 

[Guest]

Ah, well that solved the error messages, but... Um, nothing is actually being
appended by this. As far as I can tell this code compiles an SQL statement
for each DBF am I correct? Then the Execute strSQL should run that statement
before it moves on to the next DBF. Nothing is actually being added to the
table in the Access database when I run this though.

Sorry, I was asleep. it should be

DBEngine.Workspaces(0).Databases(0).Execute strSQL, dbFailOnError

or in short form

DBEngine(0)(0).Execute

When I leave the 0 after DBEngine the script fails sooner.

There's something missing from the Execute line. It should be

DBEngine(0).Workspaces(0).Execute strSQL, dbFailOnError

On Wed, 9 Mar 2005 15:11:02 -0800, Dynath

So after applying your advice the VBA looks like this:

Private Sub Command0_Click()
Dim InputDir, ImportFile As String
Dim tblName, Tableloc As String
Dim InputMsg As String
Dim PromMsg As String

InputMsg = "Type the pathname of the folder that contains "
InputMsg = InputMsg & "the files you want to import."
PromMsg = "type the table name to Append files To."
InputDir = InputBox(InputMsg)
Tableloc = InputBox(PromMsg)
' Change the file extension on the next line for the
' type of file you want to import.
ImportFile = Dir(InputDir & "\*.dbf")

Do While Len(ImportFile) > 0
'Use table name variable to add the needed tables.
tblName = Tableloc
'tblName = Left(ImportFile, (InStr(1, ImportFile, ".") - 1))'
' Change dBase III on the next line to the type of file you
' want to import.
strSQL = "INSERT INTO " & tblName & " SELECT * FROM " _
& "[dBASE III;Database=" & ImportDir & ";]." & ImportFile & ";"
DBEngine.Workspaces(0).Execute strSQL, dbFailOnError
Loop
End Sub

Sadly though it fails on ".Execute" saying method or datamember not found.
Is there anything I have missed? I am not very familiar with running VBA to
produce SQL queries. And in all honesty I was avoiding SQL initially because
I know less about it than I do most other parts of databases.

:

I seldom need to use dbf files and don't know the ins and outs of why
new tables are created when there's a perfectly good existing one. But
the simplest thing is to avoid the problem by creating and executing a
SQL append query for each source table. Do this by replacing the
TransferDatabase call in your Do While Len(ImportFile) > 0 with
something like this:

strSQL = "INSERT INTO " & tblName & " SELECT * FROM " _
& "[dBASE III;Database=" & ImportDir & ";]." & ImportFile & ";"
DBEngine(0).Workspaces(0).Execute strSQL, dbFailOnError

The assembled SQL statemetn should look like this:

INSERT INTO ExistingTable
SELECT * FROM [dBASE III;Database=C:\Temp\;].File#dbf;

Note the # separating extension from file name; I'm not certain that
this is needed with DBF files.


On Wed, 2 Mar 2005 13:37:06 -0800, Dynath

Yes they are all structured the same, they are all produced by the
Government Printing Office from a single template. There are about 10 of them
released every week which is why I am trying to consolodate them. You are
correct they are simply tables, still there are to many of them to
practically link and I have to add new ones to regularly to do the proccess
manually. Each table has 5 feilds all are text with a limit of 255
characters.

Any ideas of where I went wrong??

:

Hi Dynath,

Are all the dbf files (which are not databases, but tables) in the
folder guaranteed to have exactly the same structure (field names and
types)?

On Tue, 1 Mar 2005 12:25:02 -0800, Dynath

What I am trying to do is take a large number of entries spread out in some 5
or 6 thousand databases stored in one folder, and put that data into a single
Table in my primary database. I've gotten as far as getting the form to
collect the data from the Databases and then it adds them in tables named
"Name, Name1, Name2, Etc." Can anyone tell me what I'm doing wrong? I
assume its something wrong with the Syntax of the DoCmd.TransferDatabase
comand at the end. Basically the form this goes in should ask me for a
location where the Databases are then ask me for a table name, and then just
append the data from the databases to the table, creating the table if needed.


Private Sub Command0_Click()
Dim InputDir, ImportFile As String
Dim tblName, Tableloc As String
Dim InputMsg As String
Dim PromMsg As String

InputMsg = "Type the pathname of the folder that contains "
InputMsg = InputMsg & "the files you want to import."
PromMsg = "type the table name to Append files To."
InputDir = InputBox(InputMsg)
Tableloc = InputBox(PromMsg)
' Change the file extension on the next line for the
' type of file you want to import.
ImportFile = Dir(InputDir & "\*.dbf")

Do While Len(ImportFile) > 0
' Use the import file name without its extension as the table
' name.
tblName = Tableloc
'tblName = Left(ImportFile, (InStr(1, ImportFile, ".") - 1))'
' Change dBase III on the next line to the type of file you
' want to import.
DoCmd.TransferDatabase acImport, "dBase III", InputDir, _
acTable, ImportFile, tblName
ImportFile = Dir
Loop
End Sub

 

[John Nurick]

It's time for some ordinary debugging, then.

Step through the code, look at the SQL statements that are being
generated, make sure that they make sense. Are the table names and file
names correct? Do the field names in the DBF files match those in your
table?

Try copying the SELECT clause of one of them and pasting it into the
query designer in SQL view; does it return the expected records?

ALso, do your DBF files or folders have long names? If so, it may be
necessary to substitute the equivalent short names (there's a method in
the FileSystemObject object for doing this).

Ah, well that solved the error messages, but... Um, nothing is actually being
appended by this. As far as I can tell this code compiles an SQL statement
for each DBF am I correct? Then the Execute strSQL should run that statement
before it moves on to the next DBF. Nothing is actually being added to the
table in the Access database when I run this though.

Sorry, I was asleep. it should be

DBEngine.Workspaces(0).Databases(0).Execute strSQL, dbFailOnError

or in short form

DBEngine(0)(0).Execute

When I leave the 0 after DBEngine the script fails sooner.

:

There's something missing from the Execute line. It should be

DBEngine(0).Workspaces(0).Execute strSQL, dbFailOnError

On Wed, 9 Mar 2005 15:11:02 -0800, Dynath

So after applying your advice the VBA looks like this:

Private Sub Command0_Click()
Dim InputDir, ImportFile As String
Dim tblName, Tableloc As String
Dim InputMsg As String
Dim PromMsg As String

InputMsg = "Type the pathname of the folder that contains "
InputMsg = InputMsg & "the files you want to import."
PromMsg = "type the table name to Append files To."
InputDir = InputBox(InputMsg)
Tableloc = InputBox(PromMsg)
' Change the file extension on the next line for the
' type of file you want to import.
ImportFile = Dir(InputDir & "\*.dbf")

Do While Len(ImportFile) > 0
'Use table name variable to add the needed tables.
tblName = Tableloc
'tblName = Left(ImportFile, (InStr(1, ImportFile, ".") - 1))'
' Change dBase III on the next line to the type of file you
' want to import.
strSQL = "INSERT INTO " & tblName & " SELECT * FROM " _
& "[dBASE III;Database=" & ImportDir & ";]." & ImportFile & ";"
DBEngine.Workspaces(0).Execute strSQL, dbFailOnError
Loop
End Sub

Sadly though it fails on ".Execute" saying method or datamember not found.
Is there anything I have missed? I am not very familiar with running VBA to
produce SQL queries. And in all honesty I was avoiding SQL initially because
I know less about it than I do most other parts of databases.

:

I seldom need to use dbf files and don't know the ins and outs of why
new tables are created when there's a perfectly good existing one. But
the simplest thing is to avoid the problem by creating and executing a
SQL append query for each source table. Do this by replacing the
TransferDatabase call in your Do While Len(ImportFile) > 0 with
something like this:

strSQL = "INSERT INTO " & tblName & " SELECT * FROM " _
& "[dBASE III;Database=" & ImportDir & ";]." & ImportFile & ";"
DBEngine(0).Workspaces(0).Execute strSQL, dbFailOnError

The assembled SQL statemetn should look like this:

INSERT INTO ExistingTable
SELECT * FROM [dBASE III;Database=C:\Temp\;].File#dbf;

Note the # separating extension from file name; I'm not certain that
this is needed with DBF files.


On Wed, 2 Mar 2005 13:37:06 -0800, Dynath

Yes they are all structured the same, they are all produced by the
Government Printing Office from a single template. There are about 10 of them
released every week which is why I am trying to consolodate them. You are
correct they are simply tables, still there are to many of them to
practically link and I have to add new ones to regularly to do the proccess
manually. Each table has 5 feilds all are text with a limit of 255
characters.

Any ideas of where I went wrong??

:

Hi Dynath,

Are all the dbf files (which are not databases, but tables) in the
folder guaranteed to have exactly the same structure (field names and
types)?

On Tue, 1 Mar 2005 12:25:02 -0800, Dynath

What I am trying to do is take a large number of entries spread out in some 5
or 6 thousand databases stored in one folder, and put that data into a single
Table in my primary database. I've gotten as far as getting the form to
collect the data from the Databases and then it adds them in tables named
"Name, Name1, Name2, Etc." Can anyone tell me what I'm doing wrong? I
assume its something wrong with the Syntax of the DoCmd.TransferDatabase
comand at the end. Basically the form this goes in should ask me for a
location where the Databases are then ask me for a table name, and then just
append the data from the databases to the table, creating the table if needed.


Private Sub Command0_Click()
Dim InputDir, ImportFile As String
Dim tblName, Tableloc As String
Dim InputMsg As String
Dim PromMsg As String

InputMsg = "Type the pathname of the folder that contains "
InputMsg = InputMsg & "the files you want to import."
PromMsg = "type the table name to Append files To."
InputDir = InputBox(InputMsg)
Tableloc = InputBox(PromMsg)
' Change the file extension on the next line for the
' type of file you want to import.
ImportFile = Dir(InputDir & "\*.dbf")

Do While Len(ImportFile) > 0
' Use the import file name without its extension as the table
' name.
tblName = Tableloc
'tblName = Left(ImportFile, (InStr(1, ImportFile, ".") - 1))'
' Change dBase III on the next line to the type of file you
' want to import.
DoCmd.TransferDatabase acImport, "dBase III", InputDir, _
acTable, ImportFile, tblName
ImportFile = Dir
Loop
End Sub