Opening Access from Excel VBA

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

The code started Access by creating an application instance assigned to an object variable. At the end of the procedure, the variable went out of scope so Access shut down.

You accepted an answer to use a module-level variable for the Access application instance. In that case, Access remains running after the procedure ends. However if the user exits Excel, Access will close down too.

If the goal is to start Access and leave it running until the user decides to close it, just start Access directly without assigning the application instance to an object variable (Set db = New Access.Application). That db variable would be useful if your Excel code needed it for other purposes. However, it's actually only used to open the db file.

You can use the Run method of WScript.Shell to open your db file in an Access session.

Private Sub bttnToAccess_Click()    Const cstrDbFile As String = "C:\Users\wcarrico\Desktop\wcarrico-CapstoneFinalSubmission.accdb"    Dim objShell As Object    Set objShell = CreateObject("WScript.Shell")    objShell.Run cstrDbFile    Set objShell = NothingEnd Sub

I know this is an old thread, but you will get this error in Excel VBA if you are trying to open an Access database, but you do not have two specific References clicked. (Tools, References on the VBA Editor screen). You need to click 'Microsoft Access 15.0 Object Library' and 'Microsoft ActiveX Data Objects 6.1 Library'.