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=Micro soft Office XP;WSID=ADMIN1;DATABASE=Aw esomeDB;TA BLE=dbo.Fi naltable". 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!
ASKER
Hi Scott,
How would I delete the link using Access 2010?
How would I delete the link using Access 2010?
Mark the table in the navigation pane and press Del ...
/gustav
/gustav
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
/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.
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
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
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
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
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
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
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
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;Trust ed_Connect ion=Yes;AP P=Microsof t Office 2013;DATABASE=nfgachin;;TA BLE=dbo.Re directItem s
An example only.
If this information is not enough to guide you, you will have to consult your database administrator.
/gustav
An example:
ODBC;Description=QR;DRIVER
An example only.
If this information is not enough to guide you, you will have to consult your database administrator.
/gustav
ASKER
Gotcha, what I see is "ODBC;DSN=Awesomedb;UID=sa ;APP=Micro soft Office XP;WSID=ADMIN1;DATABASE=Aw esomeDB;TA BLE=dbo.Fi naltable" and I get the same if I do the command "? CurrentDb.TableDefs("dbo_F inaltable" ).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_F inaltable" ).Name => dbo_Finaltable
CurrentDb.TableDefs("dbo_F inaltable" ).SourceTa bleName => dbo.Finaltable
If you have SQL Server security enabled on the server, you can try using this function to attach the table:
CurrentDb.TableDefs("dbo_F
CurrentDb.TableDefs("dbo_F
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
/gustav
ASKER
This is what I got:
? CurrentDb.TableDefs("dbo_F inaltable" ).Name => dbo_Finaltable
True
? CurrentDb.TableDefs("dbo_F inaltable" ).SourceTa bleName
dbo.Finaltable
? CurrentDb.TableDefs("dbo_F inaltable" ).SourceTa bleName => dbo_Finaltable
True
I don't believe it's an SQL Server, just Microsoft Access Driver.
? CurrentDb.TableDefs("dbo_F
True
? CurrentDb.TableDefs("dbo_F
dbo.Finaltable
? CurrentDb.TableDefs("dbo_F
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
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
ASKER
I just asked and it's definitely not an SQL Server.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
/gustav
To do that, delete the linked table, then use the External Data - ODBC Database ribbon command to recreate it.