Opening Access from Excel VBA

Question

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.

Corroboree

Are you attempting to run the Access GUI inside Excel, or just trying to connect to the database and query the data inside? – Dulcedulcea

I am just providing easy access to the database for the user. I am EXTREMElY novice at VBA and not even sure what the Acess GUI is. The database is basically a seperate tool for planning a wedding. The excel and access files will eventually link through a import. – Corroboree

Answer 1

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

Dulcedulcea

I very quickly looked through some of the results on Google and had no idea what any were talking about or where to go from there. This project is for an intro to VBA class at school so I have just begun learning. Also, I think my comment above about importing might have confused my intentions. I designed a database for wedding planning that manages guest lists, vendors, and few other things. The excel file I am working on now deals more with the financial side of the wedding (modeling, min max mostly calculations, etc.). I really just want to be able to open the access file and nothing else. – Corroboree

Navigating a recordset might be a bit hard to chew at first if OP is extremely novice at VBA as he says. – Fanni

Yeah recording a macro is a great way to learn. However, I am not trying to import anything at the moment. All I hope to accomplish is to give an alternative to going to the desktop and opening the file from there. I want to open the file and then nothing else. Like I said in the question, it seems like the code provided opend access but then immediately closes it as well. – Corroboree

That may be because the variable goes out of scope; move the declaration of db outside the function, to module level. – Dulcedulcea

That seems to have worked! Thanks a ton and sorry for all of the confusion. – Corroboree

Answer 2

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 = Nothing
End Sub

Deformation

I tried this method and it gave be a run-time error "Method 'Run' of Object 'IWshShell3' failed... – Protectorate

Answer 3

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

Beal

Answer 4

Remove the New declaration then it works

Moonier

Answer 5

Actually it is pretty straightforward:

Private Sub bttnToAccess_Click()
 db = DBEngine.OpenDatabase("C:\Users\wcarrico\Desktop\wcarrico-CapstoneFinalSubmission.accdb") 
End Sub

For this to work you need to declare db as Database at the Module level.

 Dim db As Database  'Requires reference to the Microsoft
                     'Access Database Engine Object Library

Polito

dbneeds to be DAO (Data Access Objects) not ADO (Active X Data Objects)! – Shellyshelman

@Shellyshelman Good catch! – Polito

Usual flaw. Always declare with library on DAO/ADODB-Objects – Shellyshelman

Answer 6

Just create a batch file that has the address of the database you want to open and add this one-line of code:

Call Shell ("The batch file address")

Boger