Open Microsoft Access Database in Excel VBA code

[Golftourney]

I am looking to run a VB macro in Excel which opens an Access database, opens a table, then paste appends a record to the table. Can anyone give me a hand with the code?

 

[PHV]

Something like this ?
Dim myDB As Object
myDB = GetObject("\path\to\database.mdb")
And then play with OLE Automation.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[jksmi]

Hi,

if you add the access object library as a reference in XL and and explicitly refer to Access in you declarations it will be a bit easier as you can work with it as if you were in Access, just replace application with your Access App variable (application is the default for access objects and therefore you dont usually have to type it, but use the object browser to help work out where you need it) to create a an access object explicitly use

Dim appAccess As Access.Application

Set appAccess = CreateObject("Access.Application")
appAccess.OpenCurrentDatabase ("\path\to\database.mdb")

- which is just the same as what PHV posted except you'll get the auto list of members, i.e. appAccess.DoCmd.RunSQL strUpdateQuery

The other thing you could do is write it all in your access database, add your access db to Excel as a reference and call access db functions from Excel... HTH, Jamie

 

[Golftourney]

You guys are great. Thanks...