Attaching tables from Azure SQL database to Access 2013 with vba Error 3170 Could not find installable ISAM

Question

I have the following code to attempt to attach Azure SQL tables to Access front end.  I get the "Could not find installable ISAM error".  I have researched extensively and can't find what I'm doing wrong.  This code works perfectly with an older Access database 2010 I think.

The purpose for me trying to link my tables directly and bypassing the DSN file is to try and increase the speed.  The database currently doesn't have that much data but one form takes nearly 2 minutes to open now.  The form was based on a 'one table' query with criteria on one field only.  In SQL we wrote several views to reduce the output in size but this has not made any difference to the speed.  I am now setting the recordsource of the form with code on the OnLoad event.  Once I've analysed which 'view' I need I'm then assigning that view to the recordsource.  Still no improvement in speed.  So now I'm trying to attach tables directly rather than through ODBC.  Any other suggestions would be appreciated.

I know the connection string is OK - it works in passthrough queries.  but I'm wondering about the ODBC Driver 13 for Server.

I have to admit that this is beyond my ability. 

These are the references I have active:

This is the code I'm using:

=================

Function AttachTable(stLocalTableName As String, stRemoteTableName As String)

On Error GoTo AttachTable_Err

    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

    stConnect = "DRIVER={ODBC Driver 13 for SQL Server};Server=tcp:xxxxxxdb.database.windows.net,1433;Database=xxxxx;Uid=brain@xxxxxxxxdb;Pwd=xxxxxxxx;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30; "

    Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)

    CurrentDb.TableDefs.Append td

    AttachTable = True

Exit Function

AttachTable_Err:

    AttachTable = False

    MsgBox "AttachTable encountered an unexpected error: " & Err.Number & ": " & Err.Description

End Function

Sub Relink_Tables()

On Error GoTo Relink_Error

    Relink_One_Table "dbo_MyTableName", "MyTableName"

    MsgBox "Tables Relinked"

    Exit Sub

Relink_Error:

    MsgBox "Error in linking " & Err.Number & ": " & Err.Description

End Sub

Sub Relink_One_Table(stAccessTable As String, stSQLTable As String)

    AttachTable stAccessTable, stSQLTable

End Sub

Many thanks in advance for any help.

SG

Answer 1

Option Explicit is there.  That's why the code wouldn't run.  I think the person who sent me this code probably had the gDbapp set as a global variable.

Thanks for pointing this out though. I really need all the help I can get.

Answer 2

> 2 minutes to open a form with only 42 records is not acceptable

I agree, and that is not the normal performance of an Azure database, regardless of how bad the database design may be.

First question: has the issue "Could not find installable ISAM" evaporated? It's in your subject line so I thought I would ask. The subject line does not say anything about slow.

Assuming slow is your issue, then we can ask some follow-up questions:

1. When issuing the same query in SQL Server Management Studio (SSMS), do we see the same performance?
2. Do other users around you see the same performance?
3. Do you see the same performance at another location (e.g. at home)?

Answer 3

> I had missed declaring the variable

Probably because you do not have Option Explicit at the top of EVERY code module (in addition to Option Compare Database). Do it right now, and also set it to be the default for new modules in Code window > Tools > Options > Require variable declaration.

No serious developer should go without this.

Then choose Debug > Compile and fix any errors that may be flagged, like undeclared variables.

Answer 4

I had missed declaring the variable

Dim gDBapp As DAO.Database

    Dim tdf As DAO.TableDef

    Dim rTables As DAO.Recordset

    Dim sql As String

    Dim TableName As String

Set gDBapp = CurrentDb()

The code then ran ok but I can't be sure it has done anything.  When I hover on the table names the string looks the same.  There's no difference to speed.

Has anyone got any suggestions on how to speed up this database?  2 minutes to open a form with only 42 records is not acceptable.

Thank you

SG

Answer 5

I tried it with DAO defined and got the same error.

I have also tried this code but it errors of the line:

                Set tdf = gDbapp.TableDefs(TableName)

Error message 421 Object required.  TableName is defined and shows as a temporary table name?

Public Sub ChangeConnection()

Dim ConnectionStringDAO As String

ConnectionStringDAO = "DRIVER={ODBC Driver 13 for SQL Server};Server=tcp:xxxxxxxxdb.database.windows.net,1433;Database=xxxxxDB;Uid=xxxxx@xxxxxxxxdb;Pwd=xxxxxxxx;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30; "

    Dim tdf As DAO.TableDef

    Dim rTables As DAO.Recordset

    Dim sql As String

    Dim TableName As String

' select linked tables.

    sql = "SELECT MSysObjects.Name FROM MSysObjects"

    sql = sql & vbNewLine & "WHERE  MSysObjects.Type IN(4,6)"

    sql = sql & vbNewLine & "ORDER BY MSysObjects.Name;"

    Set rTables = CurrentDb.OpenRecordset(sql, dbOpenSnapshot, dbSeeChanges)

Do Until rTables.EOF

        TableName = rTables!Name

        Set tdf = gDbapp.TableDefs(TableName)

If tdf.Connect <> "" Then

tdf.Connect = ConnectionStringDAO

tdf.RefreshLink

End If

rTables.MoveNext

    Loop

    rTables.Close

    gDbapp.TableDefs.Refresh

Set rTables = Nothing

Set tdf = Nothing

End Sub