正在创建用于将MS Access链接到Azure SQL server的登录过程

Question

我有一个MS Access程序，它包含许多链接到Azure SQL server的SQL表。理想情况下，我想通过弹出表单创建一个登录过程，在该过程中，我会向用户询问凭据，然后更新链接的表，并使用所需的数据传递查询。然而，我无法让它发挥作用。代码运行良好，没有错误消息，但是当我在执行过程后打开包含链接表的表单时，我仍然会收到登录请求。这个想法是让登录过程在启动屏幕上运行，用户在成功登录后不会被要求在任何地方输入凭据。

这是我正在使用的代码：

Public Function ConnectToAzureSQL(sServer As String, sDatabase As String, sUserName As String, sPassWord As String)
On Error GoTo Proc_err
Dim con As Object
Dim var As Variant
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim strConnect As String
Set db = CurrentDb
Set con = CreateObject("ADODB.Connection")
strConnect = "DRIVER={ODBC Driver 17 for SQL Server}" & _
";Server=sServer" & _
";Database=sDatabase" & _
";UID=sUserName" & _
"PWD=sPassWord".
con.Open strConnect
For Each tdf In db.TableDefs
Debug.Print tdf.Name
If Len(tdf.Connect) Then
tdf.Connect = strConnect
tdf.RefreshLink
End If
Next
con.Close
Proc_exit:
MsgBox ("done")
Exit Function
Proc_err:
Debug.Print Err.Description & " : " & Str(Err.Number)
Resume Proc_exit
End Function

Answer 1

我忘记提到的是，此方法使用SQL server登录方法。幸运的是，我自己找到了答案。如果有人需要，这里是程序。这种方法的缺点是登录数据被捕获在连接字符串中。转念一想，我选择了使用MFA的Azure Active Directory登录方法，在该方法中，您的应用程序中不存储密码。

Public Function ConnectToAzureSQL(sServer, sDb, sUID, sPWD As String, bConn As Boolean)
On Error GoTo Proc_err
Dim con As Object
Dim var As Variant
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim strConnect As String
Set db = CurrentDb
Set con = CreateObject("ADODB.Connection")
sConnect = "DRIVER={ODBC Driver 17 for SQL Server}" & _
";Server=" & sServer _
& ";Database=" & sDb _
& ";UID=" & sUID _
& ";PWD=" & sPWD
con.Open strConnect
bConn = True
DoCmd.OpenForm "Frm_Bericht"
For Each tdf In db.TableDefs
If Len(tdf.Connect) Then
tdf.Connect = strConnect & ";UID=" & sUID & ";PWD=" & sPWD
Forms!Frm_Bericht.Caption = "Update verbinding naar tabel: " & tdf.Name
tdf.RefreshLink
End If
Next
con.Close
DoCmd.Close acForm, "Frm_Bericht"
Proc_exit:
Exit Function
Proc_err:
Debug.Print Err.Description & " : " & Str(Err.Number)
Resume Proc_exit
End Function