Access Database connection for accdb file

Question

I am trying to run a SQL query in VBA that will print out the result to an Excel sheet with the following code:

Sub Select_From_Access()
   Dim cn As Object, rs As Object
   Dim intColIndex As Integer
   Dim DBFullName As String
   Dim TargetRange As Range

   DBFullName = "C:Users\jswizzle\Testdb.accdb"


   Application.ScreenUpdating = False

   Set TargetRange = Sheets("Sheet1").Range("A1")

   Set cn = CreateObject("ADODB.Connection")
   cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DBFullName & ";"

   Set rs = CreateObject("ADODB.Recordset")
   rs.Open "SELECT * FROM Region", cn, , , adCmdText

   For intColIndex = 0 To rs.Fields.Count - 1
   TargetRange.Offset(1, intColIndex).Value = rs.Fields(intColIndex).Name
   Next

   TargetRange.Offset(1, 0).CopyFromRecordset rs

   Application.ScreenUpdating = True
   On Error Resume Next
   rs.Close
   Set rs = Nothing
   cn.Close
   Set cn = Nothing
   On Error GoTo 0
   Exit Sub

End Sub

Unfortunately I keep getting the following error:

Run-time error '-2147467259 (80004005)';

Unrecognized database format 'C:Users\jswizzle\Testdb.accdb'.

I am using as a reference Microsoft Activex Data Objects 2.8 Library.

Please let me know what needs to be changed, thank you!

Answer 1

Microsoft.Jet.OLEDB.4.0 is used to connect to the older Access MDB files.

If you are going to use an ACCDB file (the newer version) then you should use Microsoft.ACE.OLEDB.12.0 instead.