ACC97: "Invalid procedure call" Error with Linked Table Manager

• Microsoft Access 97

SYMPTOMS

Moderate: Requires basic macro, coding, and interoperability skills.

When you run the Linked Table Manager, you receive the following error message:

   Invalid procedure call or argument.

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 datasource 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. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

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

1. Start Microsoft Access and open the sample database Northwind.mdb.

2. On the File menu, point to Get External Data, and click Link Tables.

3. In the Link dialog box, select ODBC Databases from the Files Of Type list.

4. In the Select Data Source dialog box, click New. In the Create New Data Source dialog box, select SQL Server. Click Next.

5. When asked for the name of the file data source, type "FDSNTest" (without the quotation marks). Click Next.

6. Click Finish.

7. In the SQL Server Login dialog box, type the name of the Server, the Login ID, and the Password.

8. 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.

9. Select FDSNTest.dsn from the list in the Select Data Source dialog box and click OK.

10. Enter any necessary information into the SQL Server Login dialog box, and click OK.

11. Select a table from the list in the Link Tables dialog box, and click OK.

12. On the Tools menu, point to Add-Ins, and click Linked Table Manager. Note that you receive the error message:

    Invalid procedure call or argument.

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:

   ARTICLE-ID: Q165866
   TITLE     : How to Use File DSNs and DSN-less Connections