Querying access db from excel - 32 bit to 64 bit conversion

Legal Tender

New Member
Joined
May 9, 2014
Messages
25
So I have some excel code I wrote years ago to query access databases that someone else set up (I'm decent in excel but know next to nothing about access), I was on vacation last week and when I came back yesterday the IT department apparently upgraded our excel from excel 2016 to office 365, and also went from a 32 bit version to a 64 bit version. So the way in which I query the access databases using excel is no longer working. I initially thought I would just need to update the references from Microsoft Access 16.0 Object Library to a higher version but it seems like excel 2016 and excel 365 are both considered to be version 16.0 so that wasn't it. I then thought that my code needed to be adjusted by adding ptrsafe to some of the top line declare statements which I'm pretty sure I do need to leave those changes intact that but that still didn't solve the issue. From my last round of googling it seems like the way I am using the statement "Microsoft.Jet.OLEDB.4.0" might be the problem as that might no longer be a supported method of querying the database. Below is an example of the simplest query in the macro, if someone could show me how to edit this to get it running I should be able to edit the rest of them in a similar manner, thank you for your consideration.


VBA Code:
    Dim cnn1 As ADODB.Connection
    Dim rs1 As ADODB.Recordset
    Dim sQRY1 As String
    Dim strFilePath1 As String
    strFilePath1 = Sheet1.Cells(5, 6).Value
    Set cnn1 = New ADODB.Connection
    Set rs1 = New ADODB.Recordset
    cnn1.Provider = "Microsoft.Jet.OLEDB.4.0"
    cnn1.Properties("Data Source") = strFilePath1
    cnn1.Open
       
    sQRY1 = "SELECT [All Interface].[Fisc Date] FROM [All Interface] GROUP BY [All Interface].[Fisc Date];"
   
    rs1.CursorLocation = adUseClient
    rs1.Open sQRY1, cnn1, adOpenStatic, adLockReadOnly
    ActiveSheet.Range("H16").CopyFromRecordset rs1
    rs1.Close
    Set rs1 = Nothing
    cnn1.Close
    Set cnn1 = Nothing

And if it helps these are the references I have in the workbook:
 

Attachments

  • Capture refs.JPG
    Capture refs.JPG
    42 KB · Views: 24

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
I think I found it now, the line that needed to be changed was the line:

cnn1.Provider = "Microsoft.Jet.OLEDB.4.0"

and i changed it to

cnn1.Provider = "Microsoft.Ace.OLEDB.12.0"

and the queries are working again, in case anyone else comes across a similar issue.
 
Upvote 0
Solution
Better way nowadays is to use PowerQuery
 
Upvote 0

Forum statistics

Threads
1,224,849
Messages
6,181,387
Members
453,034
Latest member
mikdadhussain

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top