ACC2000: Access Project Prompts You to Log On Even Though "Blank Password" Check Box Is Selected (202615)


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q202615
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access project (.adp).

SYMPTOMS

When you open an Access project, you are prompted to enter a user name and password, even though a default user name has been set and the Blank Password check box has been selected. When you return to the Data Link Properties dialog box, you find that the Blank password check box is no longer selected.

RESOLUTION

There are two methods that you can use to avoid the Logon dialog box. You can use the first method with the user interface (UI). The second method is more involved; it requires Visual Basic for Applications (VBA) programming.

Using the UI

  1. Open your Access project.
  2. On the File menu, click Connection.
  3. Click to select the Blank password and the Allow saving password check boxes.
  4. Click OK to save the settings.
  5. Close and reopen the Access project. Note that you are not prompted by the Logon dialog box.

Using VBA

  1. Open your Access project.
  2. Create a new module, and then type or paste the following code: 
    Option Compare Database
Option Explicit

Sub subDisconnect()
    Application.CurrentProject.CloseConnection
    Application.CurrentProject.OpenConnection
End Sub

Function fncConnect()
    Dim strServer As String     'The name of the MSDE or SQL Server.
    Dim strUser As String       'The user name for logging in.
    Dim strPwd As String        'The user password for logging in.
    Dim strDB As String         'The name of the source database.
    Dim strConnection As String 'Connection settings.
    Dim strPrompt As String     'Message to display if error occurs.
    Dim intBtns As Integer      'Buttons/icon to display on error message.
    Dim strTitle As String      'Title bar of error message.
    Dim intRetVal As Integer    'Button clicked on error message.
    
    On Error GoTo ConnectionErr
    
    strServer = "<ServerName>"  'Enter the actual name of your server.
    strUser = "sa"
    strPwd = ""
    strDB = "<DatabaseName>"    'Enter the actual database name.
    
    strConnection = "Provider=SQLOLEDB.1" & _
                    ";Persist Security Info=True" & _
                    ";Data Source=" & strServer & _
                    ";User ID=" & strUser & _
                    ";Password=" & strPwd & _
                    ";Initial Catalog=" & strDB

    Application.CurrentProject.OpenConnection strConnection

ConnectExit:
    Exit Function

ConnectionErr:
    strPrompt = Err.Description
    intBtns = vbApplicationModal + vbExclamation + vbOKOnly
    strTitle = "ERROR #" & Err.Number
    intRetVal = MsgBox(strPrompt, intBtns, strTitle)
    Resume ConnectExit
End Function
    In the code, be sure to replace <ServerName> with the actual name of your Microsoft Data Engine (MSDE) or SQL Server, and replace <DatabaseName> with the actual name of your MSDE or SQL Server database.
  3. Type Call subDisconnect() in the Immediate Window, and then press ENTER to clear the connection settings assigned to the Data Link Properties dialog box.
  4. Close and save the module, and then create a new macro.
  5. Under Action, click RunCode, and in the Function Name box, type fncConnect().
  6. Close and save the macro as Autoexec.
  7. Close and reopen the Access project. Note that you do not receive the Logon dialog box.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

Steps to Reproduce the Behavior

  1. Open the sample Access project NorthwindCS.adp.
  2. On the File menu, click Connection.
  3. In the Data Link Properties dialog box, click Use a specific user name and password, type sa in the User name box, leave the Password box blank, and click to clear the Blank password and Allow saving password check boxes.
  4. Click OK, and then close NorthwindCS.adp.
  5. Reopen NorthwindCS.adp and note that you are prompted for a password as expected (you may have to click Tables, Views, Database Diagrams, or Stored Procedures to get the Logon dialog box to actually appear).
  6. Click OK in the Logon dialog box, and then on the File menu, click Connection.
  7. Click to select the Blank password check box, and then click OK.
  8. Close NorthwindCS.adp, and then reopen the database. Note that the Logon dialog box still appears. Click OK to get back to the database.
  9. On the File menu, click Connection. Note that the Blank password check box is no longer selected.
Modification Type:MinorLast Reviewed:1/26/2005
Keywords:kbbug KbClientServer kbpending KB202615
©2004 Microsoft Corporation. All rights reserved.