DOCUMENT:Q177594  08-DEC-1997  [vbwin]
TITLE   :HOWTO: Bypass Login Prompt When Opening Linked Table
PRODUCT :Microsoft Visual Basic for Windows
PROD/VER:WINDOWS:1.0,1.1,2.0,3.0,4.0,5.0,7.0,97
OPER/SYS:WINDOWS
KEYWORDS:VB4ALL VB4WIN vb5all vb5howto

======================================================================
---------------------------------------------------------------------
The information in this article applies to:

 - Microsoft Visual Basic Professional and Enterprise Editions for
   Windows, version 5.0
 - Microsoft Visual Basic Professional and Enterprise Editions, 16-bit and
   32-bit, for Windows, version 4.0
 - Microsoft Visual Basic Professional Edition for Windows, version 3.0
 - Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97
---------------------------------------------------------------------

SUMMARY
=======

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.

MORE INFORMATION
================

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.

1. In Access, create a new database, db1.mdb, and a linked table,
   dbo_authors, from SQL Server Pubs database.

2a. In Visual Basic, start a new project and choose "Standard EXE." Form1
    is created by default.

2b. In Access, create a new database and create a new form (Form1).

3. 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)

4. Paste the following code in the General Declarations section of Form1:

      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
         Set DBEngine = 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 Jet
Database Engine Programmer's Guide, Chapter 9, Developing Client/Server
Applications

(c) Microsoft Corporation 1997, All Rights Reserved.
Contributions by Adrian Chiang, Microsoft Corporation
======================================================================
Keywords          : VB4ALL VB4WIN vb5all vb5howto
Version           : WINDOWS:1.0,1.1,2.0,3.0,4.0,5.0,7.0,97
Platform          : WINDOWS
Issue type        : kbhowto

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS
PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND.  MICROSOFT DISCLAIMS
ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.  IN NO
EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR
ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL,
CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF
MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGES.  SOME STATES DO NOT ALLOW THE EXCLUSION
OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES
SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 1997.