How To Supply User Authentication when Opening Jet Linked Tables to ODBC Datasources (245587)

MORE INFORMATION

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.