如何传递用户名和密码以从Excel访问数据库?

Question

我根据书本说明编写了我的第一个 ADO/SQL 数据提取代码。

数据库具有用户名和密码，但此处没有用户名和密码输入。

Sub GetAccessData_With_SQL()    
'step 1 Declare your variables
Dim MyConnect As String
Dim MyRS As ADODB.Recordset
Dim MySQL As String
'step 2 Declare your connnection string
MyConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source = C:ThorsysTAS.accdb"
'step 3 Build your SQL statement
MySQL = "SELECT productID, headID, list FROM dbo_tblSTproduct"
'step 4 Instantiate and specify your recordset
Set MyRS = New ADODB.Recordset
MyRS.Open MySQL, MyConnect, adOpenStatic, adLockReadOnly
'step 5 Copy the recordset to Excel
Sheets("ADO and SQL").Select
ActiveSheet.Range("A2").CopyFromRecordset MyRS
'step 6 Add column labels
With ActiveSheet.Range("A1:E1")
.Value = Array("productID", "headID", "list")
.EntireColumn.AutoFit
End With   
End Sub

Answer 1

您必须将用户名和密码放在连接字符串中...举个例子：

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:ThorsysTAS.accdb;User ID=myUsername;Password=myPassword;