[AccessD] [dba-SQLServer] DSN Tables - Could not find installable ISAM

[David Emerson]

Thanks Gustav, much appreciated.

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Gustav Brock via AccessD
Sent: Wednesday, 18 December 2019 8:02 p.m.
To: Access Developers discussion and problem solving
Cc: Gustav Brock
Subject: Re: [AccessD] [dba-SQLServer] DSN Tables - Could not find
installable ISAM

Hi David

Yes. Use the latest ODBC driver and a connection string like:

DRIVER=SQL Server Native Client 17.0;Description=Your application
name;APP=Microsoft®
Access;SERVER=tcp:YourInstanceName.database.windows.net,1433;DATABASE=YourDb
Name;Trusted_Connection=No;UID=Username at YourInstanceName;PWD=YourPassword;

We have these two functions to control that and relink the tables and PT
queries:

<code>
' Create ODBC connection string from its variable elements.
'
' 2018-10-02. Cactus Data ApS, CPH.
'
Public Function ConnectionString( _
    ByVal Hostname As String, _
    ByVal Database As String, _
    ByVal Username As String, _
    ByVal Password As String) _
    As String

    Const AzureDomain   As String = ".windows.net"
    Const OdbcConnect   As String = _
        "ODBC;" & _
        "DRIVER=SQL Server Native Client 11.0;" & _
        "Description=Cactus TimeSag og Finans;" & _
        "APP=Microsoft® Access;" & _
        "SERVER={0};" & _
        "DATABASE={1};" & _
        "UID={2};" & _
        "PWD={3};" & _
        "Trusted_Connection={4};"
        
'    Examples (Note, optional encryption):
'        "ODBC;Driver=SQL
Server;Server=tcp:b8uros8p4x.database.windows.net;Database=cactus;UID=cactus
@ b8uros8p4x;Pwd=Password;Encrypt=yes"
'
'        "ODBC;Driver=SQL Server Native Client
17.0;Server=(localdb)\MSSQLLocalDB;Database=Test;Trusted_Connection=Yes"

    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)
    FullConnect = Replace(FullConnect, "{4}", IIf(Username & Password = "",
"Yes", "No"))
    
    ConnectionString = FullConnect

End Function


' Attach all tables linked via ODBC to SQL Server or Azure SQL.
'
' 2016-04-24. Cactus Data ApS, CPH.
'
Public Function AttachSqlServer( _
    ByVal Hostname As String, _
    ByVal Database As String, _
    ByVal Username As String, _
    ByVal Password As String) _
    As Boolean

    Const cstrDbType    As String = "ODBC"
    Const cstrAcPrefix  As String = "dbo_"

    Dim dbs             As DAO.Database
    Dim tdf             As DAO.TableDef
    Dim qdf             As DAO.QueryDef
    
    Dim strConnect      As String
    Dim strName         As String
    
    On Error GoTo Err_AttachSqlServer
    
    Set dbs = CurrentDb
    strConnect = ConnectionString(Hostname, Database, Username, Password)
    
    For Each tdf In dbs.TableDefs
        strName = tdf.Name
        If Asc(strName) <> Asc("~") Then
            If InStr(tdf.Connect, cstrDbType) = 1 Then
                If Left(strName, Len(cstrAcPrefix)) = cstrAcPrefix Then
                    tdf.Name = Mid(strName, Len(cstrAcPrefix) + 1)
                End If
                tdf.Connect = strConnect
                tdf.RefreshLink
                Debug.Print Timer, tdf.Name, tdf.SourceTableName,
tdf.Connect
                DoEvents
            End If
        End If
    Next
    
    For Each qdf In dbs.QueryDefs
        If qdf.Connect <> "" Then
            Debug.Print Timer, qdf.Name, qdf.Type, qdf.Connect
            qdf.Connect = strConnect
        End If
    Next
    Debug.Print "Done!"
    
    AttachSqlServer = True
    
Exit_AttachSqlServer:
    Set tdf = Nothing
    Set dbs = Nothing
    Exit Function
    
Err_AttachSqlServer:
    Call ErrorMox
    Resume Exit_AttachSqlServer
    
End Function
</code>

/gustav

-----Oprindelig meddelelse-----
Fra: AccessD <accessd-bounces at databaseadvisors.com> På vegne af David
Emerson
Sendt: 17. december 2019 19:30
Til: 'Access Developers discussion and problem solving'
<accessd at databaseadvisors.com>
Emne: Re: [AccessD] [dba-SQLServer] DSN Tables - Could not find installable
ISAM

Does anyone have experience with linking tables to Azure?

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com