copying tables from one database to append a table in another

[zoonkai]

I have two separate database (.mdb) files I'm working with. I need to copy the records from ten tables of the source database into one preexisting table.

the source tables are named "INDEX1" TO "INDEX10"
the destination tabe is named "i" for invoices

is there a way I could automate this with a macro or query or something

TIA

Donald Dixon

Don-Nan Pump & Supply Co. Inc.
(915) 682-7742
(915-570-7742

email: donnan@don-nan.com
web:

http://www.don-nan.com

Donald (Zoonkai) Dixon
donnan@don-nan.com

http://www.don-nan.com

 

[TheVMan]

I would consider one macro for each database. In the first, I'd have a "transfer to" export procedure to send the records send to some external file in a network share or whatever. The second macro does the same but as an import. Use multiple steps in your macro for so you can do all the imports/exports in one macro.

 

[PaulF]

Why not Link the 10 tables from the 2nd MDB to the 1st MDB, then use code to do an Append.

Dim x As Integer, strSQL As String
DoCmd.SetWarnings False
For x = 1 To 10
strSQL = "INSERT INTO i SELECT * FROM Index" & x & ";"
DoCmd.RunSQL (strSQL)
Next x
DoCmd.SetWarnings True
MsgBox "Done"

PaulF

 

[zoonkai]

VMan I used what you suggested and it works great.....Thanx!!

PaulF.....I like that suggestion....I will prolly look into changing that to make it even easier...Thanx also!!

I'm exporting to a delimited text file and then import into the destination
database.

The source database is a paperless office scanning database with an .mdb format......the way it works it has the data spread out over ten tables....??? so What I'm doing is combining those into one table in a separate database and then comparing them to a table with a large sequence of numbers (unmatched query), to help me find the "missing" numbers.....maybe it's not the best way to do it but.....they're not all numeric so it made it hard to do some things....

once a week or so i can now update the information and check for the missed
documents


Donald (Zoonkai) Dixon
donnan@don-nan.com

http://www.don-nan.com