i'm using dsnless links in access 2019 to a mysql instance on aws. I have an Access login form, which passes uid & password to a connection string in VBA, and connects and executes simple "select 1".
All along, i assumed the credentials from my access input form were being used to check against my MYSQL users, BUT...it looks to maybe use a cached user/password instead? When i enter a wrong password into my access login form, it still successfully connects and executes the qdf / sql query.
(i can 'watch' the connection string in vba does have the wrong password)
how can i force odbc to authenticate using the actual id & password actually being sent in connection string??
This simple login form has worked, but just realizing now it will pass a wrong password from my access form, but yet still connect and execute the sql...
Function TestLogin(uid As String, pwd As String) As Boolean
On Error GoTo testerror
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim RST As DAO.Recordset
Dim strcon As String
Dim errX As DAO.Error
Dim strsql As String
Dim strRW
strcon = "ODBC; Driver=MySQL ODBC 8.0 Unicode Driver;" & _
"SERVER={xxx};DATABASE=xxx;PORT=3306;" & _
"UID=" & uid & "; PWD=" & pwd & ";COLUMN_SIZE_S32=1;DFLT_BIGINT_BIND_STR=1;OPTION=3"
Set dbs = CurrentDb()
dbs.QueryTimeout = 5
Set qdf = dbs.CreateQueryDef("")
qdf.Connect = strcon
qdf.sql = "SELECT current_user"
Set RST = qdf.OpenRecordset(dbOpenSnapshot, dbSQLPassThrough)
TestLogin = True
mysqlUser = uid
floor = DLookup("floor", "tblxx", "user ='" & mysqlUser & "'")
Set qdf = Nothing
Set RST = Nothing
DoCmd.Close
DoCmd.OpenForm "Switchboard"
exit_errorTrap:
Set qdf = Nothing
Set RST = Nothing
Exit Function
Ok, the WAY this works can be confusing.
On application start up we asume you do not by accient (or intention) let any form, any VBA code that runs to touch, or logon or use IN ANY POSSBILE way on startup.
Ok, with above? If you linked the tables correct without UID/Password, if you touch, or even try to open a table from the nav pane? you SHOULD get a ODBC prompt. So, test the above - hold down shift key during startup - NO CODE or NO touch of a linked table is assumed here.
Now, click on a linked table - if the table opens, then you screwed up your table linking, and you DO NOT need the logon, then, right????
Next issue - and READ VERY careful.
If you execute a VALID logon (with your logon code), then ONCE you done this, the correct connection is cached - you can NOT IN ANY POSSBILE way blow out, remove, or re-set that cache.
If AFTER having executed ANY valid logon? Then ANY ADTIONAL attempted logons (say with your logon code) WILL ALWAYS return true and valid. INCLUDING WRONG logons!!!! (they STILL return true!!!!).
The above information thus means some very significant issue's.
to clear hte logon, you will have to (must) exit Access. You thus can't crete a logout form - you must EXIT applcation.
Worse yet, you BETTER ALWAYS BUT ALWAYS BUT ALWAYS exit access first to clear that cache when developing and testing re-linking of tables. If you at any point in time during development by intention (or accident) open a linked table, and enter uid/password (or do so via code, or do so by JUST clicking on a linked table that DOES and DID save the uid/password?.
Then if you decide to re-link tables - EVEN WITH a incorrect UID/password, they WILL REPORT they linked ok!!!! - but they did not!!!! Even during re-link, if you use the wrong UID/Password, you are screwed, since access will in fact use the cached one (if your uid/password are wrong!!!!).
So, you first have to 100%, if not 200% do the first test above - check, double check/ triple check that linked tables do NOT WORK when clicked on.
Now, run your VBA logon. Now, cliking on any linked table should work. But as noted, ONCE you touched, or logged on (by any possbile means), the UID/PW is cached, and will remain so for that session until such time you exit access.
What this means is you get ONCE chance to logon. They can fail, but the instant you achieve ONE successful logon, then ALL FURTHER attempts at logons WILL WORK - even if incorrect. In summary:
You can prompt for a logon. you can do this without having to re-link tables. All linked tables, and EVEN pass-though queries will use that cached logon.
You MUST exit access to clear this cache, and thus it is IMPOSSILE to now launch your custom logon form and logon with a different user, since the previous user remains cached.
This also as noted means during deveopment, to link tables without uid/pw, you need to exit Access. launch access (without ANY POSSBILE touch of linked tables/data).
You then execute your logon. You THEN re-link tables without UID/Password. Then you have to exit access, re-launch (shift startup by-pass). Now click on a table - it should fail - prompt for odbc.
But, if you exit, shift- by pass startup. Execute your logon. Now click on a linked table - it should work. If it does not, or the first test (without a logon DID work, then as noted, you have royal screwed up something here.
so, for wrong/incorrect logons? You can continue trying to log on.
But ONCE you have logged on - that's quite much the end of this process. Any further attempts at logons will ALWAYS work - even for totaly wrong logons - you have to exit to clear the PW cache.
you also as a result can thus not use two cached passwords - say some tables linked as read only, and some tables linked as read/write (based on different uid/pw). and the reason is you can't control which logon will be used - and access will thus choose the read/write logon.
So, I suppose the most simple explain? Once a uid/pw is cached, then you can and will be able to use all linked tables regardless of what uid/logon you attempt to use. And EVEN if at that point in time you did decide to run your re-link code? it will re-link JUST fine, even if you supply a incorrect uid/password. But, be carful!!! - you can in some cases thus re-link, check that the tables worked, but when you exit, and re-enter - you find those linked tables now don't work!!!
So, be VERY careful, if not outright SUPER careful when you re-link, especially if you been messing around with connections - you have to exit access, shift by-pass startup. and THEN re-link your tables. One stray cached uid/password, and you are in heaps of trouble - since things will work, and not make any sense at all.
keeping the above simple concept in mind - all will and should now make sense.
I been using this code:
Function TestLogin(strCon As String) As Boolean
On Error GoTo TestError
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Set dbs = CurrentDb()
Set qdf = dbs.CreateQueryDef("")
qdf.connect = strCon
qdf.ReturnsRecords = False
'Any VALID SQL statement that runs on server will work below.
' this does assume user has enough rights to query built in
' system tables
qdf.sql = "SELECT 1 "
qdf.Execute
TestLogin = True
Exit Function
TestError:
TestLogin = False
Exit Function
End Function
now you used a pass-though query - and I never done/use that before - and don't think it will work. You have to use above format.