A question about DAO

[termeric]

Good Morning,

I've used the same piece of code for quite some time whihc uses DAO to pull a query form access into excel. this has always been used with the MDB formatted database in the past. Recently, i created a new database, using the newer ACCDB format. When i went to use my code to pull a query over, i received this message.

Run-time error '3343':
Unrecognizes database format 'C:\data\RenewalDB.accdb'


my code is below, the only change i made form it work with MDB to not workign with ACCDB was to change to database path and the Query name. I also took the pasword out from this line Set db = DAO.DBEngine.Workspaces(0).OpenDatabase("C:\Data\SSTracking.mdb", False, False, ";PWD=xxxx")

any ideas?

Private Sub FirstStep()
'GetQueryDAO
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Integer, iNumCols As Integer
Dim wb As Workbook, ws As Worksheet, ws2 As Worksheet
 
 
'--------------------------------------------------------------------
'Requires a reference to the DAO Object Library
'   1) in the visual basic editor, go to Tools | References
'   2) check the box for Microsoft DAO 3.6 Object Library (or higher)
'--------------------------------------------------------------------
    
    
'** Suspend automatic Calculations
    Application.Calculation = xlCalculationManual
    
'** Connect to the Database
    Set db = DAO.DBEngine.Workspaces(0).OpenDatabase("C:\Data\Renewaldb.accdb", False, False)
    Set rs = db.OpenRecordset("Assignments All Lines", dbOpenSnapshot)
    
  
'** Unhide, Select Paste and clear the old data
    Set ws = Sheets("Sheet1")
    With ws
        .Visible = True
        .Select
        .Cells.ClearContents
    End With
    
 
    
'** Write Column Headers
    iNumCols = rs.Fields.Count
    For i = 1 To iNumCols
        ws.Cells(1, i).Value = rs.Fields(i - 1).Name
    Next
 
'** Write the data
    ws.Range("A2").CopyFromRecordset rs
    'wb.Activate
    'ws.Activate
    
'** Close the connections
    rs.Close
    db.Close
 
'** Updates the Translate Formulas  and Reactivate calculations
    Application.Calculation = xlCalculationAutomatic

Thanks,

Christian

 

[RoryA]

Change your references - uncheck the DAO 3.6 library and use the Microsoft Office Access Database Engine instead.

 

[termeric]

change your references - uncheck the dao 3.6 library and use the microsoft office access database engine instead.

thank you!