Microsoft KB Archive/172347

From BetaArchive Wiki
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.

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:

Invalid procedure call or argument.

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

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

165866 How to Use File DSNs and DSN-less Connections



Additional query words: attached reset

Keywords: kbbug kberrmsg KB172347