ACC: Sample Function to Provide Password for Attached Tables

Last reviewed: August 29, 1997
Article ID: Q109829
The information in this article applies to:
  • Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

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 the ID and password when you open the table (either manually or programmatically).

This article describes a sample function that you can use in Microsoft Access 97, 7.0, 2.0 and 1.x to eliminate the login prompt when you open the table.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access versions 1.x and 2.0. For more information about Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x or the "Building Applications" manual in Microsoft Access version 2.0.

MORE INFORMATION

The following steps demonstrate how to eliminate the login prompt when you open a linked table:

  1. You must first provide your login information. To do this, use one of the four standard ODBC dialog boxes, or create your own custom login box.

  2. Use the sample function below (or one similar to it) along with a valid <UserId> and <Password> to establish the connection.

    NOTE: In the following sample code, an underscore (_) is used as a line- continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic.

          Function Establish_Connection ()
    
             Dim db As Database, dbcurr As Database
             Dim ds As Recordset '(Dim ds as Dynaset in version 1.x)
             Set dbcurr = CurrentDB()
             Set db = OpenDatabase("", False, False,"ODBC;DSN=_
              <data source name>;UID=<UserID>;PWD=<Password>;DATABASE=_
              <database name>;")
             Set ds = db.OpenRecordset("<any table existing in the _
              database defined by DATABASE above>")
             '(use db.OpenDynaset in Microsoft Access 1.x)
          End Function
    
       Substitute your own server name, password, database name, and table name
       in the function above.
    
    
When you run this function, Microsoft Access establishes a connection with the server specified in the function. The Connection Manager then stores the connection information, enabling you to open other linked tables on that server without being prompted for the login information each time. The Connection Manager will retain the login information until you quit Microsoft Access.


Additional query words: attaching
Keywords : kbprg kbusage PgmHowTo OdbcOthr
Version : 1.0 1.1 2.0 7.0 97
Platform : WINDOWS
Hardware : x86
Issue type : kbinfo


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.

Last reviewed: August 29, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.