HOWTO: Bypass Login Prompt When Opening Linked TableID: Q177594
|
In 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.
This article demonstrates how to bypass the Login prompt when you open an
Access linked table by pre-connecting to the database and providing User ID
and password programmatically in Basic.
The Microsoft Jet database engine caches authentication information for each DSN. This prevents users from being prompted to login to remote databases each time a table is opened. You can take advantage of this behavior by pre-connecting to the database directly and programmatically providing user ID and password to prevent the login prompt from appearing when opening linked tables that don't have the user ID and password cached.
Microsoft Data Access Object 2.x (VB4 16-bit)
Microsoft Data Access Object 3.x (VB4 32-bit; VB5)
Sub Command1_Click()
Dim db1 As Database
Dim db2 As Database
Dim rs As Recordset
Dim strConnect As String
'*** You have to modify the path to where db1.mdb is located
Set db1 = OpenDatabase("C:\MyTest\db1.mdb")
strConnect = UCase(db1.TableDefs("dbo_authors").Connect) & _
";UID=sa;PWD=;"
Set db2 = OpenDatabase("", False, False, strConnect)
db2.Close
Set db2 = Nothing
Set rs = db1.OpenRecordset("dbo_authors")
Debug.Print rs(0)
Debug.Print "Recordset Opened Successfully"
rs.Close
db1.Close
Set rs = Nothing
Set db1 = Nothing
End Sub
See "Managing Connection Resources, Preconnecting" in the Microsoft Jet Database Engine Programmer's Guide, Chapter 9, Developing Client/Server Applications
© Microsoft Corporation 1999, All Rights Reserved.
Contributions by Adrian Chiang, Microsoft Corporation
Additional query words:
Keywords : kbAccess kbDAO kbVBp kbVBp300 kbVBp400 kbVBp500 kbVBp600 kbGrpVB
Version : WINDOWS:1.0,1.1,2.0,3.0,4.0,5.0,6.0,7.0,97
Platform : WINDOWS
Issue type : kbhowto
Last Reviewed: June 2, 1999