MS Access - SQL Server Login Prompt won't go away

Question

So I have this application and I moved all local tables to SQL Server using upsizing, now they are currently linked tables. I'm able to access tables and forms related to tables can be accessed with no problems. But when I programmatically fetch a record, or perform a sql operation in VBA script, a SQL Server Login prompt pops up asking me to enter in the SQL Authentication login to access the database.

I followed this link here:

https://support.microsoft.com/en-us/kb/177594

Where this is my end code:

Dim db1 As Database
Dim db2 As Database
Dim rs As Recordset
Dim strConnect As String
Set db1 = OpenDatabase("C:\Workspace\ms1.mdb")


strConnect = UCase(db1.TableDefs("dbo_TableA").Connect) & ";UID=User1;PWD=Password1"

Set db2 = OpenDatabase("", False, False, strConnect)
db2.Close
Set db2 = Nothing
Set rs = db1.OpenRecordset("dbo_TableA")
rs.Close
db1.Close
Set rs = Nothing
Set db1 = Nothing


DoCmd.RunCommand acCmdSaveRecord

'Sql Server login prompt pops up after running the below code;'
If DCount("*", "TableA", "[ColA] = [forms]![FRM_LOGS]![USER]") = 0 Then
    MsgBox "User ID not found - contact HelpDesk", vbCritical
    DoCmd.Quit
    Exit Sub
End If

The DCount is triggering the SQL Server Login Prompt. I need this prompt to go away. If I open up a form, query, report, anything where the access object is bound to the data, I get no message. It ONLY happens in VBA when I'm trying to access the data object.

Edit! I did find the culprit. I deleted the linked table to the TableA in sql server, and I relinked it again, and clicked the Save password checkbox. I did this before, and it didn't work. Did it again, and it fixed everything. Not sure why this didn't work the first time. I marked the below as an answer because that did solve the problem given the circumstances.

To clarify: You are using SQL Server authentication (not Windows)? And when linking the tables you didn't store user/password? What is db1, the current frontend database or another db which also has linked tables? — Andre
You could try either this msdn.microsoft.com/en-us/library/ms807027.aspx or this msdn.microsoft.com/en-us/library/dd787978.aspx — Tom K.
@Andre db1 is the OpenDatbase("C:\Workspace\ms1.mdb") I set it in the code above — sksallaj
If you now have "Save password" activated, then you don't need any of the code above with db1 and db2. — Andre

dbmitch dbmitch · Accepted Answer · 2016-07-29T15:45:08

Not sure what you're doing here with two database connections and using DCOUNT on an internal table?

It looks like your database connection has linked tables that have stored passwords

Why not just use your recordset that works to check for a valid user?

Set db1 = OpenDatabase("C:\Workspace\ms1.mdb")

Set rs = db1.OpenRecordset("SELECT [ColA] FROM [dbo_TableA] WHERE [ColA] = """ & [forms]![FRM_LOGS]![USER] & """")
if rs.EOF Then
    MsgBox "User ID not found - contact HelpDesk", vbCritical
    DoCmd.Quit
    Exit Sub
End If

rs.Close
db1.Close
Set rs = Nothing
Set db1 = Nothing

MS Access - SQL Server Login Prompt won't go away

1 Answers