1

I have script that creates a pass through query using a connection string and a password. It works on first connection attempt: fails until the user enters the correct password. If the connection string is lost or the user needs to renter it for any reason, it will succeed no matter what password is passed through the connection string.

I've tried created named queryDefs and removing them, unique and non-unique. If this is the way it has to be, I'll work with it, but I don't like not understanding what's going on, so if anyone has any insight, that would be great. Thanks!

Dim db As DAO.Database, qDef As QueryDef, rst As DAO.Recordset
Set db = CurrentDb

Set qDef = db.CreateQueryDef(vbNullString)
With qDef
    .Connect = connStr
    .sql = sql
    .ReturnsRecords = True
    Set rst = .OpenRecordset(dbOpenSnapshot, dbSQLPassThrough)
End With
If readAll Then
    With rst
        If Not .EOF And Not .BOF Then
            .MoveLast
            .MoveFirst
        End If
    End With
End If
Set PassThroughRecordset = rst
drexasaurus
  • 60
  • 6
  • You might want to include the SQL in variable `sql` – HardCode Aug 26 '16 at 18:29
  • I tested `"select top 1 LegalEntityName, ContractorId, GroupId From LegalEntity"` most recently while testing the function, I believe sometimes a connection is established with a test statement, some hard-coded value such as `"Select 'dog' as Value"` I used an ODBC connection string to a SQL Server database as the connection string. After a successful connection, the wrong pword in PWD= part of the string will pass, even if you leave it blank. – drexasaurus Aug 26 '16 at 19:27

1 Answers1

1

This is by design, connections with user/password are cached as long as Access runs, and server/db stay the same.

See here and the linked blog post:

Save password for ODBC connection to MS SQL server from MS Access 2007

How to avoid prompting for user id and password in MSAccess 2003

Community
  • 1
  • 1
Andre
  • 26,751
  • 7
  • 36
  • 80