CODEWITHSUNDEEP
excelvbams-access
JSwordy
JSwordy

Reputation: 169

Access Database connection for accdb file

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!

Upvotes: 0

Views: 3540

Answers (1)

braX
braX

Reputation: 11755

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.

Upvotes: 2

Related Questions