我在 db4free.net 上設定了一個測驗資料庫，并將 Northwind 培訓資料庫的副本上傳到其中，以查看是否可以將一些資訊提取到 excel 作業簿中并不斷收到通用的未指定/自動化錯誤。
我在參考資料中包含了“Microsoft ActiveX Data Objects 2.8 library”，甚至嘗試了 6.1 以獲得良好的衡量標準。
在任何人嚇壞我之前，包括用戶名和密碼；這個測驗資料庫上唯一存在的是一個訓練資料集。我在那里存盤了零個個人資訊。
這是我的代碼：
Sub sqlTest()
'Declare some strings to hold the connection string and the SQL statement
Dim cnStr As String
Dim sqlStr As String
'Define a connection and a recordset to hold extracted information
Dim oConn As ADODB.Connection
Dim rcSet As New ADODB.Recordset
Set oConn = New ADODB.Connection
Set rcSet = CreateObject("ADODB.Recordset")
'connection string to connect to db4free.net
cnStr = "Driver={MySQL ODBC 8.0 Unicode Driver};SERVER=85.10.205.173;DATABASE=resumedemo;PORT=3306;UID=jwaycaster;PWD=resumedemo123;"
'Test SQL query
sqlStr = "SELECT * FROM `Employees`"
'This is where it crashes
oConn.Open cnStr
oConn.CommandTimeout = 900
rcSet.Open sqlStr, oConn
Sheets(1).Range("A1").CopyFromRecordset rcSet
rcSet.Close
oConn.Close
End Sub
我搜索了幾個相關主題，但似乎找不到答案。希望我錯過了一些簡單的東西。
為后代編輯：閱讀回復后，我意識到這臺計算機上沒有安裝驅動程式（我正在探親，不敢相信我忘了檢查）。安裝驅動程式并更新我的參考后，我的代碼執行得很好，但我建議改用 CDP1802。
uj5u.com熱心網友回復：
有時自己處理錯誤會很有用。添加參考
- Microsoft ActiveX 資料物件 6.1 庫
- Microsoft ActiveX 資料物件 RecordSet 6.0 庫
Option Explicit
Sub sqlTest()
' credentials
Const SERVER = "85.10.205.173"
Const DB = "resumedemo"
Const UID = "jwaycaster"
Const PWD = "resumedemo123"
'Define a connection and a recordset to hold extracted information
Dim oConn As ADODB.Connection, rcSet As ADODB.Recordset
Dim cnStr As String, n As Long, msg As String, e
'connection string to connect to db4free.net
cnStr = "Driver={MySQL ODBC 8.0 Unicode Driver};SERVER=" & SERVER & _
";PORT=3306;DATABASE=" & DB & _
";UID=" & UID & ";PWD=" & PWD & ";"
'Test SQL query
Const SQL = "SELECT * FROM `Employees`"
' connect
Set oConn = New ADODB.Connection
'oConn.CommandTimeout = 900
On Error Resume Next
oConn.Open cnStr
If oConn.Errors.Count > 0 Then
For Each e In oConn.Errors
msg = msg & vbLf & e.Description
Next
MsgBox msg, vbExclamation, "ERROR - Connection Failed"
Exit Sub
Else
MsgBox "Connected to database " & oConn.DefaultDatabase, vbInformation, "Success"
End If
' run query
Set rcSet = oConn.Execute(SQL, n)
If oConn.Errors.Count > 0 Then
msg = ""
For Each e In oConn.Errors
msg = msg & vbLf & e.Description
Next
MsgBox msg, vbExclamation, "ERROR - Execute Failed"
Else
Sheets(1).Range("A1").CopyFromRecordset rcSet
MsgBox SQL & " returned " & n & " records", vbInformation
End If
On Error GoTo 0
oConn.Close
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/372748.html