Query / VB Help Merging Multiple Linked Databases

[Herdrich]

Ok here is what i have going on I have been first linking excel documents to the access database i have about 500 that need to be linked and combined. Then i am using a union query to combine about 50 at a time because i get a query is too complex if i do any more. Then i am trying to combine all of those queries together and send them to a table so that they can be searched by a technician.

I am hoping that I can find a way to link all of the databases in a folder instead of adding them one by one also is there a better way to link all of these tables and send them to a table. Is there a way i can use VB instead or is doing what i have been the only way?

Thanks ,
Glenn

 

[PHV]

Why not using the TransferSpreadsheet method ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Herdrich]

Its looking good so far using the TransferSpreadsheet method i will do about 25 and see what it looks like. Its still an annoying task adding all of these databases. Is there a way to select all in a folder?

 

[PHV]

Have a look at the Dir function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Herdrich]

docmd.TransferSpreadsheet,acSpreadsheetTypeExcel12Xml,[Mass List],Dir ("Elfs1\3sfs\Base Access\POST FOOD TAXI\FOOD&TAXI\OFFICE SUPPLIERS\"),Yes,,

That is what i have so far not sure where i am going wrong what do i need to change so that the code will be correct.

Thanks