强制Access忘记链接表的用户名和密码 [英] Forcing Access to forget a username and password for a linked table
问题描述
我有一个通过链接服务器连接到SQL Server的MS Access数据库.
I have an MS Access database that is connected to SQL server via linked servers.
使用修改后的AttachDSNLessTable
过程添加链接表:
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
我在应用程序中具有更改登录用户的功能,这将删除所有tabledef:
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.
现在这似乎可行,但是如果我以user1
身份登录,然后将用户更改为user2
而没有关闭访问,则使用user1
凭据建立连接,运行包含SUSER_NAME()
的视图将显示user1
是已登录的用户.
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?
修改
我的整个登录功能:
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
推荐答案
编辑
花点时间让我意识到.您的问题是Access按{server,database}为基础缓存连接.据我所知,没有办法清除此缓存
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.
使用DSN文件 您不能更改数据库名称或服务器名称以使连接唯一,但是可以更改DSN文件名,Access会将其视为唯一"/新连接.
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.
如果使用相同的DSN文件,该连接将再次被缓存,因此每次登录尝试都必须使用不同的DSN文件.
If you use the same DSN file, the connection becomes cached again, so you must use different DSN file for each login attempts.
这是一项常规功能,允许用户登录或注销系统.每次登录都会产生一个新的DNS文件,并与您的数据库建立新的连接=>允许您登录的用户成为新用户.
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
如果用户登录,您只需拨打电话
LogIn(Username, password)
这将更新所有链接表以及传递查询.
which will update all linked tables as well as passthrough queries.
这是屏幕截图.
QryCurrentUser执行MySQL命令select user();
,该命令显示当前连接的所有者.
如您所见,每个登录名现在都显示正确的登录用户名.
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 我已添加 NO_PROMPT& AUTO_RECONNECT 到连接字符串,可防止在登录失败并自动重新连接(MySQL命令,不确定是否适用于MSSQL)时显示数据库连接窗口(如果不兼容).
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.
这篇关于强制Access忘记链接表的用户名和密码的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!