Exporting tables from different DBs

[Brian100x]

I am having trouble finding code to make this work.

I need to export a table from one backend database to another backend database in code. The code needs to be run from the front end database. If I use the DoCmd.transferdatabase It transfers the link table deffinitions not the backend table.

Here is the background

I have a front end database linked to several back end databases. I have two back end databases that reach access's upper size limit of 2GB. When compacted they are around 800MB. My problem is that when trying to compact large databases access has issues. 1)it takes an increadable amount of time. 2)it often fails. I have found a work around. If I export just the table it is basically doing a compact. This only works for exports. If I import, use make table or append table queries I end up with large databases again.

Any help would be appericated.
Thanks
Brian

 

[Rob.Mills]

Dim db As DAO.Database

Set db = DBEngine.Workspaces(0).Databases(Path & Name)

DoCmd.CopyObject CurrentDb, "tableName", acTable, db

 

[Brian100x]

Rob,

Thanks it was close enough for me to modify.

I didn't want the destination database to be the currentDB but that is eaisly solved.

Brian

 

[Brian100x]

Ok spoke to soon.

Access doesn't like a database object in the sourceobjectname (DB). It appears to be looking for a table name in the current database.