Newer O365 versions and Jet/Ace not working

[brusk]

I've had a workbook working without issue for at least 6 years with the occasional issues of changing things as the company moved from Office whatever version to O365 and then from 32 to 64 bit but never a big deal. I've ran into a road block as the new laptops IT is sending out to other users are causing crazy issues. My workbook relied heavily on Ace to do internal queries to about 40 tables I have setup on a hidden sheet. I started having users complain and was able to switch over the Jet and that fixed a few. Now I'm having every new install failing on that portion. In the thoughts that IT was giving people a stripped down install I downloaded O365 2021 and put it on a dev machine to find out it also does not work.

Is there an older addon that's missing for the drivers, DLL's etc for Ace or Jet to run?

 

[brusk]

I think I had it backwards. I was running Jet 4.0 and switched over to ACE 12.0.

 

[johnnyL]

Can you provide an example of a line of code with ACE 12.0 that causes issues?

 

[brusk]

Can you provide an example of a line of code with ACE 12.0 that causes issues?

It's not technically failing, it's just now failing to return any data. No errors just 0 records returned every time.

    Dim RecordSet As Object ' ADODB.RecordSet
    Dim ConnectionString As String
    Dim Column As Long
    Dim row As Long
    Dim ReturnArray As Boolean
    Dim RecordCount As Long
    Call WriteLogFile("RunSQLQuery: SQLCommand:" & SQLCommand, True)
    On Error GoTo ERROR
    Set RecordSet = CreateObject("ADODB.RecordSet") ' New ADODB.RecordSet
     
    If IsArray(Destination) Then
        If UBound(Destination) = -1 Then ReturnArray = True
    End If
    Debug.Print SQLCommand
    
    ConnectionString = "Provider = Microsoft.Ace.OLEDB.12.0;" & _
        "Data Source=" & ThisWorkbook.FullName & ";" & _
        "Extended Properties='Excel 8.0;hdr=yes;IMEX=1'"
    RecordSet.Open SQLCommand, ConnectionString, adOpenDynamic, adLockReadOnly, adCmdText

 

[johnnyL]

Try this for your ConnectionString:

ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & _
        ";Extended Properties=""Excel 12.0 Xml;HDR=YES"";"                                          ' Open the connection to the source file

 

[brusk]

I've only heard back from one user but that seems to have fixed his laptop. Appreciate the help.

 

[johnnyL]

Appreciate the update @brusk.