Link a SQL Server table in MS Access 2010 using VBA - ISAM Error

I have an MS Access 2010 32-bit database that is primarily a front-end. The data is stored in an SQL Server 2008 R2 managed by my company's IT.

For the most part I've been accessing the SQL Server's tables/queries by linking to the tables/queries within Access. I made these linked tables manually through Access's interface.

I have a need to link to a SQL Server table via VBA (the table name changes depending on user).

I have followed examples from the internet on how to do this. Unfortunately the farthest I get is an error message when I try to create the linked table.

Error reads: "Could not find installable ISAM."

There are some theories that you need to mess with your pc's registry or re-install certain windows component, but there's a lot of people saying the connection string is typically the source of the error -- or more specifically, an improper connection string.

I've been trying various strings all day with no luck. Hopefully someone here can point me in the right direction!

Below is what I've been using for all kinds of SQL queries to the server which works perfectly fine (server name redacted). Using the same connection string to make the linked table does not work, runs into that error message.

Public Const CONNECTION_STRING As String = _
    "Provider=SQLOLEDB;Data Source=MyServer;" & _
    "Initial Catalog=MyDatabase;" & _
    "Integrated Security=SSPI;"

I've tried using the connection definition backing the tables I manually linked. This can be obtained using this code:

debug.print CurrentDb().TableDefs("dbo_tblExampleTable").Connect

That didn't work at all, which was surprising. Returns error: data source name not found and no default driver specifed:

Const CXN_STRING_FROM_DB_DEFINITION As String = _
        "ODBC;DSN=MyServerDSN;Description=blah blah;Trusted_Connection=Yes;APP=Microsoft Office 2010;DATABASE=MyDatabase"

I tried the Native 10.0 provider:

Const CONNECTION_STRING_NATIVE As String = _
    "DRIVER={SQL Server Native Client 10.0};" & _
    "SERVER=MyServer;" & _
    "DATABASE=MyDatabase;" & _
    "Trusted_Connection=Yes"

Nothing works. Really hoping to get some advice here.

Thank you!

2 answers

Public Function AttachTable(vLocalTable As String, vRemoteTable As String, vServer As String, vDatabase As String, Optional vUserName As String, Optional vPassword As String)
On Error GoTo ErrX
Dim td As TableDef
Dim cnX As String    
    For Each td In CurrentDb.TableDefs
        If td.Name = vLocalTable Then
            CurrentDb.TableDefs.Delete vLocalTable
        End If
    Next      
    If Len(vUserName) = 0 Then
        '//Use trusted authentication if vUserName is not supplied.
        cnX = "ODBC;DRIVER=SQL Server;SERVER=" & vServer & ";DATABASE=" & vDatabase & ";Trusted_Connection=Yes"
    Else
        '//WARNING: This will save the UserName and the Password with the linked table information.
        cnX = "ODBC;DRIVER=SQL Server;SERVER=" & vServer & ";DATABASE=" & vDatabase & ";UID=" & vUserName & ";PWD=" & vPassword
    End If
    Set td = CurrentDb.CreateTableDef(vLocalTable, dbAttachSavePWD, vRemoteTable, cnX)
    CurrentDb.TableDefs.Append td
    AttachTable = True
    Exit Function
ErrX:    
    AttachTable = False
    MsgBox Error$ & " (" & Err.Number & ")",, "Function> AttachTable"
End Function