Open .accdb Access file using Excel 2007 VBA

Crinder

New Member
Joined
Mar 30, 2011
Messages
18
Hi all,

I'm currently trying with no luck to open an access 2007 (.accdb) file using Excel VBA. I can get as far as opening Access using the following code:

Dim MyAccess As Access.Application
Set MyAccess = CreateObject("Access.Application")
MyAccess.Visible = True
MyAccess.OpenCurrentDatabase ("C:\Users\User.Name\Desktop\Cronos.accdb")

But on that last line when supposed to open the file I get a Run time error 7866. Anyone know of a way around this, or even a different way to open an Access accdb file using Excel VBA?

Thanks.
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,793
Office Version
  1. 2019
Platform
  1. Windows
Code:
MyAccess.OpenCurrentDatabase ("C:\Users\[COLOR="Red"]User.Name[/COLOR]\Desktop\Cronos.accdb")

You probably need to have the actual user's login name in the file path.


As far as opening an mdb it depends on what you are doing. Your code opens the Access application.
 

Crinder

New Member
Joined
Mar 30, 2011
Messages
18
I am using my actual username. I just typed "User.Name" here to mask it, but the address is correct.

as a reminder, this isn't a mdb file, it's an accdb and I suspect the code to open it from excel is different than it would be with a mdb file.

Does anyone know how this code would go?
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,793
Office Version
  1. 2019
Platform
  1. Windows
This works for me. But you must have Access 2007 and it must be the default database that is created when you use CreateObject. That should be clear enough when you step through the code - you'll see the Access application you opened (so you can tell if its Access 2007 or not).

One problem (for me) is that the Access application is destroyed when the subroutine is finished - so it doesn't last very long.

Here's my test code (like yours, except file path is different and the Access object variable persists (as long as Excel is open - but the DB will still be lost if you close Excel).
Code:
Dim MyAccess As Object

Sub Foo()
Set MyAccess = CreateObject("Access.Application")
MyAccess.Visible = True
MyAccess.OpenCurrentDatabase ("C:\myTemp\TestDB.mdb")

End Sub
 
Last edited:

Ruddles

Well-known Member
Joined
Aug 24, 2010
Messages
5,786

ADVERTISEMENT

PMFBI but this isn't a problem with the database object library, is it?

To open a .mdb you can use a DAO Object Library but to open a .accdb you need the Microsoft Office 12.0 Access database engine Object Library.
 

Crinder

New Member
Joined
Mar 30, 2011
Messages
18
Thanks to both of you for your responses. I am actually using Access 2007, a file with extension .accdb and microsoft access 12.0 library. Still can't get it to open, any ideas? do you need any more info from my part?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,067
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

Are you sure the database isn't already open?
 

Crinder

New Member
Joined
Mar 30, 2011
Messages
18
I'm sure, thanks.
Also, I just need to open the access file to run some queries and macros. I hadn't thought about it before, but if there is a way to do so without opening the file, that would be just as good.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,067
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
To run macros (yuck) you will have to open the db. Is the db secured in any way? (password or workgroup security)
 

gizmo0202

New Member
Joined
Apr 5, 2011
Messages
2
Here is the code I've used for Excel 2007 and Access 2007:

Dim TARGET_DB As String

TARGET_DB = "Financial Summary40.accdb"
'create the connection to the database

Set cnn = New ADODB.Connection
myConn = ThisWorkbook.Path & Application.PathSeparator & TARGET_DB
With cnn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Open myConn
End With

Hope this helps!
Gizmo
 

Watch MrExcel Video

Forum statistics

Threads
1,122,491
Messages
5,596,477
Members
414,070
Latest member
DuncanLucas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top