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

RESOLUTION

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.