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.
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
- Open your Access project.
- On the File menu, click Connection.
- Click to select the Blank password and the Allow saving password check boxes.
- Click OK to save the settings.
- Close and reopen the Access project. Note that you are not prompted by the Logon dialog box.
- Open your Access project.
- Create a new module, and then type or paste the following code:
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.
Option Compare Database
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
strPrompt = Err.Description
intBtns = vbApplicationModal + vbExclamation + vbOKOnly
strTitle = "ERROR #" & Err.Number
intRetVal = MsgBox(strPrompt, intBtns, strTitle)
- Type Call subDisconnect() in the Immediate Window, and then press ENTER to clear the connection settings assigned to the Data Link Properties dialog box.
- Close and save the module, and then create a new macro.
- Under Action, click RunCode, and in the Function Name box, type fncConnect().
- Close and save the macro as Autoexec.
- Close and reopen the Access project. Note that you do not receive the Logon dialog box.
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.
Article ID: 202615 - Last Review: October 11, 2013 - Revision: 2.1
- Microsoft Access 2000 Standard Edition
|kbnosurvey kbarchive kbbug kbclientserver kbpending KB202615|