Import Access Tables using VBA

[leierjm]

Hi, I need some VBA code to import a table from one access database to another access database. Is this even possible using VBA? I already have code to bring data from access to excel and vise versa. Thanks!

 

[Oorang]

Yes it is But are you sure you need to copy the data? Why not just create a linked table to the other database? Here is how to do either:

Import:

DoCmd.TransferDatabase acImport, "Microsoft Access", "C:\Users\MyUserName\Documents\Northwind.mdb", acTable, "Orders", "Orders"

Link:

DoCmd.TransferDatabase acLink, "Microsoft Access", "C:\Users\MyUserName\Documents\Northwind.mdb", acTable, "Orders", "Orders"

Notice that the only difference between the two examples is the first parameter.

 

[leierjm]

I do want to copy the database because I need to have one live version and one static version of the tables. A few people will be needing to copy multiple tables over and I think the only way to prevent mistakes is if they can hit a button in excel.

I was reading a little about the docmd. command and I am not sure if my computer is up-to-date enough to use the command. I am still using Microsoft Excel 2003 and am unfamiliar with Visual C#. I'm sorry I didn't mention that before.

I am using db. and conn. connections to connect to access:
Dim Conn As New ADODB.Connection
Con.ConnectionString = DBlocation + AccessFile
Con.Provider = "Microsoft.Jet.OLEDB.4.0"
Con.Open
Con.Close


Dim db As Database
Set db = OpenDatabase(DBlocation + AccessFile)
db.TableDefs.Delete "Ovations_data_inccap_" + LOB
db.Close

If I can use the docmd. with Excel 2003 please let me know and I will dig a little more. Thank you for your help!

 

[xenou]

Not sure if I'm shooting off an answer too fast but in your original post you mentioned Access to Access. Using VBA in Access, the DoCmd object is available by default all the time.

Its not clear now - are you running code from Access or from Excel? Is the data going from one Access database to another, or from Access to Excel?

 

[leierjm]

ADVERTISEMENT

I need to use Excel Visual Basic to transfer a table from one Access database to another Access database.

 

[leierjm]

Oorang, thanks for the code. I figured out how to use the docmd command. Here's the final code I used.


Dim oacc As Access.Application
Set oacc = New Access.Application
oacc.OpenCurrentDatabase DBlocation + AccessFile
oacc.DoCmd.TransferDatabase acExport, "Microsoft Access", DBlocation2 + AccessFile2, acTable, TableName, TableName, False

Thanks again!

 

[lucausa75]

Thanks for your code.
I got an error on acExport which seems to be not recognized...
Do I have to add something into the references?
Thanks