help to bulk import and append - newbie, Access 2007

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am working with 8 groups of 51 dbase files that are all in a trusted
location. My plan is to create one table per group, which means importing 408
dBase IV (I’m guessing the version) files as tables and appending 400 of the
tables to their respective 1st table. I’ve already imported the first two
files for each group to get the file structure and practice appending. I want
to do the rest in batch mode. I don’t know VBA or Access macros, though I
have worked with SPlus scripts & beaucoup dos batch files.

For the VBA, I found an example & have adapted it to import database data. I
first tried to work out the core of the macro, TransferData:
-------
Sub Import()

Dim dbs As Database

Set dbs = CurrentDb
DoCmd.TransferDatabase(acImport, "dBASE IV",
"C:\LSAY\Census\TabData\1990SF3\cd90_3A\vars01\", "stf305ar.dbf",,
"stf305ar", No)

End Sub
--------
My error messages are ‘compile error, syntax error’ and ‘compile error,
expected: =’. I don’t know what’s wrong. I must have left something out that
requires an ‘=’, but I don’t know what.

The macro is straightforward up to a point (have model sub-macro per group),
but … how do I expand each sub-macro to include the 48 other files? (or do I
have to create 400 more sub-groups?) Is there a way to append each file to
its base table within the same macro so that I don’t have to figure out how
to batch serial queries?

Here is the bulk import code (borrowed from Kirk, whose URL I didn’t keep).
Am I on the right track? Can I include an Append action in the same process?
(I don’t see anything about ‘append’ in the do.cmd list)
----------
Sub MassImport()

Dim strPath As String
Dim strTableName As String
Dim strFileName As String
Dim dbs As Database

Set dbs = CurrentDb
' Set the path to the directory where the files will be.
strPath = "C:\LSAY\Census\TabData\1990SF3\cd90_3A\vars01\"
strTableName = Dir(strPath) ' Retrieve the first entry table name.
strFileName = Dir(strPath & ".dbf") ' Retrieve the first entry file name.
Do While strFileName <> "" ' Start the loop.
DoCmd.TransferDatabase (acImport, "dBase IV", strPath,, strFileName,
strTableName, No)
strFileName = Dir ' Get next entry.
Loop

End Sub
-----------
 
Marian

Uhmmm...

Are each of those "groups" unique? That is, are all the data elements the
same in all the groups? If so, don't use separate tables for groups (that's
soooo spreadsheetly!). Instead, use a single table with all the data
elements, then add [Group] as a final data element. When you append records
into this new table, just make sure you note which group the records are
coming from.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Unfortunately, each group is unique -- good old US census data. I =could=
join the 8 tables after all the appending gets done, but I only need certain
fields out of each group for any one set of variables I plan to construct. My
problem is with the 'automation'. Is it possible?

Jeff Boyce said:
Marian

Uhmmm...

Are each of those "groups" unique? That is, are all the data elements the
same in all the groups? If so, don't use separate tables for groups (that's
soooo spreadsheetly!). Instead, use a single table with all the data
elements, then add [Group] as a final data element. When you append records
into this new table, just make sure you note which group the records are
coming from.

Regards

Jeff Boyce
Microsoft Office/Access MVP


