Solved

ODBC connection to failed

Posted on 2015-02-20
22
105 Views
Last Modified: 2015-03-03
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!
0
Comment
Question by:OECMax
  • 10
  • 10
  • 2
22 Comments
 
LVL 84
Comment Utility
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.
0
 

Author Comment

by:OECMax
Comment Utility
Hi Scott,

How would I delete the link using Access 2010?
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
Mark the table in the navigation pane and press Del ...

/gustav
0
 

Author Comment

by:OECMax
Comment Utility
Hey Gustav, doing that deletes the entire table of data.
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
No. It's the navigation pane in Access. Pressing Del deletes the link, not the table, as will the message tell you.

/gustav
0
 
LVL 84
Comment Utility
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.
0
 

Author Comment

by:OECMax
Comment Utility
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?
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
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
0
 

Author Comment

by:OECMax
Comment Utility
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.
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
> 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
0
 

Author Comment

by:OECMax
Comment Utility
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
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
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
0
 

Author Comment

by:OECMax
Comment Utility
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?
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
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
0
 

Author Comment

by:OECMax
Comment Utility
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!
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
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
0
 

Author Comment

by:OECMax
Comment Utility
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.
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
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
0
 

Author Comment

by:OECMax
Comment Utility
I just asked and it's definitely not an SQL Server.
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
Comment Utility
Then, I guess, an SQL Server ran on the old 2003 server. So the first task is to locate the data from this. That would be from an old backup.
When located, you can restore the data to a new install of SQL Server. Your new server can host this. You can use the Express version which is free.

/gustav
0
 

Author Closing Comment

by:OECMax
Comment Utility
Thanks Gustav! Found an image of an old backup and was able to retrieve the database and connection settings.
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
Great. Thanks for the feedback.

/gustav
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now