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

Article translations Article translations
Article ID: 245587 - View products that this article applies to.
This article was previously published under Q245587
Expand all | Collapse all

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,
177594 How To 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.
  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.

Properties

Article ID: 245587 - Last Review: July 13, 2004 - Revision: 3.3
APPLIES TO
  • Microsoft ActiveX Data Objects 1.5
  • Microsoft ActiveX Data Objects 2.0
  • Microsoft ActiveX Data Objects 2.1 Service Pack 1
  • Microsoft ActiveX Data Objects 2.5
  • Microsoft ActiveX Data Objects 2.6
  • Microsoft Visual Basic 5.0 Learning Edition
  • Microsoft Visual Basic 6.0 Learning Edition
  • Microsoft Visual Basic 5.0 Professional Edition
  • Microsoft Visual Basic 6.0 Professional Edition
Keywords: 
kbclient kbdatabase kbhowto kbjet KB245587

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com