Another Approach in copying data from another file to a local table

[Hudas]

Hi -

I have local tables that needs to be updated based from another file. To update the data of the local table I usually do this

strSQL = "Insert Into tbl_holidays Select * From [MS Access;DATABASE=" & pathofanotheraccessdatabase & ";pwd=" & thePwd & "].[tbl_holidays]"
 CurrentDb.execute strSQL, dbfailonerror

In this example I copied all the data from the other file tbl_holidays to the local tbl_holidays. Its alright if its just this one but I have atleast 20 tables where I have to repeat the same process. AND I have atleast 20 users for this?

Is there a better way to do this? an ADO approach maybe?

Thank you
Hudas

 

[Minty]

Can you not link the other tables into the target database, and then update from the linked tables?

 

[The_Doc_Man]

Something clicked here.

Do you have 20 tables and 20 users because each user has a different table? If so, part of your problem COULD be a design issue of improperly sharing the information. The culprit in that case would be a questionable table structure that is not properly normalized. I think that to give you best advice, we need to know a little bit more about your scenario.

Do you know how to build a split database in which you share the back-end but users have a distinct front-end? That might be a part of your answer.