DSNLess Link Azure Table To Access

[jo15765]

I am wanting to DSNLess link multiple azure sql tables to my access database. This is the code I have

Public Function AttachDSNLessTable(table)
    On Error GoTo AttachDSNLessTable_Err
    Dim td As TableDef
    Dim stConnect As String
    
    'Alter these variables to change any settings
    Dim stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String
    Dim stUsername As String, stPassword As String, S As String, pkfield As String
    localTable = Replace(table, ".", "")
        'What table name will be in access
    stLocalTableName = "Azure_" & localTable
        'What table name is in SQL Server
    stRemoteTableName = table
    pkfield = "ID"
        'Server name
    stServer = "server"
        'Database
    stDatabase = "database"
        'User
    stUsername = "user"
        'Password
    stPassword = "password"
    
    
    For Each td In CurrentDb.TableDefs
        If td.Name = stLocalTableName Then
            CurrentDb.TableDefs.Delete stLocalTableName
        End If
    Next
      
    If Len(stUsername) = 0 Then
        '//Use trusted authentication if stUsername is not supplied.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
    Else
        '//WARNING: This will save the username and the password with the linked table information.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
    End If
    Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
    CurrentDb.TableDefs.Append td
    AttachDSNLessTable = True
        
    Exit Function


AttachDSNLessTable_Err:
    
    AttachDSNLessTable = False
    MsgBox "AttachDSNLessTable encountered an unexpected error: " & Err.Description


End Function

This works fine for my inhouse SQL Server - but i get the error

AttachDSNLessTable encountered an unexpected error: ODBC--call failed

What am I missing here?

[pbaldy]

What does the finished string look like? I connect to Azure tables all the time, an example of the finished string:

ODBC;DRIVER=ODBC Driver 17 for SQL Server;SERVER=tcp:server.domain.com,1433;UID=abc;PWD=xyz;APP=Microsoft Office;DATABASE=Lease;

Also make sure the user has permissions of course, and their IP address is whitelisted if that feature is being used.

[jo15765]

What does the finished string look like? I connect to Azure tables all the time, an example of the finished string:

ODBC;DRIVER=ODBC Driver 17 for SQL Server;SERVER=tcp:server.domain.com,1433;UID=abc;PWD=xyz;APP=Microsoft Office;DATABASE=Lease;

Also make sure the user has permissions of course, and their IP address is whitelisted if that feature is being used.

IP is whitelisted and user has perms. I have verified by connecting via SSMS

The connection string looks like this

ODBC;DRIVER=SQL Server;SERVER=server;DATABASE=TestDb;UID=user;PWD= pass

[Gicu]

I would say you would need to use a newer driver with Azure instead of the legacy driver you are currently trying. MS says here it wants Microsoft ODBC Driver for SQL Server version 17.2.0.1 or later:
https://learn.microsoft.com/en-us/az...nection-string
Cheers,

[pbaldy]

"server" is an address, not just the name of a server like would work on a LAN?

[Minty]

+ 1 for both pbaldy and Gicu comments we use Azure SQL all the time.
If you can connect in SSMS check it's connection properties. They should be very similar to the ones required in Access.