You are currently offline, waiting for your internet to reconnect

How To Bypass Login Prompt When Opening Linked Table

This article was previously published under Q177594
SUMMARY
In Access, when you first link (attach) an external table using an ODBCdriver, you have the option to store the User ID and password for the tablelocally. If you do not store the ID and password locally, you will beprompted later for such information when you open the table.

This article demonstrates how to bypass the Login prompt when you open anAccess linked table by pre-connecting to the database and providing User IDand password programmatically in Basic.
MORE INFORMATION
The Microsoft Jet database engine caches authentication information foreach DSN. This prevents users from being prompted to login to remotedatabases each time a table is opened. You can take advantage of thisbehavior by pre-connecting to the database directly and programmaticallyproviding user ID and password to prevent the login prompt from appearingwhen opening linked tables that don't have the user ID and password cached.
  1. In Access, create a new database, db1.mdb, and a linked table, dbo_authors, from SQL Server Pubs database.
  2. In Visual Basic, start a new project and choose "Standard EXE." Form1is created by default.
  3. In Access, create a new database and create a new form (Form1).
  4. In Visual Basic 4.0 and later, add a Reference to:
       Microsoft Data Access Object 2.x   (VB4 16-bit)   Microsoft Data Access Object 3.x   (VB4 32-bit; VB5)					
  5. Paste the following code in the General Declarations section of Form1:

    Note You must change Username=<username> and PWD=<strong password> to the correct values before you run this code. Make sure that Username has the appropriate permissions to perform this operation on the database.
          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) & _                      ";Username=<username>;PWD=<strong password>"         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					
NOTES:

  1. You must provide correct login information, User ID and Password, in strConnect to establish the connection.
  2. If you know which DSN the table is linked to, you can hard-code the value of strConnect.
  3. The Microsoft Jet database engine will first try to log you in with the same user ID and password that you log into the Jet database with (default is Admin/no password). If you make the local login match the server login, you will not get any login prompts.
  4. Microsoft SQL Server can integrate its security mechanism with Microsoft NT domain accounts. If the user has a valid account in the domain, you will not get any login prompts.
REFERENCES
See "Managing Connection Resources, Preconnecting" in the Microsoft JetDatabase Engine Programmer's Guide, Chapter 9, Developing Client/ServerApplications
Properties

Article ID: 177594 - Last Review: 02/12/2007 19:09:00 - Revision: 3.4

  • Microsoft Visual Basic 5.0 Professional Edition
  • Microsoft Visual Basic 6.0 Professional Edition
  • Microsoft Visual Basic 5.0 Enterprise Edition
  • Microsoft Visual Basic 6.0 Enterprise Edition
  • Microsoft Visual Basic 4.0 Professional Edition
  • Microsoft Visual Basic 4.0 Professional Edition
  • Microsoft Visual Basic 4.0 16-bit Enterprise Edition
  • Microsoft Visual Basic 4.0 32-Bit Enterprise Edition
  • Microsoft Visual Basic 3.0 Professional Edition
  • Microsoft Access 1.0 Standard Edition
  • Microsoft Access 1.1 Standard Edition
  • Microsoft Access 2.0 Standard Edition
  • Microsoft Access 95 Standard Edition
  • Microsoft Access 97 Standard Edition
  • kbhowto KB177594
Feedback