Transfer data DB to DB

[GrexP]

I need to set up an automated process to transfer data from about 25 tables from one database to another. This will happen on a weekly basis and I'm wondering what the best way to set this up is.

Both are Access 03 db files. The main database has data for a dozen or so clinics and I need to get the data for one clinic out and in to the the secondary db. The data in the secondary db will be replaced every week with fresh data from the main db. Once it is refreshed with new data the clinic will download the db from our site.

My first thought is to just export queries to CSV files and then import them in to the secondary db file. If run from a macro it could be a scheduled task. I could then import the CSV files. This could also be automated with a macro.

Any other ideas.

 

[boblarson]

If the tables are just going to be replaced (data does not need to be merged) then you can set up code to use SQL to drop the current tables and replace them with the other tables (using DoCmd.TransferDatabase).

There is code within my Backend auto updater (available here: http://www.btabdevelopment.com/main/AccessTools/tabid/78/Default.aspx) that basically does that. You should be able to take the code out of it and modify it for your use.

 

[GrexP]

Excellent! Thanks Bob. I didn't realize the TransferDatabase would do individual tables like that. I also wasn't familiar with "DROP TABLE". I was planning on "DELETE * FROM tblName". Can I assume that the "DROP TABLE" will help to keep the mdb file from bloating in size and cut down the need for compacting?

 

[boblarson]

Can I assume that the "DROP TABLE" will help to keep the mdb file from bloating in size and cut down the need for compacting?

Nope, can't assume that. Anytime you delete something from an mdb file it still has the space reserved until you compact. So, that won't help with that. You might have better luck, with the compacting issue, with deleting the data and appending the data, but even that will still cause some need to compact.

 

[GrexP]

Nope, can't assume that.

That would have been nice.Oh well,

Anyway, the code worked fine. I can export 27 queried tables lickity-split.

 

[boblarson]

Anyway, the code worked fine. I can export 27 queried tables lickity-split.

Glad to hear.

 

[fenhow]

Using using DoCmd.TransferDatabase

Hi,

I am releasing an updated version of a MDE app. I need the end users to import only two tables from their old app into the new one.

tblMain and tblDaily

On the new release I intentionally left out those two tables. I want a prompt the first time the user enters the database to search for and import those two tables.

I have tried to create a macro to do this but the location of each users database may be different so I cannot make it fixed?

Is there a way to prompt the user for the location of the old DB, select the two tables and hit import?

Thanks.

Fen How

 

[boblarson]

Check out this for letting them select the file:
http://www.mvps.org/access/api/api0001.htm

The rest is just using DoCmd.TransferDatabase code.