MS Access 2016 OLEDB connection to SQLEXPRESS

? LinkTable("yourazurehost.database.windows.net", "databasename", "userid", "password")


' Link a table.
Public Function LinkTable( _
    ByVal Hostname As String, _
    ByVal Database As String, _
    ByVal Username As String, _
    ByVal PassWord As String)

    Dim db      As DAO.Database
    Dim td      As DAO.TableDef
    
    Set db = CurrentDb
    Set td = db.CreateTableDef()
    
    td.Connect = ConnectionString(Hostname, Database, Username, PassWord)
    
    td.SourceTableName = "ServerTableName"
    td.Name = "LinkedTableName"
    
    db.TableDefs.Append td

End Function


Public Function ConnectionString( _
    ByVal Hostname As String, _
    ByVal Database As String, _
    ByVal Username As String, _
    ByVal PassWord As String) _
    As String

' Create ODBC connection string from its variable elements.
' 2016-04-24. Cactus Data ApS, CPH.

    Const AzureDomain   As String = ".windows.net"
    Const OdbcConnect   As String = _
        "ODBC;" & _
        "DRIVER=SQL Server Native Client 11.0;" & _
        "Description=Some description;" & _
        "APP=Microsoft Access;" & _
        "SERVER={0};" & _
        "DATABASE={1};" & _
        "UID={2};" & _
        "PWD={3};" & _
        "Trusted_Connection=No;"
        
    Dim FullConnect     As String
    
    If Right(Hostname, Len(AzureDomain)) = AzureDomain Then
        ' Azure SQL connection.
        ' Append servername to username.
        Username = Username & "@" & Split(Hostname)(0)
    End If
    FullConnect = OdbcConnect
    FullConnect = Replace(FullConnect, "{0}", Hostname)
    FullConnect = Replace(FullConnect, "{1}", Database)
    FullConnect = Replace(FullConnect, "{2}", Username)
    FullConnect = Replace(FullConnect, "{3}", PassWord)
    
    ConnectionString = FullConnect

End Function

Select all Open in new window