Append several dbf files to one Access XP table

[Ngan Bui]

I created a code to store the filenames of several dbf
files. I want to be able to append the data in each of
these dbf files into one Access XP table.

If I use the docmd.transferdatabase command, Access
creates a new table for each dbf file, which is by design.

Does anyone know how I can append to an existing table,
not create a new table?

Thanks.
Ngan

 

[Ngan Bui]

the first part of the code stores the names of the files
in the folder, so I could have 5 filenames.

I then have a Do Loop that runs several lines code for
each file name:
Do
Docmd.transferdatabase acImport, "dbase 5.0", "C:\", ,
strFileName, "tblImport"
db.execute insert query
db.execute update query
loop

When the code goes to the next file name, at the
docmd.transferdatabase, it sees that there is already a
table called tblImport so Access adds another table called
tblImport1.

So if there is 5 filenames, I get tblImport, tblImport1,
tblImport2...tblImport4.

I want it so the data is append to one tblImport, instead
of creating the 5 different tblImport. Is that possible
with the transferdatabase function or some other function?

Ngan

 

[Joe Fallon]

"If you import a table or other object, Access adds a number to the name if
it conflicts with an existing name. For example, if you import Employees and
Employees already exists, Access renames the imported table or other object
Employees1."

Based on the designed behavior you can't do it that way.
So you will have to devise a workaround.

One idea:
1. Import to a "staging" table named "tblImport"
2. Run an append query to move the imported data to a "permanent" table.
3. Delete tblImport
4. Re-run the process and tblImport should be re-created thus allowing your
append query to always be based on tblImport.