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
If you open the table through ActiveX Data Objects (ADO), you
receive this run-time error:
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.
The Microsoft Knowledge Base article,
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
- 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
- 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
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 Microsoft ActiveX Data Objects
(version 2.1 or later) Library.
- Add two command buttons (cmdOpenLink and
- 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
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
Debug.Print "There are"; rs.recordcount; "records."
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")
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"
- 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.
Article ID: 245587 - Last Review: July 13, 2004 - Revision: 3.3
- 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
|kbclient kbdatabase kbhowto kbjet KB245587|