Odtnhj

I have an MS Access database that is connected to SQL server via linked servers.

The linked tables are added using a modified AttachDSNLessTable procedure:

stConnect = "ODBC;DRIVER=" & stDriverName & ";SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
Set td = CurrentDb.CreateTableDef(stLocalTableName)
td.SourceTableName = stRemoteTableName
td.Connect = stConnect

CurrentDb.TableDefs.Append td

I have a facility within the application to change the logged in user, this will remove all the tabledefs:

For Each td In CurrentDb.TableDefs
 If td.Name = stLocalTableName Then
 CurrentDb.TableDefs.Delete stLocalTableName
 End If
Next

Then it will re-add using the procedure above.

Now this appears to work however if I log in as user1 then change user to user2 without closing Access the connection is made using the user1 credentials, running a view that includes SUSER_NAME() shows user1 is the logged in user.

Is there any way to force the connection to be reset or to force the user to be changed?

Edit

My entire login function:

Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stDriverName As String, stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String)
 On Error GoTo AttachDSNLessTable_Err
 Dim td As TableDef
 Dim stConnect As String

 For Each td In CurrentDb.TableDefs
 If td.Name = stLocalTableName Then
 CurrentDb.TableDefs.Delete stLocalTableName
 Exit For
 End If
 Next

 If Len(stUsername) = 0 Then
 '//Use trusted authentication if stUsername is not supplied.
 stConnect = "ODBC;DRIVER=" & stDriverName & ";SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
 Else
 '//WARNING: This will save the username and the password with the linked table information.
 stConnect = "ODBC;DRIVER=" & stDriverName & ";SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
 End If

 Set td = CurrentDb.CreateTableDef(stLocalTableName)
 td.SourceTableName = stRemoteTableName
 td.Connect = stConnect

 CurrentDb.TableDefs.Append td
 AttachDSNLessTable = ""
 Exit Function

AttachDSNLessTable_Err:

 AttachDSNLessTable = err.Description

End Function

A couple ideas to try. I only suggest these because they are easy to check.

1. Try renaming the old table first, then create the new table, then delete the old one. If you trick Access into thinking you are adding rather than replacing, it may cooperate.



2. 
   

   Try adding another element to the connection string (other than UID, which Access treats in a special way) to make it unique per user. I believe you can add an arbitrary tag/value pair to an ODBC connection string and it gets ignored. For example

   
   
   

   "ODBC;Driver=SQL Server;MyUniqueTag=" & stUserName & ";UID=" & stUserName
   

   
   
   

   ODBC connection pools are unique per connection string, so this may fool Access into using a different connection pool because it doesn't know that MyUniqueTag isn't used by the ODBC driver.

   
   

EDIT

Took me a while to realise it. Your issue is that Access caches connections per server,database base. There is no way of clearing this cache (as far as i know of)

However there is a wayaround: The answer is to make the connection unique even if the server or db details haven't changed.

Using DSN Files
You cannot change database name or server name to make a connection unique, but you can change DSN file name which Access will see the connection as "unique" / new connection.

If you use the same DSN file, the connection becomes cached again, so you must use different DSN file for each login attempts.

Here is a general function which allows a user to logIn or logOut of your system. Each login will produce a new DNS file and make a new connection to your DB => allowing your logged in user to be the new user.

Below functions are just conceptual. Change according to your needs
and add error trappings.

Public Function FN_CREATE_DNS_FILE()


 Const Server As String = "" ' Server
 Const Driver As String = "" ' Driver
 Const Port As String = "" ' Port
 Const Database As String = "" ' Database

 Dim DsnFileName As String
 Dim Fso As Object
 Dim DnsFile As Object

 Set Fso = CreateObject("Scripting.FileSystemObject")
 DsnFileName = VBA.Environ$("temp") & "" & VBA.Format(Now(), "yyyy-mm-dd_hh_mm_ss") & ".dsn"

 Set DnsFile = Fso.CreateTextFile(DsnFileName)
 DnsFile.WriteLine "[ODBC]"
 DnsFile.WriteLine "DRIVER=" & Driver
 DnsFile.WriteLine "PORT=" & Port
 DnsFile.WriteLine "DATABASE=" & Database
 DnsFile.WriteLine "SERVER=" & Server

 'Close file and clean up
 DnsFile.Close
 Set Fso = Nothing
 Set DnsFile = Nothing

 FN_CREATE_DNS_FILE = DsnFileName

End Function

Public Function LogOut()

 Dim Qdf As QueryDef

 For Each Qdf In CurrentDb.QueryDefs
 If (VBA.InStr(Qdf.Connect, "ODBC")) > 0 Then
 Qdf.Connect = "ODBC;" 'Either delete if you don't require this object or set to blank connection string
 End If
 Next Qdf

End Function



Public Function LogIn(stUsername As String, stPassword As String)

 Dim Tdf As TableDef
 Dim Qdf As QueryDef
 Dim stConnect As String
 Dim ConForQuery As String
 Dim I As Integer: I = 0

 Dim DsnFileName As String

 On Error GoTo AttachDSNLessTable_Err
 'Produce new DNS file with new filename to make Acces Connection unique
 DsnFileName = FN_CREATE_DNS_FILE()

 stConnect = "ODBC;AUTO_RECONNECT=1;NO_PROMPT=1"
 If Len(stUsername) = 0 Then
 '//Use trusted authentication if stUsername is not supplied.
 stConnect = stConnect & ";Trusted_Connection=Yes"
 ConForQuery = stConnect
 Else
 '//WARNING: This will save the username and the password with the linked table information.
 stConnect = stConnect & ";UID=" & stUsername & ";PWD=" & stPassword
 ConForQuery = stConnect & ";UID=" & stUsername
 End If

 ConForQuery = ConForQuery & ";" & "FILEDSN=" & DsnFileName
 stConnect = stConnect & ";" & "FILEDSN=" & DsnFileName

 On Error GoTo ERROR_Invalid_login
 'Update all linked tables
 For Each Tdf In CurrentDb.TableDefs
 If (VBA.InStr(Tdf.Connect, "ODBC")) > 0 Then
 Tdf.Connect = stConnect & ";TABLE=" & Tdf.Name
 If (I = 0) Then Tdf.RefreshLink 'Refreshing one table is enough as long as table definition hasnt changed
 I = I + 1
 End If
 Next Tdf

 'update all passthrough queries
 For Each Qdf In CurrentDb.QueryDefs
 If (VBA.InStr(Qdf.Connect, "ODBC")) > 0 Then
 Qdf.Connect = stConnect
 End If
 Next Qdf

 LogIn = ""
 Exit Function

AttachDSNLessTable_Err:
 LogIn = Err.Description
 Exit Function

ERROR_Invalid_login:
 LogIn = "Login failed"
 LogOut 'Delete or set blank for all pass through queries
End Function

if a user logs in you would simply call

LogIn(Username, password)

which will update all linked tables as well as passthrough queries.

here is a screenshot.

QryCurrentUser executes MySQL command select user(); which shows the owner of the current connection.
As you can see, each login shows now correct logged in usernames.

If login fails, you have two options. Delete All linked in tables and passthrough queries. or set them to a blank connectionstring.

PS
I have added NO_PROMPT & AUTO_RECONNECT to the connectionstring which prevents showing database connection window when login fails and re-connects automatically (MySQL command, not sure if it works for MSSQL) remove them if not compatible.

Let me know if you were able to achieve this.