Open Access database from excel vba

[tdesilva]

Hi,
I am trying to open access database using excel application. I am using Office 2010.
my code in excel vba is
Dim db As Object
Set db = VBA.CreateObject ("Access.Application")
db.OpenCurrentDatabase "L:\Client Database\Access_fe.mdb"
db.Application.Visible = True

I have error message
Run-time error '429';
ActiveX component can't create object
at line no 2 where CreateObject ("Access.Application")

my references are:
Visual basic for Application
Microsoft Excel 14.0 Object Library
OLE Automation
Microsoft Office 14.0 Object Library
Microsoft Forum 2.0 Object Library
Microsoft Scripting Runtime
Microsoft Access 14.0 Object Library
Microsoft ActiveX Data Objects 2.8 Library
Microsoft Script control 1.0

Please let me know how to overcome this issue
Thanks

 

[Gasman]

a. Would it not be just CreateObject ?
b. I believe the db would be set to the file name not the access app.?

so

objAccess = CreateObject....
objAccess.OpenCurrentDatabase ...
debug.print CurrentDB.name

 

[isladogs]

Depends whether or not the db is already open.
If its already in use, use GetObject instead.

 

[background_r]

Don't try to open the Access application then; just create a connection object using one of the Data Access technologies

 

[tdesilva]

I can open database in excel application with the below code in Excel 2013
Dim cstrDbFile As String
Dim objShell As Object
cstrDbFile = "C:\Database1.accdb"
Set objShell = VBA.CreateObject("WScript.Shell")
objShell.Run cstrDbFile

At work, I have access database in .mdb format as some of the users are still using Access2003.
Above code cannot be used to open .mdb database.

I have excel application where I have to add a button to open access database

Is there a method I can use to open access database in .mdb format from an excel application

 

[Gasman]

That code works fine with my mdb ?

Sub OpenAccess()
Dim cstrDbFile As String
Dim objShell As Object
cstrDbFile = "C:\Users\Paul\Documents\SSAFA\SSAFA.mdb"
Set objShell = VBA.CreateObject("WScript.Shell")
objShell.Run cstrDbFile
End Sub

 

[tdesilva]

Thanks, What is the version of excel did you use? 2010 or 2013

 

[tdesilva]

I can run the above script opening .mdb in 64 bit operating system but when I run in 32 bit operating system I get the error run time error 2147024703 ((800700c1)
Method 'Run' of the Object IWshShell3 failed

Is there another method of running shell command in 32 bit operating system

 

[Gasman]

I only have 2007 and only 32bit

What if you were to run the shell command as 'access.exe FileToOpen' ?

 

[tdesilva]

Thanks it worked