Found the fix. For anyone who hasn't noticed, I'm using a DSN-less connection without Trusted Connections (not all our users will be on the domain, complicated reasoning). That means we have to use SQL Server Security, and therefore the UID and PWD have to be saved by something. The original code doesn't save it, but if you add 'tdf.Attributes = DB_ATTACHSAVEPWD' you're golden. I'm sure someone can point out some sort of security flaw with this, but I think I'm ok with it for now. Here's the log in code when you have a log in screen with a User ID (quserid) and Password (qpwd) field:
Code:
Private Sub cmdLogIn_Click()
On Error GoTo Err_cmdLogIn_Click
'-----------------------------------------------------------------------------------------------------------------------------
' This code is used to validate users with SQL Server Security connect to SQL server.
' If the wrong user name or password is provided access is denied.
' Created by: Daniel VanBeek with code from Danny Lesandrini, www.databasejournal.com
' Date Created: 08 Jul 2012
' Date Modified: 08 Jul 2012
'-----------------------------------------------------------------------------------------------------------------------------
Dim strTable As String
Dim strSuccess As String
Dim tdf As TableDef
Dim strMsg As String
Dim strUID As String
Dim strPWD As String
Dim strConnect As String
Dim stDocName As String
Dim stLinkCriteria As String
' Check for existence of User Name and password.
' If missing, inform user and exit.
If IsNull(Me.quserid) Then
strMsg = "Enter user login. (Example: bsmith)" = ""
MsgBox strMsg, vbInformation, "Missing Data"
Me.quserid.SetFocus
ElseIf IsNull(Me.qpwd) Then
strMsg = "Enter your password."
MsgBox strMsg, vbInformation, "Missing Data"
Me.qpwd.SetFocus
Else
strUID = Me.quserid
' Password may be NULL, so provide for that possibility
strPWD = Nz(Me.qpwd, "")
' Prepare connection string
strConnect = "DRIVER={SQL Server}" _
& ";SERVER=###.###.###.###" _
& ";DATABASE=myDB" _
& ";Uid=" & strUID _
& ";Pwd=" & strPWD & ";"
'Debug.Print strConnect
End If
' Refresh Access Linked Tables
For Each tdf In CurrentDb.TableDefs
' Only attempt to refresh link on tables that already
' have a connect string (linked tables only)
If Len(tdf.Connect) > 0 Then
strTable = tdf.Name
' Set the tables connection string
tdf.Connect = strConnect
tdf.Attributes = DB_ATTACHSAVEPWD
' Give feedback to user
strMsg = "Refreshing link to ... " & strTable
Me.lblmsg.Caption = strMsg
Me.Repaint
tdf.RefreshLink
End If
Next
' Give feedback to user
'strSuccess = IIf(Err.Number = 0, "Successful", "NOT successful.")
'strMsg = "Finished. Connect was " & strSuccess
'Me.lblmsg.Caption = strMsg
stDocName = "frmMainMenu"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, "LogInForm"
Exit_cmdLogIn_Click:
Exit Sub
Err_cmdLogIn_Click:
MsgBox Err.Description
Resume Exit_cmdLogIn_Click
End Sub
As you can see, this will only log you in and take you to the main menu if the SQL Server connection string works. If not, you'll get a Connection failed error from the SQL Server Login box. It then gives you another chance to log in through the SQL Server Login box.
Can anyone help me with the error handling so that it catches the SQL Server Login box and routes you back to my login form with an error?