Open .accdb Access file using Excel 2007 VBA

[Crinder]

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.

 

[xenou]

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]

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]

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

Dim MyAccess As Object

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

End Sub

 

[Ruddles]

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]

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]

Are you sure the database isn't already open?

 

[Crinder]

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]

To run macros (yuck) you will have to open the db. Is the db secured in any way? (password or workgroup security)

 

[gizmo0202]

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