Run-time Error on Method 'Open' of object '_Connection'

Hi, all

I have this one file that several users have to run. It can run from my end perfectly but not from other users' end (have tried with 2 users so far).

This error has been prompted:
Annotation 2020-11-11 154710.jpg

And the line highlighted is at:

Sub GenerateOrder()

Response = MsgBox("Generate new orders?", vbYesNo, "AIMSS Output to MVS Scheduler") 'Did you check the dates?
If Response = vbYes Then

    Range("A2:E" & Range("A" & Rows.Count).End(xlDown).Row).ClearContents

    Dim fn As String, myList, i As Long, ii As Long, sql As String
    Dim cn As Object, rs As Object
    fn = Application.GetOpenFilename("ExcelBooks,*.xls*")
    If fn = "False" Then Exit Sub
    myList = Sheets("Variables").Cells(1).CurrentRegion.Value
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    With cn
        .Provider = "Microsoft.Ace.OLEDB.12.0"
        .Properties("Extended Properties") = "Excel 12.0;HDF=Yes;"
        .Open fn
    End With
    With Sheets("Orders")
        .Cells.ClearContents
        For i = 2 To UBound(myList, 1)
            sql = "Select '" & myList(i, 2) & "' As `" & myList(1, 2) & "`, "
            sql = sql & "'" & myList(i, 4) & "' As `" & myList(1, 4) & "`, "
            sql = sql & "`" & myList(i, 5) & "`, `Date` From `result_coldbox$`"
            sql = sql & " Where `" & myList(1, 3) & "` = '" & myList(i, 3) & "'"
            rs.Open sql, cn
            If i = 2 Then
                For ii = 0 To rs.Fields.Count - 1
                    .Cells(1, ii + 1) = rs.Fields(ii).Name
                Next
            End If
            .Range("a" & Rows.Count).End(xlUp)(2).CopyFromRecordset rs
            rs.Close
        Next
    End With
    Set cn = Nothing: Set rs = Nothing
    
Dim txtrange As Range
Set txtrange = ThisWorkbook.Sheets("Orders").Range("C1")
txtrange.Value = "Valeur"

Dim head As Range
Set head = ThisWorkbook.Sheets("Orders").Range("E1")
head.Value = "Utilisateur"

Range("D2:D200").NumberFormat = "yyyy-mm-dd hh:mm"

'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    
'CREATING USERNAME

Dim ComputerName, Username As String

'Getting user name
Username = Environ("username")

ActiveSheet.Range("E2").Select

ActiveCell.Value = Username & "@as.corp.airliquide.com"
Selection.AutoFill Destination:=Range("E2:E" & Range("D" & Rows.Count).End(xlUp).Row)

Range(Selection, Selection.End(xlDown)).Select

Worksheets("Orders").Range("A2:E" & Range("A" & Rows.Count).End(xlUp).Row).Sort Key1:=Range("B1"), Key2:=Range("D1")
    Worksheets("Orders").Range("C2:C" & Range("A" & Rows.Count).End(xlUp).Row).NumberFormat = "0.00"
    Worksheets("Orders").Range("D2:D" & Range("A" & Rows.Count).End(xlUp).Row).NumberFormat = "dd/mm/yyyy hh:mm"
    
 FIN = Sheets("Orders").Range("C" & Range("A" & Rows.Count).End(xlUp).Row).End(xlUp).Row
    Dim K As Integer
   
    For K = 2 To FIN
 
        Range("C" & K).Select
        If Sheets("Consignes").Range("C" & K).Value = 1 Then
        Range("C" & K).Select
        Range("C" & K).NumberFormat = "0"
        ElseIf Sheets("Consignes").Range("C" & K).Value = 0 Then
        Range("C" & K).NumberFormat = "0"
        End If
        
    Next K
    
    End If
    
Dim Fldr As String

Response = MsgBox("Save a copy of this file?", vbYesNo, "Save As") 'Did you check the dates?
If Response = vbYes Then

   With Application.FileDialog(4)
      .AllowMultiSelect = False
      If .Show <> -1 Then Exit Sub
      Fldr = .SelectedItems(1)
   End With
    
ActiveWorkbook.SaveAs Fldr & "\" & "YOKK_MVS_" & Format(Now(), "DD-MMM-YYYY") & ".xlsb"

Else

    Exit Sub

End If

End Sub

The tough part here is that this issue happens to some other people except me. With my limited experience, I'm not really sure how to debug this issue. Perhaps anyone who are used to this kind of error can give some insights?

Thanks!

Hi, Kaper

I've compared the other users' References with mine, and we only ticked this:
Annotation 2020-11-11 193049.jpg

I did not tick the object library you mentioned but still manage to run the program, but not for other users unfortunately.

Originally Posted by rorya

Your code is late bound, so you don't need a reference

Hi, Rorya

May I know what late bound means?

I have corrected the typo, still does not work on the other users. And our Office version is 2010, all of us.

Originally Posted by rorya

If that doesn't help, do you know what version of Office the problem machines have on them?

I just realized something, I found that my Office 2010 version is different:

Attachment 703759

Meanwhile other users' version is:

Attachment 703760

But in order for me to persuade my IT guy to somehow 'downgrade' other users' version to match with mine, I might need to pinpoint whether this is the actual cause. As what my IT dept mentioned, all versions under Microsoft 2010 have been standardized.

I wonder if can this cause the issue I'm facing? From my understanding, other users' version is the latest one compared to mine.

Any insight would be much appreciated. Thanks!

Originally Posted by rorya

Are you all opening the same format workbooks? Your code is for xlsb files.

Yes indeed, they should be using the same format workbooks.

Oh, my bad. Didn't notice that.

For my excel version, it's the image my_excel_sp1 (Version 14.0.06023.1000)

Meanwhile, for the failed users', it's the image other_users_excel as attached (Version 14.0.07015.1000)

Originally Posted by rorya

The error is usually down to a bad connection string (but you've corrected that) or missing OLEDB providers. You might ask IT or the users to check in the registry and make sure there is a HKEY_CLASSES_ROOT\Microsoft.ACE.OLEDB.12.0 key there.

I have checked their registry, and they do have the key mentioned I'm getting more confused now.