Unrecognized database format

[Derek McGill]

Any ideas as to why .accdb gives an error and .mdb works in this code ?

Sub accessimport()

Dim db As Database
Dim ws As Workspace
Dim acsql As String
Dim recSet As DAO.Recordset

Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase _
("C:\Users\wewe\Desktop\Rating Data for Excel v1.accdb")
'("C:\Users\wewe\Desktop\RatingDataBase.mdb")
Set recSet = db.OpenRecordset("Rating_Data_for_Excel_V1")
Set Rng = Sheet2.Range("A1")
lFieldCount = recSet.Fields.Count

For i = 0 To lFieldCount - 1
Rng.Offset(0, i).Value = recSet.Fields(i).Name
Next i
Rng.Offset(1, 0).CopyFromRecordset recSet

recSet.Close
Set ws = Nothing
Set db = Nothing

End Sub
[\CODE]

 

[Chihiro]

Always specify what error message (and code) you are getting and at what line, when asking for help on VBA errors. Otherwise, we'll be shooting in the dark.

Edit: My guess, is that you don't have proper reference to database library.

 

[Debaser]

Are you using a DAO3.6 reference? If so, that predates the .accdb format. You need to use the relevant Microsoft Office 12.0 Access database engine Object Library (14.0 for Office 2010, 15.0 for 2013 and 16.0 for 2016)

 

[Derek McGill]

Error 3343
Unrecognised database format
At
("C:\Users\wewe\Desktop\Rating Data for Excel v1.accdb")

 

[Chihiro]

What Debaser said. Use Microsoft Office xx.0 Access Database Engine Object reference.

 

[Derek McGill]

Hi Debaser / Chihiro
It was Microsoft Office 12.0 Access database engine Object Library

 

[Debaser]

Do you mean you already had that set and not DAO3.6?

 

[Derek McGill]

DAO3.6 was set, code worked when i set Microsoft Office 12.0 Access database engine Object Library.