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

[David Emerson]

Does anyone have experience with linking tables to Azure?

-----Original Message-----
From: dba-SQLServer [mailto:dba-sqlserver-bounces at databaseadvisors.com] On
Behalf Of David Emerson
Sent: Tuesday, 17 December 2019 9:55 a.m.
To: AccessDSQL
Subject: [dba-SQLServer] DSN Tables - Could not find installable ISAM

Hi Team,

I have an Access 2016 accdb with code to link SQL tables.  The code works
for SQL Server and we are trying to migrate the database to Azure.

When I run the code it gives me error 3170 - Could not find installable
ISAM.

The connection string works when I use it to connect to the Azure database
(I have changed the password for this email).  It is just the table linking
that doesn't work.

Can anyone give me a pointer as to where I can look for a solution?

Public Function AttachDSNLessTable(stLocalTableName As String,
stRemoteTableName As String, stServer As String, stDatabase As String,
Optional stUsername As String, Optional stPassword As String, Optional
stKeyfield As String)
'//Name     :   AttachDSNLessTable
'//Purpose  :   Create a linked table to SQL Server without using a DSN
'//Parameters
'//     stLocalTableName: Name of the table that you are creating in the
current database
'//     stRemoteTableName: Name of the table that you are linking to on the
SQL Server database
'//     stServer: Name of the SQL Server that you are linking to
'//     stDatabase: Name of the SQL Server database that you are linking to
'//     stUsername: Name of the SQL Server user who can connect to SQL
Server, leave blank to use a Trusted Connection
'//     stPassword: SQL Server user password
'//     stKeyfield: Name of field if stRemoteTableName is a view. This is
used to set up an index so data can be edited
    
    On Error GoTo Err_AttachDSNLessTable

    Dim td As TableDef
    Dim stConnect As String
    
    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 = "Driver={ODBC Driver 13 for SQL
Server};Server=tcp:prism-uat.database.windows.net,1433;Database=Prism-UAT;Ui
d=SUPPORT-Dalyn at xxxxxx.co.nz;Pwd=MYSECUREPWD;Encrypt=yes;TrustServerCertific
ate=no;Connection Timeout=30;Authentication=ActiveDirectoryPassword"
    End If
    Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD,
stRemoteTableName, stConnect)
    CurrentDb.TableDefs.Append td
    
    If Len(stKeyfield) > 0 Then
        CurrentDb.Execute "CREATE UNIQUE INDEX 'PrimaryKey' ON " &
stLocalTableName & " (" & stKeyfield & ") WITH PRIMARY"
    End If
    
    AttachDSNLessTable = True
    Exit Function

Exit_AttachDSNLessTable:
    Exit Function

Err_AttachDSNLessTable:
    AttachDSNLessTable = False
    
   
    Select Case Err
        Case 0
            MsgBox "0 error", vbCritical, "Error heading"
        Case 2001 ' Event canceled
        Case 2046 ' Not able to save
            Resume Next
        Case Else
            Call basErrorMsg("AttachDSNLessTable")
    End Select
    Resume Exit_AttachDSNLessTable

End Function

Regards

David Emerson
Dalyn Software Ltd
Wellington, New Zealand