Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    107

    Question Unanswered: Testing ODBC Links

    Hi there,

    I have an Access 2000 database with four tables linked using ODBC to a RealSQL database backend. Upon opening my database I would like to test that these ODBC links are working and notify the user if they are not. Does anyone have any ideas how to do this?

    Thanks

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    The only way that I've found is to perform a simple query to test if the connection is established ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    107
    Thanks, but I'd thought of that one. Don't want to have a query for each ODBC link as it will affect performance. Anyone got any other ideas?

  4. #4
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    107

    Solution

    Managed to devise a rather crude solution if anyone's interested:

    Public Function TestODBCLink(strTableName As String, strDSNName As String)

    Dim cat As New ADOX.Catalog
    Dim tbl As New ADOX.Table

    cat.ActiveConnection = CurrentProject.connection
    tbl.Name = strTableName

    Set tbl.ParentCatalog = cat
    tbl.Properties("Jet OLEDB:Create Link") = True
    tbl.Properties("Jet OLEDB:Link Provider String") = "ODBC;DSN=" & strDSNName
    tbl.Properties("Jet OLEDB:Remote Table Name") = strTableName

    cat.Tables.Append tbl
    cat.Tables.Delete strTableName

    End Function

    Basically I try and connect to the table and if the ODBC link is down it will generate an error otherwise it adds the link to the database and I just delete it afterwards cos' I really don't need it.

    Does anyone know of a better way?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •