ODBC Driver Struggles on new computers

[BallunarCrew]

I have an Access database that reads data from a SQL Server database on Azure. It has been running for several years with this configuration and no problems. We now have new workstations, running Windows 11 (not sure if it is professional or not). We have downloaded and installed the ODBC driver but continue to get errors from the Access database that the ODBC Connection to "SQL Server Native Client 11.0 dbname failed.
On one of the new computers we installed SQL Server Management Studio (SSMS) and that installed the desired driver and that one works fine but one all the other computers it did not work. We tried to download just the ODBC driver as well as trying the SSMS route. The Microsoft site says Driver 18 is available - is that the problem? It is 18 and we are trying to connect to 11? If so, how does it work on one of 7 computers. Any help is appreciated.

 

[SoftwareRT]

1) Your old Access database is using an ODBC connection with SQL Server Native Client 11.0 (which is actually quite old — it's from around SQL Server 2012).
2) On Windows 11, Microsoft no longer installs SQL Server Native Client 11.0 by default. Instead, they push ODBC Driver 17 or ODBC Driver 18 for SQL Server.
3) ODBC Driver 18 is a newer driver, and it enforces different security defaults (such as requiring encrypted connections and stricter certificate checks).
4) The reason it "works on 1 out of 7" is likely because SSMS installed Native Client 11.0 only on that machine (some packages still us the old client).

Suggested Fix: Update Access to Use ODBC Driver 17 or 18

• Modify the Access ODBC connection to use "ODBC Driver 17 for SQL Server" or "ODBC Driver 18 for SQL Server".
• BUT if you use Driver 18, you need to adjust the connection string slightly to either: Allow untrusted certificates (for testing) OR Set up SSL properly (production).

Example updated connection string (for Driver 18):

Driver={ODBC Driver 18 for SQL Server};Server=tcp:yourserver.database.windows.net,1433;Database=yourdatabase;Uid=youruser;Pwd=yourpassword;Encrypt=yes;TrustServerCertificate=yes;


Note: The TrustServerCertificate=yes; is critical unless you have a trusted SSL cert set up. Azure SQL Database (and SQL Server on Azure VMs) already has encryption on by default.

Side Note: I have no idea how many tables you have in access - - Normally, Microsoft expects you to delete and re-link every table by hand, one-by-one. If you have 5 tables it’s annoying. If you have 50 or 100 tables it’s a nightmare, if so use the code below to re-link…

    Sub RelinkAllTables()
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim oldDriverPart As String
    Dim newDriverPart As String

    ' *** Change these two lines to match your old and new driver parts ***
    oldDriverPart = "Driver={SQL Server Native Client 11.0}"
    newDriverPart = "Driver={ODBC Driver 17 for SQL Server}"

    Set db = CurrentDb

    For Each tdf In db.TableDefs
        ' Only update linked tables (those with a non-empty Connect string)
        If Len(tdf.Connect) > 0 Then
            If InStr(tdf.Connect, oldDriverPart) > 0 Then
                Debug.Print "Updating: " & tdf.Name
                tdf.Connect = Replace(tdf.Connect, oldDriverPart, newDriverPart)
                tdf.RefreshLink
            End If
        End If
    Next tdf

    Set db = Nothing
    Set tdf = Nothing

    MsgBox "Relinking complete!", vbInformation
End Sub