I am using DAO to reference database1. Inside database1, I want to essentially import a table from database2. the entire structure and data.

I can't tell how to do this from CreateTableDef and the arguments that intellisense is prompting me like Source, Connect - where do I find the syntax for this?

I know the 2 database paths, and I want to use VBA to essentially copy a table from database2 into database1.

I have the objects set already, I just need to know the way to get an item from database2 to database1.

No need for CreateTableDef. Take a look at the DoCmd.TransferDatabase method.

Thank you, I should have explained in my original post that I am coding this from a 'third' location, if you will. So I am not sure how to gain access to the DoCmd category.

Then I just realized a moment ago that there is yet a third problem. database2 (source of tables) is accdb and was indeed created in 2007. database1 (destination of tables) is .mdb and was created in 2003.

Currently, users are copying a table from the .accdb, and PASTING it into the .mdb. That is the action I am trying to automate. I am using DAO as I am doing this from Excel, but like I said I think it's probably no different excel vs. access, because this will be done from a 'third location' other than the 2 databases. (unless I can create a macro or code within either database which will essentially get those 2 tables from database2 to database1).

I am open to a more fragmented process of somehow exporting from accdb, then creating inside mdb, even 2 separate procedures in the 2 separate databases, then this code activates both procedures. Just trying to find any way for now, then possibly even the 'best' way.

Does this make more sense as to my quandary.

Can I do this by actually declaring an access application variable, then using Application.Run? If so, do I even need to open the database?

... 'not sure the syntax from here?

This post has been edited by ipisors: Apr 10 2012, 06:46 PM

Thanks, Doug. I should have looked more into creating an application instance. That is wonderful, thanks so much!
Getting so warm here....Gosh, I wish I was on my way to Canada!!

This post has been edited by ipisors: Apr 10 2012, 07:55 PM

Doug, I'm not sure this is working as I need it to actually. It seems to require that the Destination table name be present in the destination database. But I don't want to do that, I want it to put the table in from SourceDB to DestinationDB where none is present in DestinationDB. Otherwise, it would just be appending data right?

I get a runtime error "The Microsoft Office Access database engine could not find the object 'Table1'. Make sure the object exists and that you spell its name and the path name correctly."

And I am using:


In my case, Database1.mdb is the destination database, and does NOT contain "Table1". Database2.accdb is the source database, and definitely does contain the table, "Table1"

Oh nevermind, i saw my mistake. my code should have been