Import Access Tables using VBA

leierjm

New Member
Joined
Dec 28, 2009
Messages
4
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!
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Oorang

Well-known Member
Joined
Mar 4, 2005
Messages
2,071
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:
Code:
DoCmd.TransferDatabase acImport, "Microsoft Access", "C:\Users\MyUserName\Documents\Northwind.mdb", acTable, "Orders", "Orders"
Link:
Code:
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

New Member
Joined
Dec 28, 2009
Messages
4
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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
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

New Member
Joined
Dec 28, 2009
Messages
4

ADVERTISEMENT

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

leierjm

New Member
Joined
Dec 28, 2009
Messages
4
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

New Member
Joined
Oct 15, 2021
Messages
4
Office Version
  1. 2013
Platform
  1. Windows
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,197
Messages
5,857,884
Members
431,905
Latest member
RW11700

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top