访问链接表-登录时不保存密码 [英] Access linked table - login without saving password

问题描述

我有一个Access数据库，该数据库正在迁移到Azure SQL服务器后端.

我希望用户能够使用自定义表单来设置用于链接表的详细信息.

当前，我正在使用AttachDSNLessTable用用户凭据重新创建链接表，但是这会将详细信息保存到访问文件中，而这并不是很安全.我曾考虑过在数据库关闭期间删除链接表，但是如果该任务被终止，则连接细节将保留.

有没有一种方法可以仅针对当前会话为链接表指定用户名和密码?

解决方案

如果您正在执行代码的重新链接，则应该能够通过不指定密码的dbAttachSavePWD属性来防止密码被保存.创建链接表时的TableDef对象.为方便起见，您还应该每次都删除 SQL链接表，然后再重新链接它，以确保尊重您的选项，并且Microsoft Access更新链接表的架构.

以下是一些示例代码，这些代码将删除链接表并重新链接它，显示dbAttachSavePWD属性的用法:

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim strConn As String
Dim AzureTableName as String
Dim AccessTableName as String

Set db = CurrentDb()
AzureTableName = "dbo.YourTable"
AccessTableName = "YourTable"
strConn = "ODBC;Driver={SQL Server};Server=YOURSERVER\SQLINSTANCE;Database=MYDATABASE;Trusted_Connection=No;UID=MyUserName;PWD=MyPassword"
db.TableDefs.Refresh
For Each tdf In db.TableDefs
    If tdf.Name = AccessTableName Then
        db.TableDefs.Delete tdf.Name
        Exit For
    End If
Next tdf
Set tdf = db.CreateTableDef(AccessTableName)

'===============================
'If you want the password to be saved, include the following 3 lines of code
'to specify the dbAttachSavePWD attribute of the TableDef being created
'If you don't want to save the password, you would omit these 3 lines of code
'===============================
If InStr(strConn, "PWD=") Then
    tdf.Attributes = dbAttachSavePWD
End If

tdf.SourceTableName = AzureTableName
tdf.Connect = strConn
db.TableDefs.Append tdf

当您省略dbAttachSavePWD属性时，该表将在此会话期间链接并可用.当您关闭Microsoft Access数据库并重新打开它(在运行任何重新链接代码之前)时，该表似乎仍处于链接状态(显示在数据库窗口中)，但是如果您尝试访问它，则会收到错误，阻止在没有正确重新链接的情况下访问数据.

I have an Access database which I am moving to an Azure SQL server back-end.

I want the users to be able to use a custom form to set the details used for the linked tables.

Currently I am using an AttachDSNLessTable to re-create the linked table with a users credentials however this saves the details to the Access file which isn't very secure. I thought about removing the linked tables during the DB close but if the task is killed the connection details will remain.

Is there a way I can specify a username and password for a linked table only for the current session?

解决方案

If you are performing your re-linking from code, then you should be able to prevent the password from being saved by NOT specifying the dbAttachSavePWD attribute of the TableDef object when you create the linked table. To facilitate this, you also should always delete the SQL linked table each time before re-linking it to make sure that your options are respected, and that Microsoft Access updates the schema for the linked table.

Here is some sample code that will delete a linked table and re-link it, showing the dbAttachSavePWD attribute usage:

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim strConn As String
Dim AzureTableName as String
Dim AccessTableName as String

Set db = CurrentDb()
AzureTableName = "dbo.YourTable"
AccessTableName = "YourTable"
strConn = "ODBC;Driver={SQL Server};Server=YOURSERVER\SQLINSTANCE;Database=MYDATABASE;Trusted_Connection=No;UID=MyUserName;PWD=MyPassword"
db.TableDefs.Refresh
For Each tdf In db.TableDefs
    If tdf.Name = AccessTableName Then
        db.TableDefs.Delete tdf.Name
        Exit For
    End If
Next tdf
Set tdf = db.CreateTableDef(AccessTableName)

'===============================
'If you want the password to be saved, include the following 3 lines of code
'to specify the dbAttachSavePWD attribute of the TableDef being created
'If you don't want to save the password, you would omit these 3 lines of code
'===============================
If InStr(strConn, "PWD=") Then
    tdf.Attributes = dbAttachSavePWD
End If

tdf.SourceTableName = AzureTableName
tdf.Connect = strConn
db.TableDefs.Append tdf

When you omit the dbAttachSavePWD attribute, the table is linked and available during this session. When you close the Microsoft Access database and re-open it (prior to running any re-linking code), the table appears to still be linked (shows up in the database window), but if you try to access it, you will receive an error, preventing access to the data without proper re-linking.

这篇关于访问链接表-登录时不保存密码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！