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

[Legal Tender]

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.

    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:

 

[Legal Tender]

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.

 

[JEC]

Better way nowadays is to use PowerQuery