Link to home
Start Free TrialLog in
Avatar of OECMax
OECMax

asked on

ODBC connection to failed

We have recently moved our files to a newer fileserver with Server 2008 since our last 2003 Server crashed.  One of the files is a simple Access database with a Linked Table, however when I try to open the table called "dbo_Finaltable" I get the error "ODBC--connection to 'Awesomedb' failed." then "Microsoft Access can't open the table in Datasheet view."  If I hover the mouse over the table I see "ODBC;DSN=Awesomedb;UID=sa;APP=Microsoft Office XP;WSID=ADMIN1;DATABASE=AwesomeDB;TABLE=dbo.Finaltable".  I tried to add a user DSN and a System DSN in Data Sources (ODBC) in 32 and 64-bit versions but the same error comes up.  What do I need to do to view this database?  Thanks in advance!
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

It's often easiest to delete the link and recreate it. When you do so, Access will walk you through the steps needed.

To do that, delete the linked table, then use the External Data - ODBC Database ribbon command to recreate it.
Avatar of OECMax
OECMax

ASKER

Hi Scott,

How would I delete the link using Access 2010?
Mark the table in the navigation pane and press Del ...

/gustav
Avatar of OECMax

ASKER

Hey Gustav, doing that deletes the entire table of data.
No. It's the navigation pane in Access. Pressing Del deletes the link, not the table, as will the message tell you.

/gustav
A "Linked Table" is just a file definition that tells Access how to locate and show you the table. Deleting that "Linked Table" only deletes the link, not the database table itself.
Avatar of OECMax

ASKER

Is the data still located in the same Access file   after the linked table is deleted or is it linked to a different file I need to find on the Server?
If it is a linked table from an SQL Server, the data has been, still is and - if the link is deleted - will remain in a table on the server.
That's why the icon for a linked table contains top-left a small arrow.

A better wording for link and delete - which often is used - is attach and detach.

/gustav
Avatar of OECMax

ASKER

Removing the linked table gets rid of any data in the mdb file.  Could it be that there is another file on the server this database is linked to and it has been moved or deleted?  I try to convert to a local table and i get the same error "ODBC--connection to 'Awesomedb' failed.
> Removing the linked table gets rid of any data in the mdb file.

I'm not sure what you mean. Deleting the link will not remove any data, only the link to these.

> I try to convert to a local table ..

If you "convert" to a local table, I believe it will import the data from the linked table into a local table, delete the link, and then rename the local table to the name the linked table had.

However, if you cannot connect to the linked table, this process will fail already in the first step with the error you see.

/gustav
Avatar of OECMax

ASKER

When I delete the Linked Table I don't see any tables or any way to access any data.  See attached photos of before and after the deletion.
AccessDB.jpg
AccessDB-deleted.jpg
That is correct. If you have one single linked table and no local tables, then no table objects are left if you delete this single table link.
The data, however, are still persisted in a table on the server.

You will have to follow the advice from Scott's very first comment.

/gustav
Avatar of OECMax

ASKER

I think the problem then is the location and even the name of the local table this linked table is associated with.  Is there a way to find out what the name of the local table it's looking for and possibly the location of the table it's looking for?
Yes. Right-click the table, select design, then open the property sheet. The fourth is Description or similar.
An example:

ODBC;Description=QR;DRIVER=SQL Server Native Client 11.0;SERVER=CELMONTY;Trusted_Connection=Yes;APP=Microsoft Office 2013;DATABASE=nfgachin;;TABLE=dbo.RedirectItems

An example only.
If this information is not enough to guide you, you will have to consult your database administrator.

/gustav
Avatar of OECMax

ASKER

Gotcha, what I see is "ODBC;DSN=Awesomedb;UID=sa;APP=Microsoft Office XP;WSID=ADMIN1;DATABASE=AwesomeDB;TABLE=dbo.Finaltable" and I get the same if I do the command "? CurrentDb.TableDefs("dbo_Finaltable").Connect".  Is there a command that tells me what local table it is looking for?  There's no database administrator anymore so we're on our own here.  Appreciate your help.  Thanks!
That table name is normally called the remote table, and that is dbo.Finaltable. It can be read from the property SourceTableName:

CurrentDb.TableDefs("dbo_Finaltable").Name => dbo_Finaltable
CurrentDb.TableDefs("dbo_Finaltable").SourceTableName => dbo.Finaltable

If you have SQL Server security enabled on the server, you can try using this function to attach the table:
Public Function AttachSqlServer( _
    Optional ByVal Hostname As String, _
    Optional ByVal Database As String, _
    Optional ByVal Username As String, _
    Optional ByVal Password As String) _
    As Boolean

' Attach all tables linked via ODBC to SQL Server.
' 2014-08-09. Cactus Data ApS, CPH.

    Const cstrDbType    As String = "ODBC"
    Const cstrAcPrefix  As String = "dbo_"
    Const cstrConnect   As String = _
        "ODBC;" & _
        "DRIVER=SQL Server;" & _
        "Description=Your Application;" & _
        "APP=Microsoft® Access;" & _
        "SERVER={0};" & _
        "DATABASE={1};" & _
        "UID={2};" & _
        "PWD={3};"

    Dim dbs             As DAO.Database
    Dim tdf             As DAO.TableDef
    Dim strConnect      As String
    Dim strName         As String
    
    On Error GoTo Err_AttachSqlServer
    
    Set dbs = CurrentDb
    strConnect = cstrConnect
    strConnect = Replace(strConnect, "{0}", Hostname)
    strConnect = Replace(strConnect, "{1}", Database)
    strConnect = Replace(strConnect, "{2}", Username)
    strConnect = Replace(strConnect, "{3}", Password)
    
    For Each tdf In dbs.TableDefs
        strName = tdf.Name
        If Asc(strName) <> Asc("~") Then
            If InStr(tdf.Connect, cstrDbType) = 1 Then
                If Left(strName, Len(cstrAcPrefix)) = cstrAcPrefix Then
                    tdf.Name = Mid(strName, Len(cstrAcPrefix) + 1)
                End If
                tdf.Connect = strConnect
                tdf.RefreshLink
                Debug.Print tdf.Name, tdf.SourceTableName, tdf.Connect
            End If
        End If
    Next
    
    AttachSqlServer = True
    
Exit_AttachSqlServer:
    Set tdf = Nothing
    Set dbs = Nothing
    Exit Function
    
Err_AttachSqlServer:
    Call ErrorMox
    Resume Exit_AttachSqlServer
    
End Function

Open in new window

/gustav
Avatar of OECMax

ASKER

This is what I got:

? CurrentDb.TableDefs("dbo_Finaltable").Name => dbo_Finaltable
True
? CurrentDb.TableDefs("dbo_Finaltable").SourceTableName
dbo.Finaltable
? CurrentDb.TableDefs("dbo_Finaltable").SourceTableName => dbo_Finaltable
True

I don't believe it's an SQL Server, just Microsoft Access Driver.
Well, if you don't even know if you have an SQL Server running, it will be difficult.

But you once had; dbo.Finaltable is with 99.9% certainty a table from such server.

It sounds like you need to team up with a local network guy who can verify and correct or rebuild your SQL Server installation.

/gustav
Avatar of OECMax

ASKER

I just asked and it's definitely not an SQL Server.
ASKER CERTIFIED SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of OECMax

ASKER

Thanks Gustav! Found an image of an old backup and was able to retrieve the database and connection settings.
Great. Thanks for the feedback.

/gustav