Edit: The answer to this question can be found within the comments of the accepted answer.
I am attempting to open an Access database from a button click within my excel file. I currently have this code:
Private Sub bttnToAccess_Click()
Dim db As Access.Application
Set db = New Access.Application
db.Application.Visible = True
db.OpenCurrentDatabase "C:\Users\wcarrico\Desktop\wcarrico-CapstoneFinalSubmission.accdb"
End Sub
This seems to work briefly and then Access shuts down almost immediately. If it matters, the Access file has an AutoExec macro that runs through a few tests itself on open.
Don't try to open the Access application then; just create a connection object using one of the Data Access technologies: - OLE-DB or - ODBC.
Google "ODBC Connection strings" or "OLE-DB Connection Strings" to get details depending on your particular configuration (and Access filetype).
Probably ADODB is the easiest current library to use for your data access.
Update: Try Importing the data from Access then using the Data -> From Access wizard. Yu can always use the Macro recoding facility to automatically generate some VBA code for you, that will create some infrastructure for you; I use this regularly when exploring new portions of the VBA object model.
Update - Final resolution of problem, from comments below
That may be because the variable goes out of scope; move the declaration of db outside the function, to module level