[RESOLVED] [Access 2003] Linked Table Connection

[Torc]

I am developing a system which is processing data from a number of linked
tables, via an ODBC connection.

Currently the system is working fine because I have the connection set up on
my machine.

However I was wondering how I could tell if someone was running the system without the ODBC connection set up on their machine.

Would they just see an empty table, or would there be some other error.

Can anyone advise please.

[Davadvice]

Hello,

they would recive an Error.

there is a way via Code to add the connection to the ODBC data source on that PC.

i had code for this years ago however i cannot locate it, there will be some on here i would imagine. try a search under ODBC in office Dev

thanks
David

[Torc]

Hi Davadvice,

Do you know what errors might be generated?

I am not wanting to set the connection up programatically, but want to send
off an email if someone who is not set up tries something.

Many Thanks.

[Davadvice]

hi,

I don't know of hand sorry.

The best way to find the error would be to remove the ODBC Reference in Your ODBC Data source administrator and this will return the error you would get on another PC

thanks
David

[Torc]

The following is the Function I have built to test the Linked Table Connection.

I had thought that checking the TableDef Properties would indicate if a linked
table's connection was active, but I actually had to open a record set.

Please note that I do not know if the error Number -2147467259 is the
standard error number for this or if another version of access would have a
different code.

Public Function ODBCCheck() As Boolean
' Checks to see if we can access the ODBC Linked Tables.
Dim LocalRS As ADODB.Recordset
    On Error GoTo Err_ODBCCheck
    ' Prepare for failure
    ODBCCheck = False

    Set LocalRS = New ADODB.Recordset
    LocalRS.Open "LinkedTableName", CurrentProject.Connection, adOpenKeyset, adLockPessimistic, adCmdTable
    ' If we get here opening the RecordSet has has worked so close it up.
    LocalRS.Close
    Set LocalRS = Nothing
    ODBCCheck = True ' Connection worked.
    
Exit_ODBCCheck:
    On Error GoTo 0
    Exit Function

Err_ODBCCheck:
    If Err.Number = -2147467259 Then ' The only error we want in this Function
        ' ODBC DConnection to the DSN failed
        ODBCCheck = False
        Resume Exit_ODBCCheck
    Else ' If we have a different error number we use an error handler.
        MsgBox "ModuleName - ODBCCheck" & vbCrLf & _
                "Source      " & Err.Source & vbCrLf & _
                "Number      " & Err.Number & vbCrLf & _
                "Description " & Err.Description
        End ' replace with Resume or whatever is needed.
    End If
    End ' We should never reach here, this is just for safeties sake.
End Function