Share Access from Excel VBA

Question

Edit: The answer to this question can be found in the comments of the accepted answer.

I am trying to open an Access database by clicking a button in my excel file. I 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 for a while, and then Access shuts down almost immediately. If that matters, there is an AutoExec macro in the Access file that runs through several tests immediately upon opening.

Answer 1

Do not try to open the Access application; 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 specific configuration (and Access File Type).

ADODB is probably the simplest current library used to access data.

Update: Try importing data from Access, and then using the Data → From Access wizard. Yu can always use the macro transcoding tool to automatically generate VBA code for you, which will create some infrastructure for you; I use this regularly when learning new parts of the VBA object model.

Update - the final solution to the problem, from the comments below
This may be due to the fact that the variable is beyond the scope; move the db declaration outside the function to the module level

Answer 2

Started Access code by creating an application instance assigned to an object variable. At the end of the procedure, the variable went beyond the scope of access, so Access disconnected.

You accepted the answer to using a module level variable for an Access application instance. In this case, access remains enabled after the procedure is completed. However, if the user exits Excel, Access also closes.

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

You can use the Run WScript.Shell method to open the 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 = Nothing End Sub 

Answer 3

Remove the New ad, then it works

Answer 4

I know this is an old thread, but you will get this error in Excel VBA if you try to open an Access database but you don't have two specific links. (Tools, Links on the VBA editor screen). You need to click “Microsoft Access 15.0 Object Library” and “Microsoft ActiveX 6.1 Data Library”.

Answer 5

This is actually pretty simple:

 Dim cnn As ADODB.Connection 'Requieres reference to the Microsoft Dim rs As ADODB.Recordset 'ActiveX Data Objects Library Set cnn = CreateObject("adodb.Connection") cnn.Open "DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\Users\wcarrico\Desktop\wcarrico-CapstoneFinalSubmission.accdb;" Set rs = cnn.Execute("SELECT * FROM MyTable") While Not rs.EOF Debug.Print rs(1) rs.MoveNext Wend