batching append queries

[Marian M.]

I am working with 1990 census data in Access 2007 on a 64-bit machine. The
census comes in 34 groups of variables. Each group contains 51 dBase files
(50 states + DC). I am using 8 of the groups. There is a utility that comes
with the data for sticking the 51 files together so that you have one group
dBase file, but it doesn't work on 64-bit machines. Access does not allow you
to string several Append queries into one query script. I have worked out a
mass import of the files in VBA (thanks to help from this Access discussion
group), but now have to stick all the (now) tables in each group together
into a variable-group table.

I have tried searching a couple of lists but am either using the wrong
search terms, or the answer has been there, and I truly do not understand VBA.

If I were appending only one table to another, I would use the SQL:

INSERT into tstf3nn
SELECT stf3nnST.*
FROM stf3nnST;

where nn = 2-digit variable group number
ST = 2-letter State abbreviation
and tstf3nn = base table for the nn group

Can I set up a loop that will call on a list of state abbreviations and a
list of group numbers (or a single list of nnSTs) that cover all the tables
that need appending? If so, how? Should I just stick with appending the files
within one group at a time?

What is the VBA 'method' that will append one table to another?

What search terms should I have been using to find the answer on how to
batch append queries? (Thankfully, the 2000 census data comes in ascii files.
DOS I know how to deal with.)

Thank you in advance,
Marian

 

[Albert D. Kallal]

Access does not allow you
to string several Append queries into one query script.

No, but you have ms-access execute several scripts on after another, and you
get the same results.

Remember, the VBA coding system in ms-access is MUCH more flexibly and
powerful then sol scripts alone.

You can use code to execute as many scripts "together" as you want.

I suspect the best approach would to have ea button on a form that you
press, and it reads the directory of dbase files, and imports all of the
data into ONE table, and then does your processing, and then prints the
repot...all at the touch of a button...

If you need to summaries, or report all of this data, I would not import
into individual tables, but import the data into one large table.

into a variable-group table.

Hum, not really familiar with that above term....

If I were appending only one table to another, I would use the SQL:

INSERT into tstf3nn
SELECT stf3nnST.*
FROM stf3nnST;

where nn = 2-digit variable group number
ST = 2-letter State abbreviation
and tstf3nn = base table for the nn group

Do the tables have a state field in them? (thus, we could perhaps "set" the
state field at the time of importing of al those dbase tables into one
table. Once done, then reporting becomes trivial...

Can I set up a loop that will call on a list of state abbreviations and a
list of group numbers (or a single list of nnSTs) that cover all the
tables
that need appending?

You can, but where is that list to come from? (perhaps you have a state
list???).

If so, how? Should I just stick with appending the files
within one group at a time?

You can, but is there a possibility of simply importing that data into one
file with a state field, and then you eliminate all this extra data
processing work when trying to build a report, or summary of the data?.

What is the VBA 'method' that will append one table to another?

You simply use VBA code to execute the sql

eg:

currentdb.Execute "INSERT into tstf3nn SELECT stf3nnST.* FROM stf4nnST;"

currentdb.Execute "INSERT into tstf3nn SELECT stf3nnST.* FROM stf5nnST;"

or, you can exeucte a query you built

currentdb.Execute "name of query"

and, thus

dim strSql as string
dim i as interger

for i = 10 to 20

strSql = "INSERT into tsf3" & i & " SELECT stf3" & i & "ST.*FROM str4" &
i & "ST;"
currentdb.execute strSql

next i

The above would run "nn" from 10 to 20....

but, as I said, I think this should be tackled at import time and you not
burn yourself out running a gazillion append queries to solve thing that
could be done during import time.

I think a lot of problems here stem from that you have the file name
defining the data, when it should be a field name (column) that defines that
state etc. I would just setup a good import system that imports the data
into one table, and if need be, sets the "state", or other field(s) needed
during that import

Once you have one table...then the reporting is easy with ms-access....

 

[Marian M.]

Albert, Thank you for your help.

The multiple groups of state files is something that Uncle Sam set up
sometime before 1990 (back when people were excited to have harddisks with
tens of megabytes, not 100s of gigabytes). Each group of files contains
different variables (eg, group 01 has variables P001 to P013, group 05 has
variables P014J to P017, and so forth). In the end, I'll be joining some
fields from each group into the table I will then work with (this is in
preparation for doing statistics with census data within a GIS).

To the group:
I took Albert's advice to heart & combined the import and append (code at
the bottom of this reply). Unfortunately, it's a bit of a dog, so I've tried
streamlining the code. I'm getting 'Too few parameters. Expected <number>.
(Error 3061)'. The debug button points at the 'rsGroup =' line.

