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.

 

[Crinder]

Thanks Gizmo,

I'm getting a "Compile Error: User-defined type not defined" on "New ADODB.Connection". Not sure what it could be.

 

[RoryA]

You need to set a reference (Tools-References in the VBEditor) to the 'Microsoft ActiveX Data Objects 2.n Library' where n is a number that varies from system to system (usually 7 or 8 these days)

 

[Ruddles]

Crinder, your code works for me - but this is probably not what you wanted to hear!

Have you tried opening the database from another drive, or another folder on your C: drive, just to eliminate that possibility?

 

[Crinder]

Hi all,

Ruddles, I've tried other drives and the file still doesn't open, but still I'm glad it worked for somebody

Rory, I've added the reference and now the code runs smoothly, but still it doesn't do anything, at least nothing visually obvious. What I can say for sure is that it isn't opening the file. Also it has no sort of password security, I had skipped that comment before by accident.

By know you probably realized that I have close to 0 idea as to what I'm doing here. I understand VBA in excel, but connecting to access this way is new to me, so feel free to be as explicit as necessary.

Thanks to everyone for your answers.

 

[RoryA]

That code doesn't actually do anything other than open a connection to the database. You can run queries in the database, but what do you want to do with the output (if any)? You can't, as I said, run macros without opening the database.
Does your code work to open any databases at all if you point it at different ones?

 

[Crinder]

Rory, if that is the case this will be very usefull.
One more thing if it isn't a bother. could you show me a code that, after running the one you provided, would run a query in the access file??

Thanks!

 

[gino59]

Here's a sample procedure which retrieves the Access 2007 data and posts it into an Excel 2007 table. Hope it gives you an idea...
Cheers,
Gino

Option Explicit
'    ******************************************************************************
'    This procedure retrieves ALL transactions (regardless of account) from the
'    Access 2007 file (mData.accdb) and writes the data to the mData worksheet.
'    This data is sorted by date (oldest to newest) in Access 2007 and is used with
'    user queries which require all transactional data (not just account specific).
'
Sub GetINCTX()
'    This macro requires reference to the Microsoft ActiveX Data Objects 2.x Library.
'    *******************************************************************************
     Dim DBFullName As String
     Dim Cnct As String, Src As String
     Dim Connection As ADODB.Connection
     Dim Recordset As ADODB.Recordset
     Dim Col As Integer
     Dim LR As Long
'    *******************************************************************************
'    Turn off screen updating, select the appropriate sheet, and clear all cells.
     Application.ScreenUpdating = False
     Worksheets("Income").Activate
     Cells.Clear
'    *******************************************************************************
'    The database path information is defined.  Note the Access database MUST reside
'    within the same directory as this workbook!
     DBFullName = ThisWorkbook.Path & "\mdata.accdb"
'    *******************************************************************************
'    Open the connection to the data source.
     Set Connection = New ADODB.Connection
     Cnct = "Provider=Microsoft.ACE.OLEDB.12.0;"
     Cnct = Cnct & "Data Source=" & DBFullName & ";"
     Connection.Open ConnectionString:=Cnct
'    *******************************************************************************
'    Create the new RecordSet.
     Set Recordset = New ADODB.Recordset
          With Recordset
'    Define the appropriate Filter(s) and notify the user of the selection criteria.
               Src = "Select * from mData where Tag = 'GHI' "
               Src = Src & "or Tag = 'DEF' "
               Src = Src & "or Tag = 'LMN'"
               .Open Source:=Src, ActiveConnection:=Connection
'    Write the field names.
          For Col = 0 To Recordset.Fields.Count - 1
               Range("A1").Offset(0, Col).Value = Recordset.Fields(Col).Name
          Next
'    Write the recordset.
               Range("A1").Offset(1, 0).CopyFromRecordset Recordset
          End With
     Set Recordset = Nothing
          Connection.Close
     Set Connection = Nothing
'    *******************************************************************************
'    Create and format the table from the Recordset.
     LR = Cells(Rows.Count, "A").End(xlUp).Row
     Range("A1").Select
          ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$M" & LR), _
                    , xlYes).Name = "INC2tbl"
          ActiveSheet.ListObjects("INC2tbl").ShowTotals = True
     ActiveSheet.Columns.AutoFit
'    *******************************************************************************
'    Return to the Main Worksheet, turn screen updating back on and notify the user.
     Sheets("Main").Activate
          Range("A1").Select
     Application.ScreenUpdating = True
'    *******************************************************************************
End Sub

 

[Crinder]

Thanks for the example Gino, I'll surely be able to get something out of it.

I'm currently trying to adapt the lines of code Rory provided to actually run a query. This is what I'm trying:

Sub Access()
Dim TARGET_DB As String
TARGET_DB = "Capital6.accdb"
Set cnn = New ADODB.Connection
myConn = ThisWorkbook.Path & Application.PathSeparator & TARGET_DB
With cnn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Open myConn
.DoCmd.OpenQuery ("001 - Make preliminary Data and add Actuals")
End With

End Sub

the only line I added to her original code is the one starting with ".Docmd" and as expected it is causing an error "Run time Error 3001 Application-Defined or Object-defined error" this probably isn't the right way to run the query, but it's all I could think of. Any suggestions?

 

[Norie]

What do you actually want to do?

If you just want to get the results of a query into Excel you could try something like this.

Option Explicit
Sub AccessToXL()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim myconn As String
Dim TARGET_DB As String
Dim qry As String
Dim strSQL As String
 
    TARGET_DB = "Capital6.accdb"
 
    qry = "001 - Make preliminary Data and add Actuals"
 
    strSQL = "SELECT * FROM [" & qry & "]"
 
    Set cnn = New ADODB.Connection
 
    Set rst = New ADODB.Recordset
 
    myconn = ThisWorkbook.Path & Application.PathSeparator & TARGET_DB
 
    With cnn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .Open myconn
    End With
 
    rst.Open "SELECT * FROM [001 - Make preliminary Data and add Actuals]", conn
 
    Worksheets("Sheet1").Range("A2").CopyFromRecordset rst
 
End Sub

 

[Crinder]

Thanks, but I don't want to get the data into Excel, I just want to run the query within Access using Excel vba to execute it.

This is the whole idea: I have a process which runs partly in Excel and partly in access, what I would like to do is to execute all parts of the process using Excel VBA. So what I need to be able to, from Excel, give directions to both programs, Excel and Access.

I already got the Excel VBA for excel part down, now I need to know how to, through excel vba, execute queries within Access without any need to export the data in any form.

Thanks again.