Note: This is part two of a series of articles on Access Data Projects, to see the complete list and other articles please click here.

Access ADPs can provide you with the flexibility and power only a direct connection to SQL Server can provide, but a major drawback to a developer is the login box that will always show up when you distribute your application, Access will stubbornly hold on to the last account used to connect with the server, unless you store the password with the file, and that may not be ideal.

Use a low level SQL Server account and reset the project connection
I came across a partial solution to the problem using this support article. It describes how you can reset the connection to the user and password of your choosing, but it does not address how to get rid of the login box on startup.

The solution I developed is twofold: Use a low level account on startup, show a custom login form and then reset the project connection.

Start out by creating a new SQL Server login, (or a new Windows login if your ADP is on a Windows network and your using Windows authentication on your SQL Server database), that only has rights to the login stored procedure you've created for your ADP. (If you don't have a login stored procedure you can find one soon in the same section where all are articles are located). This will allow you to save the username and password with your ADP project, but not provide rights to any table, view or stored procedure if the account info is compromised. Whatever you do, never use a high level account and password as your startup account with the ADP.

Now that you've saved the low level login and password with the ADP, the dreaded login form will no longer display on startup, allowing you to instead display your own login form for the users of your application.  Have them enter their login name and password, have them click on a button labeled Login and then proceed to break the connection and restore it with their connections to SQL Server.

Here is the sample code to validate their login, use it on your login form:
    If Not ChangeADPConnection(Me.txtUserID, Me.txtPassword) Then
        Exit Sub
    End If
txtUserID and txtPassword are two fields on the form where users enter their user name and password, substitute them with your own control names if you wish.

The ChangeADPConnection procedure will use the user name and password from the login form to validate the user and establish a connection with the server using their rights and not the rights of the low level login:
Public Function ChangeADPConnection(strUN As String, strPW As String) As Boolean
    Dim strConnect As String
    Dim strServerName As String
    Dim strDBName As String
    
    strServerName = "YourServerIPAddress"    
    strDBName = "YourDatabase"
   
    On Error GoTo EH:
    Application.CurrentProject.CloseConnection
    'The Provider, Data Source, and Initial Catalog arguments are required.
    strConnect = "Provider=SQLOLEDB.1" & _
    ";Data Source=" & strServerName & _
    ";Initial Catalog=" & strDBName
    If strUN <> "" Then
        strConnect = strConnect & ";user id=" & strUN
        If strPW <> "" Then
            strConnect = strConnect & ";password=" & strPW
        End If
    Else  'Try to use integrated security if no username is supplied.
        strConnect = strConnect & ";integrated security=SSPI"
    End If
    Application.CurrentProject.OpenConnection strConnect
    ChangeADPConnection = True
    Exit Function
EH:
    MsgBox Err.Number & ": " & Err.Description, vbCritical, "Connection Error"
    ChangeADPConnection = False
End Function

Conclusion
Access ADPs have a unique advantage over regular MDB's when using SQL Server, I encourage you to use them if you can, along with SQL Server stored procedures, and create great solutions for your clients.