Open MS Access from Excel

[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

 

[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

 

[Greg H.]

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

 

[Norman Yuan]

Yes, set "appAccess.Visible" to True.

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

 

[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.