Marian M. said:
I am working with 8 groups of 51 dbase files that are all in a trusted
location. My plan is to create one table per group, which means importing
408
dBase IV (I'm guessing the version) files as tables and appending 400 of
the
tables to their respective 1st table. I've already imported the first two
files for each group to get the file structure and practice appending. I
want
to do the rest in batch mode. I don't know VBA or Access macros, though I
have worked with SPlus scripts & beaucoup dos batch files.

For the VBA, I found an example & have adapted it to import database data.
I
first tried to work out the core of the macro, TransferData:
-------
Sub Import()

Dim dbs As Database

Set dbs = CurrentDb
DoCmd.TransferDatabase(acImport, "dBASE IV",
"C:\LSAY\Census\TabData\1990SF3\cd90_3A\vars01\", "stf305ar.dbf",,
"stf305ar", No)

End Sub
--------
My error messages are 'compile error, syntax error' and 'compile error,
expected: ='. I don't know what's wrong. I must have left something out
that
requires an '=', but I don't know what.

The macro is straightforward up to a point (have model sub-macro per
group),
but . how do I expand each sub-macro to include the 48 other files? (or do
I
have to create 400 more sub-groups?) Is there a way to append each file to
its base table within the same macro so that I don't have to figure out
how
to batch serial queries?

Here is the bulk import code (borrowed from Kirk, whose URL I didn't
keep).
Am I on the right track? Can I include an Append action in the same
process?
(I don't see anything about 'append' in the do.cmd list)
----------
Sub MassImport()

Dim strPath As String
Dim strTableName As String
Dim strFileName As String
Dim dbs As Database

Set dbs = CurrentDb
' Set the path to the directory where the files will be.
strPath = "C:\LSAY\Census\TabData\1990SF3\cd90_3A\vars01\"
strTableName = Dir(strPath) ' Retrieve the first entry table name.
strFileName = Dir(strPath & ".dbf") ' Retrieve the first entry file
name.
Do While strFileName <> "" ' Start the loop.
DoCmd.TransferDatabase (acImport, "dBase IV", strPath,, strFileName,
strTableName, No)
strFileName = Dir ' Get next entry.
Loop

End Sub
Click to expand...
Click to expand...
 
Marian

What happens when you run what you have?

Here's the approach I use... (would use for an "automation" project) (I'd
pretty much have to be in your situation, with your machines, and your code,
and run it to see if it works).

Build code that correctly handles one.

Build code that correctly "gets" all.

Run the combination (as TEST) ... confirm results.

Run the combination (as PRODUCTION).

Is this a one-time effort, or will you need to do this over and over again?
If one time, would it take you less time to do it "manually" than work out
the code and trouble-shooting, and final version?

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Marian M. said:
Unfortunately, each group is unique -- good old US census data. I =could=
join the 8 tables after all the appending gets done, but I only need
certain
fields out of each group for any one set of variables I plan to construct.
My
problem is with the 'automation'. Is it possible?

Jeff Boyce said:
Marian

Uhmmm...

Are each of those "groups" unique? That is, are all the data elements
the
same in all the groups? If so, don't use separate tables for groups
(that's
soooo spreadsheetly!). Instead, use a single table with all the data
elements, then add [Group] as a final data element. When you append
records
into this new table, just make sure you note which group the records are
coming from.

Regards

Jeff Boyce
Microsoft Office/Access MVP


Marian M. said:
I am working with 8 groups of 51 dbase files that are all in a trusted
location. My plan is to create one table per group, which means
importing
408
dBase IV (I'm guessing the version) files as tables and appending 400
of
the
tables to their respective 1st table. I've already imported the first
two
files for each group to get the file structure and practice appending.
I
want
to do the rest in batch mode. I don't know VBA or Access macros, though
I
have worked with SPlus scripts & beaucoup dos batch files.

For the VBA, I found an example & have adapted it to import database
data.
I
first tried to work out the core of the macro, TransferData:
-------
Sub Import()

Dim dbs As Database

Set dbs = CurrentDb
DoCmd.TransferDatabase(acImport, "dBASE IV",
"C:\LSAY\Census\TabData\1990SF3\cd90_3A\vars01\", "stf305ar.dbf",,
"stf305ar", No)

End Sub
--------
My error messages are 'compile error, syntax error' and 'compile error,
expected: ='. I don't know what's wrong. I must have left something out
that
requires an '=', but I don't know what.

The macro is straightforward up to a point (have model sub-macro per
group),
but . how do I expand each sub-macro to include the 48 other files? (or
do
I
have to create 400 more sub-groups?) Is there a way to append each file
to
its base table within the same macro so that I don't have to figure out
how
to batch serial queries?

Here is the bulk import code (borrowed from Kirk, whose URL I didn't
keep).
Am I on the right track? Can I include an Append action in the same
process?
(I don't see anything about 'append' in the do.cmd list)
----------
Sub MassImport()

Dim strPath As String
Dim strTableName As String
Dim strFileName As String
Dim dbs As Database

Set dbs = CurrentDb
' Set the path to the directory where the files will be.
strPath = "C:\LSAY\Census\TabData\1990SF3\cd90_3A\vars01\"
strTableName = Dir(strPath) ' Retrieve the first entry table name.
strFileName = Dir(strPath & ".dbf") ' Retrieve the first entry file
name.
Do While strFileName <> "" ' Start the loop.
DoCmd.TransferDatabase (acImport, "dBase IV", strPath,,
strFileName,
strTableName, No)
strFileName = Dir ' Get next entry.
Loop

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Hi Jeff,
Yes, I will have to do this more than once with different sets of file
groups. I started trying to adapt the VBA code I found and realized I had to
figure out how to automate for importing one specific database. Here is my
attempt:
-------
Sub Import()

Dim dbs As Database

Set dbs = CurrentDb
DoCmd.TransferDatabase(acImport, "dBASE IV",
"C:\LSAY\Census\TabData\1990SF3\cd90_3A\vars01\", ,"stf305ar.dbf",,
"stf305ar", No)

End Sub
--------
I have taken expression.TransferDatabase(TransferType, DatabaseType,
DatabaseName, ObjectType, Source, Destination, StructureOnly, StoreLogin) and
turned it into the DoCmd statement above (I left out 'StoreLogin' as I am the
only person using this machine.)

My error messages are ‘compile error, syntax error’ and ‘compile error,
expected: =’.

I don’t know what’s wrong. I don't know enough about VBA scripting rules to
figure this out. I've checked, checked, and rechecked the path and see
nothing wrong with it. I am guessing I left something out that
requires an ‘=’ that comes before or after the DoCmd.TranserDatabase
statement, or the way I've used parentheses or quotation marks within that
statement, or the way I've filled in the arguments, but I don’t know what & I
haven't been able to figure this out using the helps that come with Access
2007 and MS MDNS (the MS vna site). This is where I need help right now.

Jeff Boyce said:
Marian

What happens when you run what you have?

Here's the approach I use... (would use for an "automation" project) (I'd
pretty much have to be in your situation, with your machines, and your code,
and run it to see if it works).

Build code that correctly handles one.

Build code that correctly "gets" all.

Run the combination (as TEST) ... confirm results.

Run the combination (as PRODUCTION).

Is this a one-time effort, or will you need to do this over and over again?
If one time, would it take you less time to do it "manually" than work out
the code and trouble-shooting, and final version?

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Marian M. said:
Unfortunately, each group is unique -- good old US census data. I =could=
join the 8 tables after all the appending gets done, but I only need
certain
fields out of each group for any one set of variables I plan to construct.
My
problem is with the 'automation'. Is it possible?

Jeff Boyce said:
Marian

Uhmmm...

Are each of those "groups" unique? That is, are all the data elements
the
same in all the groups? If so, don't use separate tables for groups
(that's
soooo spreadsheetly!). Instead, use a single table with all the data
elements, then add [Group] as a final data element. When you append
records
into this new table, just make sure you note which group the records are
coming from.

Regards

Jeff Boyce
Microsoft Office/Access MVP


I am working with 8 groups of 51 dbase files that are all in a trusted
location. My plan is to create one table per group, which means
importing
408
dBase IV (I'm guessing the version) files as tables and appending 400
of
the
tables to their respective 1st table. I've already imported the first
two
files for each group to get the file structure and practice appending.
I
want
to do the rest in batch mode. I don't know VBA or Access macros, though
I
have worked with SPlus scripts & beaucoup dos batch files.

For the VBA, I found an example & have adapted it to import database
data.
I
first tried to work out the core of the macro, TransferData:
-------
Sub Import()

Dim dbs As Database

Set dbs = CurrentDb
DoCmd.TransferDatabase(acImport, "dBASE IV",
"C:\LSAY\Census\TabData\1990SF3\cd90_3A\vars01\", "stf305ar.dbf",,
"stf305ar", No)

End Sub
--------
My error messages are 'compile error, syntax error' and 'compile error,
expected: ='. I don't know what's wrong. I must have left something out
that
requires an '=', but I don't know what.

The macro is straightforward up to a point (have model sub-macro per
group),
but . how do I expand each sub-macro to include the 48 other files? (or
do
I
have to create 400 more sub-groups?) Is there a way to append each file
to
its base table within the same macro so that I don't have to figure out
how
to batch serial queries?

Here is the bulk import code (borrowed from Kirk, whose URL I didn't
keep).
Am I on the right track? Can I include an Append action in the same
process?
(I don't see anything about 'append' in the do.cmd list)
----------
Sub MassImport()

Dim strPath As String
Dim strTableName As String
Dim strFileName As String
Dim dbs As Database

Set dbs = CurrentDb
' Set the path to the directory where the files will be.
strPath = "C:\LSAY\Census\TabData\1990SF3\cd90_3A\vars01\"
strTableName = Dir(strPath) ' Retrieve the first entry table name.
strFileName = Dir(strPath & ".dbf") ' Retrieve the first entry file
name.
Do While strFileName <> "" ' Start the loop.
DoCmd.TransferDatabase (acImport, "dBase IV", strPath,,
strFileName,
strTableName, No)
strFileName = Dir ' Get next entry.
Loop

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Change this:
-------
Sub Import()

Dim dbs As Database

Set dbs = CurrentDb
DoCmd.TransferDatabase(acImport, "dBASE IV",
"C:\LSAY\Census\TabData\1990SF3\cd90_3A\vars01\", ,"stf305ar.dbf",,
"stf305ar", No)

End Sub
--------
Click to expand...

To this:
-------
Sub Import()
DoCmd.TransferDatabase acImport, "dBASE IV", "C:\LSAY\Census\TabData
\1990SF3\cd90_3A\vars01\", acTable,"stf305ar.dbf","stf305ar"

End Sub
--------

Problems with your code:
1. Wrapping your parameters in () makes VB think that
DoCmd.TransferDatabase is a function with a return value. Either
ignore the return value by using "Call DoCmd.TransferDatabase(blah,
blah, blah)" or "DoCmd.TransferDatabase blah, blah.
2. Use "False" not "No"
I would also specify acTable as the ObjectType instead of leaving it
to the default.

-Kris
 
Thank you Kris. That works a treat. I looks like my next task is to study the
documentation on the 'symbols' vba Access uses to signal what class of object
it is seeing.

Marian
 
Back
Top