HOWTO: Supply User Authentication when Opening Jet Linked Tables to ODBC Datasources

ID: Q245587

The information in this article applies to:

ActiveX Data Objects (ADO), versions 1.5, 2.0, 2.1 SP1, 2.5
Microsoft Visual Basic Learning and Enterprise Editions for Windows, versions 5.0, 6.0
Microsoft Access 2000

SUMMARY

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:

"-2147467259(80004005) ODBC--Connection to 'Server' Failed"

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.

MORE INFORMATION

The Microsoft Knowledge Base article,

Q177594 HOWTO: Bypass Login Prompt When Opening Linked Table

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.

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.

In Microsoft Visual Basic version 5.0 or 6.0, from the Project menu, choose References and add a reference to the following type library:

Microsoft ActiveX Data Objects 2.1 Library

Add two command buttons (cmdOpenLink and cmdPreAuthenticate).

Add the following code to the form's module:


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=sa;pwd=;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

Run the application and open the form. Click cmdOpenLink. You will receive a run-time error.

End the application and restart. Click cmdPreAuthenticate, and then click cmdOpenLink. The Debug/Immediate window will display the record count.

Additional query words:

Keywords : kbADO kbClient kbDatabase kbJET kbOLEDB kbGrpVBDB kbGrpMDAC kbDSupport
Version : WINDOWS:1.5,2.0,2.1 SP1,2.5,2000,5.0,6.0
Platform : WINDOWS
Issue type : kbhowto