Solved

ODBC connection to failed

Posted on 2015-02-20
22
118 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 10
  • 2
22 Comments
 
LVL 85
ID: 40622339
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
ID: 40622365
Hi Scott,

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

Expert Comment

by:Gustav Brock
ID: 40622775
Mark the table in the navigation pane and press Del ...

/gustav
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

Author Comment

by:OECMax
ID: 40622923
Hey Gustav, doing that deletes the entire table of data.
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 40622930
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 85
ID: 40622947
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
ID: 40622951
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 50

Expert Comment

by:Gustav Brock
ID: 40622981
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
ID: 40623260
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 50

Expert Comment

by:Gustav Brock
ID: 40623451
> 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
ID: 40623463
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
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 40623470
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
ID: 40623475
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 50

Expert Comment

by:Gustav Brock
ID: 40623497
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
ID: 40623555
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 50

Expert Comment

by:Gustav Brock
ID: 40623975
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
ID: 40624359
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 50

Expert Comment

by:Gustav Brock
ID: 40624389
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
ID: 40624672
I just asked and it's definitely not an SQL Server.
0
 
LVL 50

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 40625158
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
ID: 40643091
Thanks Gustav! Found an image of an old backup and was able to retrieve the database and connection settings.
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 40643288
Great. Thanks for the feedback.

/gustav
0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
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…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

729 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