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

[David Emerson]

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).

 

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