Open MS Access from Excel

G

Greg H.

I am attempting to open a MS Access database with a button in excel. I have
the path name hidden in a cell and the cell is named CSTS_Raw. I am using
the code below but when I run the code i get the below error: User-defined
type not defined. It then highlights "appAccess As New Access.Application".
Any ideas?

Private Sub CSTS_Raw_open()
Dim CSTS_Raw As Range
Set CSTS_Raw = ActiveSheet.Range("CSTS_Raw")
Dim appAccess As New Access.Application
Set appAccess = Access.Application
appAccess.OpenCurrentDatabase CSTS_Raw
End Sub
 
J

JLatham

I presume you have a Tools | References entry to Microsoft Access ##.0 Object
Library?

If so, then delete the word New from that Dim statement, make it simply
Dim appAccess As Access.Application
and then your Set statement becomes
Set appAccess = New Access.Application
 
G

Greg H.

It looks like it is running but nothing opens. Is there a visible=true
statment that i need to add?
 
N

Norman Yuan

Yes, set "appAccess.Visible" to True.

Greg H. said:
It looks like it is running but nothing opens. Is there a visible=true
statment that i need to add?
 
J

JLatham

As Norman has already said - yes, you'll need to set it's .Visible property
to see it.

At this point you treat the appAccess just as if you'd opened Access
directly - you'll need to next open up a database, and then work with its
objects by either setting other appropriate type objects to the Access
objects for 'shorthand' referencing, or using longer drill-down referencing
to get to things.

When all is done, you'll want to close the database then use appAccess.Quit
to shut down the instance of Access itself, and Set appAccess=Nothing to make
sure resources are released back to the system.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top