In Microsoft Access, when you first link (attach) an external table using an ODBC driver, you have the option to store the User ID and password for the table locally. If you do not store the ID and password locally, you will be prompted later for such information when you open the table.

If you open the table through ActiveX Data Objects (ADO), you receive this run-time error: The error occurs because the connect string is not complete.

This article demonstrates how to open a Microsoft Access linked table in ADO by pre-connecting to the database and providing User ID and password programmatically in Visual Basic.

The Microsoft Knowledge Base article, describes how to perform this process with Data Access Object (DAO). However, this does not translate directly to ADO. In DAO, all databases are opened using the same instance of Microsoft Jet. Authentication cached on one database will be available to all. In ADO, each connection creates a new instance of Microsoft Jet. Authentication cached on one connection is not available to other connections.

There are three workarounds:

•	Open the ODBC datasource directly in ADO bypassing the Jet engine completely. The advantage for this method is performance. The drawback is that this technique does not allow for heterogeneous joins between ODBC and Jet tables.
•	When creating the linked table, choose to have the authentication information stored as part of the link. The advantage is that you can perform heterogeneous joins with local Jet data. The drawback is that every user gets the same authentication. Also, anyone who can open the database will be able to open the linked table, bypassing program safeguards, although you can work around this by using a database password or user-security.
•	Pre-authenticate the ODBC login using a similar technique to that described in the article mentioned in the first paragraph of this section. The advantage is that you avoid storing authentication in the Jet database, each user can have a separate authentication, and you can perform joins with local Jet data.

In order to pre-authenticate ODBC access in ADO, you must use the same connection on which you will open the linked table. Microsoft Jet provides syntax for referencing an external database and will cache the authentication information.

The sample application uses the Microsoft SQL Server "pubs" database to illustrate the technique. For better performance, you can create a dummy table containing a single field and no records to use in the pre-authentication statement.

1.	In Microsoft Access 2000, open NorthWind.mdb and link a table to an ODBC datasource. In this example, the linked table uses the data source name (DSN) of MyServer, the Pubs database, and the Authors table. Do not save the Password.
2.	In Microsoft Visual Basic version 5.0 or 6.0, from the Project menu, choose References and add a reference to the Microsoft ActiveX Data Objects (version 2.1 or later) Library.
3.	Add two command buttons (cmdOpenLink and cmdPreAuthenticate).
4.	Add the following code to the form's module: Note You must change User ID=<User ID> and password=<Strong Password> to the correct values before you run this code. Make sure that User ID has the appropriate permissions to perform this operation on the database. Option Explicit Dim cn As ADODB.connection Private Sub cmdOpenLink_Click() ' ' Opens a linked table ' Dim rs As ADODB.Recordset Set rs = New ADODB.Recordset rs.open "dbo_authors", cn, adopenkeyset, adlockoptimistic, adcmdtable rs.movelast Debug.Print "There are"; rs.recordcount; "records." rs.Close End Sub Private Sub cmdPreAuthenticate_Click() ' ' Opens a dummy recordset on a table using the same connection properties ' as the linked table. This could be a dummy UPDATE statement to avoid the ' overhead of recordset creation. ' Dim rs As ADODB.Recordset Set rs = cn.execute("SELECT * FROM [ODBC;DSN=MyServer;uid=<User ID>;pwd=<Strong Password>;database=pubs].Authors WHERE FALSE") rs.close End Sub Private Sub Form_Load() Set cn = New ADODB.connection cn.open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb" End Sub
5.	Run the application and open the form. Click cmdOpenLink. You will receive a run-time error.
6.	End the application and restart. Click cmdPreAuthenticate, and then click cmdOpenLink. The Debug/Immediate window will display the record count.