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

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
<(E-Mail Removed)> wrote:

>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 [Microsoft Access MVP]

Please respond in the newgroup and not by email.

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" wrote:

> 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
> <(E-Mail Removed)> wrote:
>
> >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 [Microsoft Access MVP]
>
> Please respond in the newgroup and not by email.
>

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
<(E-Mail Removed)> wrote:

>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" wrote:
>
>> 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
>> <(E-Mail Removed)> wrote:
>>
>> >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 [Microsoft Access MVP]
>>
>> Please respond in the newgroup and not by email.
>>

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

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.

"John Nurick" wrote:

> 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
> <(E-Mail Removed)> wrote:
>
> >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" wrote:
> >
> >> 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
> >> <(E-Mail Removed)> wrote:
> >>
> >> >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 [Microsoft Access MVP]
> >>
> >> Please respond in the newgroup and not by email.
> >>
>
> --
> John Nurick [Microsoft Access MVP]
>
> Please respond in the newgroup and not by email.
>

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
<(E-Mail Removed)> wrote:

>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.
>
>"John Nurick" wrote:
>
>> 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
>> <(E-Mail Removed)> wrote:
>>
>> >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" wrote:
>> >
>> >> 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
>> >> <(E-Mail Removed)> wrote:
>> >>
>> >> >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 [Microsoft Access MVP]
>> >>
>> >> Please respond in the newgroup and not by email.
>> >>
>>
>> --
>> John Nurick [Microsoft Access MVP]
>>
>> Please respond in the newgroup and not by email.
>>

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

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

"John Nurick" wrote:

> 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
> <(E-Mail Removed)> wrote:
>
> >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.
> >
> >"John Nurick" wrote:
> >
> >> 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
> >> <(E-Mail Removed)> wrote:
> >>
> >> >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" wrote:
> >> >
> >> >> 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
> >> >> <(E-Mail Removed)> wrote:
> >> >>
> >> >> >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 [Microsoft Access MVP]
> >> >>
> >> >> Please respond in the newgroup and not by email.
> >> >>
> >>
> >> --
> >> John Nurick [Microsoft Access MVP]
> >>
> >> Please respond in the newgroup and not by email.
> >>
>
> --
> John Nurick [Microsoft Access MVP]
>
> Please respond in the newgroup and not by email.
>

Sorry, I was asleep. it should be

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

or in short form

DBEngine(0)(0).Execute

On Thu, 10 Mar 2005 13:11:10 -0800, Dynath
<(E-Mail Removed)> wrote:

>When I leave the 0 after DBEngine the script fails sooner.
>
>"John Nurick" wrote:
>
>> 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
>> <(E-Mail Removed)> wrote:
>>
>> >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.
>> >
>> >"John Nurick" wrote:
>> >
>> >> 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
>> >> <(E-Mail Removed)> wrote:
>> >>
>> >> >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" wrote:
>> >> >
>> >> >> 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
>> >> >> <(E-Mail Removed)> wrote:
>> >> >>
>> >> >> >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 [Microsoft Access MVP]
>> >> >>
>> >> >> Please respond in the newgroup and not by email.
>> >> >>
>> >>
>> >> --
>> >> John Nurick [Microsoft Access MVP]
>> >>
>> >> Please respond in the newgroup and not by email.
>> >>
>>
>> --
>> John Nurick [Microsoft Access MVP]
>>
>> Please respond in the newgroup and not by email.
>>

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

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.

"John Nurick" wrote:

> Sorry, I was asleep. it should be
>
> DBEngine.Workspaces(0).Databases(0).Execute strSQL, dbFailOnError
>
> or in short form
>
> DBEngine(0)(0).Execute
>
> On Thu, 10 Mar 2005 13:11:10 -0800, Dynath
> <(E-Mail Removed)> wrote:
>
> >When I leave the 0 after DBEngine the script fails sooner.
> >
> >"John Nurick" wrote:
> >
> >> 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
> >> <(E-Mail Removed)> wrote:
> >>
> >> >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.
> >> >
> >> >"John Nurick" wrote:
> >> >
> >> >> 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
> >> >> <(E-Mail Removed)> wrote:
> >> >>
> >> >> >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" wrote:
> >> >> >
> >> >> >> 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
> >> >> >> <(E-Mail Removed)> wrote:
> >> >> >>
> >> >> >> >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 [Microsoft Access MVP]
> >> >> >>
> >> >> >> Please respond in the newgroup and not by email.
> >> >> >>
> >> >>
> >> >> --
> >> >> John Nurick [Microsoft Access MVP]
> >> >>
> >> >> Please respond in the newgroup and not by email.
> >> >>
> >>
> >> --
> >> John Nurick [Microsoft Access MVP]
> >>
> >> Please respond in the newgroup and not by email.
> >>
>
> --
> John Nurick [Microsoft Access MVP]
>
> Please respond in the newgroup and not by email.
>

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).


On Thu, 10 Mar 2005 16:39:04 -0800, Dynath
<(E-Mail Removed)> wrote:

>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.
>
>"John Nurick" wrote:
>
>> Sorry, I was asleep. it should be
>>
>> DBEngine.Workspaces(0).Databases(0).Execute strSQL, dbFailOnError
>>
>> or in short form
>>
>> DBEngine(0)(0).Execute
>>
>> On Thu, 10 Mar 2005 13:11:10 -0800, Dynath
>> <(E-Mail Removed)> wrote:
>>
>> >When I leave the 0 after DBEngine the script fails sooner.
>> >
>> >"John Nurick" wrote:
>> >
>> >> 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
>> >> <(E-Mail Removed)> wrote:
>> >>
>> >> >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.
>> >> >
>> >> >"John Nurick" wrote:
>> >> >
>> >> >> 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
>> >> >> <(E-Mail Removed)> wrote:
>> >> >>
>> >> >> >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" wrote:
>> >> >> >
>> >> >> >> 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
>> >> >> >> <(E-Mail Removed)> wrote:
>> >> >> >>
>> >> >> >> >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 [Microsoft Access MVP]
>> >> >> >>
>> >> >> >> Please respond in the newgroup and not by email.
>> >> >> >>
>> >> >>
>> >> >> --
>> >> >> John Nurick [Microsoft Access MVP]
>> >> >>
>> >> >> Please respond in the newgroup and not by email.
>> >> >>
>> >>
>> >> --
>> >> John Nurick [Microsoft Access MVP]
>> >>
>> >> Please respond in the newgroup and not by email.
>> >>
>>
>> --
>> John Nurick [Microsoft Access MVP]
>>
>> Please respond in the newgroup and not by email.
>>

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.