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.

 

[Norie]

Why do you want to run queries in Access if you aren't interested in getting the results?

 

[Crinder]

I am going to retrieve the results further along in the process and to do that I already have a connection setup which works fine.
What I meant is that right now all I need is to be able to execute this queries (such as append, select and make table queries) to get the final data, and the exporting part of the process doesn't need modifications.

 

[RoryA]

Assuming an action query:

Sub ExecuteAccessActionQuery()
   ' Sample demonstrating how to execute an action query in an Access db
   Dim cn As ADODB.Connection, strQuery As String
   Dim strPathToDB As String
   
   ' Change path as necessary
   strPathToDB = "C:\Test\db1.accdb"
   
   Set cn = New ADODB.Connection
   With cn
      .Provider = "Microsoft.ACE.OLEDB.12.0"
      .ConnectionString = "Data Source=" & strPathToDB & ";"
      .Open
   End With
   strQuery = "qapp_Append_Query_Name"
   cn.Execute strQuery, , adCmdStoredProc
   cn.Close
   Set cn = Nothing
End Sub

 

[gino59]

Okay, sorry Crinder - I misunderstood. Not familiar with Access all that much. Can't see how the VBA would be much different as long as you've got the reference established.

Gino

 

[Crinder]

I can sense we're close, so thank all of you for your input. Gino, it's not a problem at all, I appreciate how you guys are helping just for helps sake.

Rory, I'm using your latest code and it looks great, but is giving me an error on one of the last lines

Code:

Sub ExecuteAccessActionQuery()
' Sample demonstrating how to execute an action query in an Access db
Dim cn As ADODB.Connection, strQuery As String
Dim strPathToDB As String

' Change path as necessary
strPathToDB = "C:\Users\User.Name\Desktop\Cronos\Capital6.accdb"

Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "Data Source=" & strPathToDB & ";"
.Open
End With
strQuery = "001 - Make preliminary Data and add Actuals"
cn.Execute strQuery, , adCmdStoredProc
cn.Close
Set cn = Nothing
End Sub

The error is displayed upon running "cn.Execute strQuery, , adCmdStoredProc". I can assure both the complete path and query name are correct (just in case, it's a make table query). The error is "Runtime Error -2147217865 (80040e37)': Automation Error." Any idea on what could be causing this?

Thanks.

 

[Norie]

Spaces in the query name which you can get round by enclosing the name using square brackets, or avoid by not having spaces in the name.

 

[Crinder]

Bullseye! Worked like a charm, thanks everyone! will let you know if anything else comes up.

 

[gino59]

Crinder - Norie's my hero - definitely bails my butt out of trouble all the time!

Glad it works!
Gino