VBA to open Access DB

[Moedeb27]

I am trying to open an Access Database from a button within Excel.

However when I attempt to run it I get the following error:
Compile Error
User-defined type not definded

on - db As Access.Application


The code I am using is as follows:
------------------------------------
Dim db As Access.Application

Public Sub OpenDB()

Set db = New Access.Application
db.OpenCurrentDatabase "C:\Data\Report.accdb"
db.Application.Visible = True

End Sub

[Neil_]

You need to define the reference before automating Access. Do this from the VB Editor / tools/ References and tick the reference to Microsoft Access 16.0 Object Library. I've edited this post to give examples of opening the full database and opening it using DAO, which gives you control of the tables and queries as record sets, but not anything else.

DAO and early binding

Please Login or Register  to view this content.

If you are going to share this with users of older versions of Office, you will need to use late binding instead. You do not need to set a reference in the VB Editor with late binding. This example opens the application rather than the tables and queries.

Please Login or Register  to view this content.

[Neil_]

And finally, Using ADDOB. You need to set a reference to "Microsoft ActiveX Data Objects Recordset 2.0 Library" to enable early binding.

Please Login or Register  to view this content.