Here is the code I'm working on now. What have I done wrong? :
------------------
Sub ImportAppend()

Dim strPath As String, strTableName As String, strFileName As String
Dim strState As String, strGroup As String
Dim iLoop As Integer, kLoop As Integer
Dim dbs As Database, rsStates As Recordset

Set dbs = CurrentDb
' Set the path to the directory where the files will be.
For kLoop = 1 To 5
For iLoop = 3 To 51
Set rsGroup = dbs.OpenRecordset("Select Group From tblGroup Where
GroupID = " & kLoop)
Set rsState = dbs.OpenRecordset("Select State From tblState Where
StateID = " & iLoop)
strGroup = rsGroup!Group 'Set the first group string
strState = rsState!State ' Set the first state string
strPath = "C:\LSAY\Census\TabData\1990SF3\cd90_3A\DataInUse\"
strFileName = "stf3" & strGroup & strState & ".dbf" ' Set the first
entry file name.
strTableName = "tstf3" & strGroup ' Set the first base table name.
DoCmd.TransferDatabase acImport, "dBase IV", strPath, acTable,
strFileName, strTableName
Next iLoop
Next kLoop

End Sub
---------------------
the operational but very slow code:
---------------------
Sub MassImportAppend()

Dim strPath As String, strTableName As String, strFileName As String
Dim strSQL As String, strState As String
Dim iLoop As Integer
Dim dbs As Database, rsStates As Recordset

Set dbs = CurrentDb
' Set the path to the directory where the files will be.
For iLoop = 3 To 51
Set rsState = dbs.OpenRecordset("Select State From tblState Where
StateID = " & iLoop)
strState = rsState!State ' Set the first state string
strPath = "C:\LSAY\Census\TabData\1990SF3\cd90_3A\DataInUse\"
strFileName = "stf305" & strState & ".dbf" ' Set the first entry
file name.
strTableName = "stf305" & strState ' Set the first entry table name.
strSQL = "INSERT into tstf305"
strSQL = strSQL & " SELECT " & strTableName & ".*"
strSQL = strSQL & " FROM " & strTableName
DoCmd.TransferDatabase acImport, "dBase IV", strPath, acTable,
strFileName, strTableName
dbs.Execute strSQL
DoCmd.DeleteObject acTable, strTableName
Next iLoop

End Sub

 

[Albert D. Kallal]

Two things to note:

If you place the following code in a module, note the output:

Dim i As Integer
For i = 5 To 15
Debug.Print i
Next i

6
7
8
9
10
11
12
13
14
15

Note how the numbers 6 to 9 don't have a leading zero. Do you need this
zero?

You need to use:

Dim i As Integer
For i = 5 To 15
Debug.Print Format(i, "00")
Next i

05
06
07
08
09
10
11
12
13
14
15

So, keep in mind that you might need to "pad" the string with a leading
zero.

Next thing to check:

Set rsGroup = dbs.OpenRecordset("Select Group From tblGroup Where
GroupID = " & kLoop)

The above looks ok, but is Group ID a string field, or number field? If
group id is a string, then you need to add the quotes:


eg:

Set rsGroup = dbs.OpenRecordset(

"Select Group From tblGroup Where GroupID = ' " & kLoop & " ' "

Actually, the above is not quite right, I put in extra spaces so you could
see the single quotes. The code would actually look like:


Set rsGroup = dbs.OpenRecordset("Select Group From tblGroup Where
GroupID = '" & kLoop & "'")

So, if group ID is a text field, then you must surround the kLoop vlaue with
quotes and, furhter, if you need padded zeros, then you must address that. I
would use


dim strSql as string

......
.......
.....

strSql = "select Group From tblGroup where Groupid = " & _
"'" & format(kloop,"00") & "'"

str rstGroup = dbs.OpenRecordSet(strSql)

I broken the above up to make it bit more easy to read, and edit. You don't
have to use the "strSql", but, it helps in making the code more easy to
read...

 

[Marian M.]

Thank you