Combine Access 500 files to 1 file

[Oasisba]

Hi,
Please help me to solve this problem.
I have 500 access files which have each only 1 table and same format. The file name is the ShopID (primary key) and locate at the same folder. I would like to combine all 500 files to 1 file.

Any idea??

Thank you.

 

[JBinQLD]

i'd approach this with a bit of coding. (suprise suprise eh regular readers?)

Break it into simple steps

a) Create a new database

b) Add a form with a button that:

c) Uses filescripting object to build an array of *.mdb filenames in current dir

d) loop through list of files

e) run (build) a query that performs a make table in current database from data in remote database

f) move to next database in file list

g) msgbox("finished!")

Anyone on here will help you with any step of this or perhaps suggest other methods

JB

 

[MichaelRed]

Do not use Make Table on All. this will just put 500 tables in the single file. If the Tables are all the same structure, you will want to (carefully!) concatenate them. This, of course, is hazzardours, as there may be duplicates and near duplicates. you probably want to copy ONE of the 500, create an (autonumber or guid) PK and append the remainder. Then chaek for dups / unwanted records (probably the most difficult part).



MichaelRed

 

[JBinQLD]

Good and obvious point by MR i should have mentioned. Perhaps create a mimic of the table structure with no keys or indexes to append all the data first.

Then look for some help on removing duplicates. i focussed more on the over all challenge of getting 500 files into one. Good luck fella, sorry for missing that important note.

JB

 

[jrbarnett]

See thread1121-1480048 for some hints/tips on finding and removing duplicate information.

John