Using Excel VBA to query Access Database

Question

I want to use Excel VBA to query an MS Access database, searching and adding to a table.

By searching this site I found this code:

Dim dbs As DAO.Database

Dim dbs As Object
Set dbs = OpenDatabase("FILEPATH\database1.accdb")


dbs.Execute "INSERT INTO Table1(ID, Field1, Field2, Field3) VALUES('1','Hello','Helo','Hi')"

which doesn't work because it says that the sub or function was not defined for the function OpenDatabase()

Has anyone got a better method of this

The reason I want to do this when I know there are better methods out there is because the database and spreadsheet already exist (run by my employer) and I just want to link them.

Hi go through this link stackoverflow.com/documentation/vba/3578/working-with-ado/12351/…. — Techie
The documentation link provided above is your best explanation. Your code doesn't work because OpenDatabase is a user defined function; you have to write it yourself (see documentation). Also, make sure you have the correct ADO references enabled. — SandPiper
To clarify - the code you found is specifically intended to run inside of the Access application: it will not run in Excel. The link posted by @HA560 has what you need. — Tim Williams

MJH MJH · Accepted Answer · 2016-08-31T03:58:47

Try this (with a correct FILEPATH, of course):

  Dim appAccess As Object: Set appAccess = CreateObject("Access.Application")
  appAccess.OpenCurrentDatabase ("FILEPATH\database1.accdb")
  appAccess.CurrentProject.Connection.Execute "INSERT INTO Table1(ID, Field1, Field2, Field3) VALUES('1','Hello','Helo','Hi')"
  Set appAccess = Nothing

Using Excel VBA to query Access Database

1 Answers