Article ID: 172347
Article Last Modified on 1/20/2007
APPLIES TO
- Microsoft Access 97 Standard Edition
This article was previously published under Q172347
Moderate: Requires basic macro, coding, and interoperability skills.
SYMPTOMS
When you run the Linked Table Manager, you receive the following error message:
Even if you delete the linked table(s), you continue to receive this error message until you quit and then restart Microsoft Access.
CAUSE
You have at least one table linked to a File DSN. The Linked Table Manager generates an error if it doesn't find the strings "DSN=" and "DATABASE=" in the Connect property of a linked table. If a table is linked to a File DSN, its Connect property doesn't contain the parameter string "DSN=". Furthermore, for some data sources such as ORACLE, the specifier "DATABASE=" may also be missing from the Connect property.
WORKAROUND
Method 1
If you want to manage linked tables with the Linked Table Manager, use System DSNs or User DSNs instead of File DSNs. To manually change your database so that it uses System DSNs or User DSNs instead of File DSNs, delete the linked tables, and then use System DSNs or User DSNs when you recreate them.
You can also use a Visual Basic for Applications procedure to change a linked table's Connect property so that the table is based on a System DSN or User DSN instead of a File DSN.
Method 2
If you want your tables to be linked to File DSNs, you will not be able to manage them with the Linked Table Manager. Because the connection information of a File DSN is stored in a text file, you may want to consider editing that file if you want to manage these connections manually. Also, you can use a Visual Basic for Applications procedure to link a table to a different File DSN.
Sample Procedure to Change a Table's Connect Property
The following sample procedure changes the Connect property of a table. It takes three arguments: the name of the table, the name of the DSN, and a Boolean argument to indicate if the DSN is a File DSN. If you do not supply a third argument, the procedure assumes that the data source is a User DSN or a System DSN.
Function ChangeLink(strLinkName As String, strDSNName As String, _ Optional IsFileDSN As Boolean) Dim db As Database Dim strConn As String On Error GoTo Errorhandler Set db = CurrentDb strConn = db.TableDefs(strLinkName).Connect ' Remove Driver and Server information ' from the connect string. strConn = Right(strConn, Len(strConn) - _ InStr(1, strConn, "APP=") + 1) ' Concatenate new File DSN to ' the connect string. If IsFileDSN Then strConn = "ODBC;FILEDSN=" & strDSNName & ";" & strConn Else strConn = "ODBC;DSN=" & strDSNName & ";" & strConn End If ' Link with the new connect string. With db.TableDefs(strLinkName) .Connect = strConn .RefreshLink End With Exit Function Errorhandler: MsgBox Err & " " & Err.Description Exit Function End Function
STATUS
Microsoft has confirmed this to be a problem in Microsoft Access 97. This problem no longer occurs in Microsoft Access 2000.
MORE INFORMATION
Microsoft Access displays the connection information for a linked table in the Description property. If a table is linked to a File DSN, and you open that table in Design view, you see that the connection string in the Description property box does not refer directly to the DSN. Instead, it contains the server and driver information specified in the File DSN. Even if you change the Connect property in code so that it refers directly to the File DSN, when you view the table's properties after you have refreshed the link, you see that the connection string still does not contain "DSN=" or refer directly to the File DSN.
Steps to Reproduce the Problem
- Start Microsoft Access and open the sample database Northwind.mdb.
- On the File menu, point to Get External Data, and click Link Tables.
- In the Link dialog box, select ODBC Databases from the Files Of Type list.
- In the Select Data Source dialog box, click New. In the Create New Data Source dialog box, select SQL Server. Click Next.
- When asked for the name of the file data source, type FDSNTest. Click Next.
- Click Finish.
- In the SQL Server Login dialog box, type the name of the Server, the Login ID, and the Password.
- Click Options and specify the database. Click OK. The File DSN now appears on the list in the Select Data Source dialog box. Click OK in the SQL Server Login dialog box.
- Select FDSNTest.dsn from the list in the Select Data Source dialog box and click OK.
- Enter any necessary information into the SQL Server Login dialog box, and click OK.
- Select a table from the list in the Link Tables dialog box, and click OK.
- On the Tools menu, point to Add-Ins, and click Linked Table Manager. Note that you receive the error message:
REFERENCES
For more information about the Connect property, search the Help Index for "Connect property."
For more information about File DSNs, please see the following article in the Microsoft Knowledge Base:
165866 How to Use File DSNs and DSN-less Connections
Additional query words: attached reset
Keywords: kbbug kberrmsg KB172347