ODBC call failed error when an access table is opened

Question

I have linked the sql server tables to ms access so that I can use ms access as the front end.I was able to access the tables from access, until I run into an error ODBC call failed when I tried to open one of the tables. There was no problem with the other tables. Actually I have changed a column name in sql server after creating a link. Is this the problem? I am really worried about this as I was about to use access as a front-end for my future purposes.

Answer 1

Yes changing the column name after linking the table is most likely causing your failure. Is it is now trying to pull data from a column that no longer exists. You will need to relink the table. You can programatically link tables in access. We do that in may of our access applications and drive the tables that need to be linked from a local access table.

Public Sub LinkODBCTables()

Dim objRS     As DAO.Recordset
Dim objTblDef As DAO.TableDef
Dim strTableName As String
Dim strAliasName As String
Dim strDSN       As String
Dim lngTblCount  As Long

Set objRS = CurrentDb.OpenRecordset( _
                " select TableName," & _
                       " AliasName," & _
                       " DSN," & _
                       " DatabaseName," & _
                       " Development_DSN," & _
                       " UniqueIndexCol" & _
                  " from tblODBCLinkedTables " & _
                  " order by TableName", dbOpenSnapshot)

While Not objRS.EOF

    ' Check to see if we already have this linked tableDef
    ' We don't care if it is not actually in there

    strTableName = objRS.Fields("TableName")
    If Not IsNull(objRS.Fields("AliasName")) Then
        strAliasName = objRS.Fields("AliasName")
    Else
        strAliasName = strTableName
    End If

    If DEV_MODE Then
        strDSN = objRS.Fields("Development_DSN")
    Else
        strDSN = objRS.Fields("DSN")
    End If

    On Error Resume Next
    CurrentDb.TableDefs.Delete strAliasName
    If Err.Number <> 0 And _
       Err.Number <> 3265 Then  ' item not found in collection
        Dim objError As Error

        MsgBox "Unable to delete table " & strAliasName
        MsgBox Err.Description

        For Each objError In DBEngine.Errors
            MsgBox objError.Description
        Next
    End If
    On Error GoTo 0

    Set objTblDef = CurrentDb.CreateTableDef(strAliasName)

    objTblDef.Connect = g_strSQLServerConn & _
                    "DSN=" & strDSN & _
                    ";DATABASE=" & objRS.Fields("DatabaseName") & _
                    ";UID=" & g_strSQLServerUid & _
                    ";PWD=" & g_strSQLServerPwd

    objTblDef.SourceTableName = strTableName

    On Error Resume Next
    CurrentDb.TableDefs.Append objTblDef
    If Err.Number <> 0 Then
        Dim objErr As DAO.Error

        For Each objErr In DBEngine.Errors
            MsgBox objErr.Description
        Next
    End If
    On Error GoTo 0

    ' Attempt to create a uniqe index of the link for updates
    ' if specified

    If Not IsNull(objRS.Fields("UniqueIndexCol")) Then
        ' Execute DDL to create the new index

        CurrentDb.Execute " Create Unique Index uk_" & strAliasName & _
                          " on " & strAliasName & "(" & objRS.Fields("UniqueIndexCol") & ")"

    End If

    objRS.MoveNext

Wend

objRS.Close

End Sub

We are using a single SQLServer login for our access applications so the g_strSQLServerUID and g_strSQLServerPwd are globals that contain that info. You may need to tweek that for your own needs or integrated security. We are setting up two DSNs one for production and the other for development. The DEV_MODE global controls wich set of DSNs are linked. You can call this code from a startup macro or startup form. It will deleted the old link and create a new link so you always have the most up to date schema.

Answer 2

When you link to a remote table, Access stores metadata about that table. When you later change the table structure, the metadata doesn't get updated to capture the change.

Delete the link. Then recreate the link. That way the metadata will be consistent with the current version of the table.