對于下面列出的代碼，除了第一個 SQL 查詢外，它運行良好。我從作業簿中提取地址和狀態資訊，并對資訊運行查詢以查找地址在表中出現的次數。如果我運行代碼并在查詢發送到 Access 之前停止它，我可以從立即視窗中提取查詢命令，轉到 Access，然后運行查詢沒有問題。但是，如果我只是運行 VBA 程式并讓它將查詢發送到 Access，我會一直得到 0 的結果。長話短說，查詢將在 Access 中運行并提供正確的結果，但是當 Excel VBA 將查詢發送到 Access 時，我的結果一直為零（并且沒有錯誤訊息）。任何幫助將不勝感激。
Dim DatabaseFileName As String, connectionstring As String
connectionstring = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & DatabaseFileName & "; Persist Security Info=False;"
Dim conn As New ADODB.Connection
conn.Open connectionstring
Dim rs As New ADODB.Recordset, SQL As String
Dim ExecSQL As New ADODB.Command
With ThisWorkbook.Sheets(1)
For I = 2 To 1235
SQL = ""
If .Cells(I, 7) <> "" Then
SQL = "SELECT Count(VRSC_CUSTOMER_SITES.SITE_ID) AS GCOUNT into [GVRCount1] "
SQL = SQL & "FROM (VRSC_CUSTOMER_SITES) "
SQL = SQL & "WHERE ((VRSC_CUSTOMER_SITES.SITE_STREET Like " & Chr(34) & .Cells(I, 7) & Chr(34) & ") AND ((VRSC_CUSTOMER_SITES.SITE_ST)="
SQL = SQL & Chr(34) & .Cells(I, 5) & Chr(34) & ") AND ((VRSC_CUSTOMER_SITES.SITE_PHONE) Not Like ""999*""));"
rs.Open SQL, conn
SQL = "SELECT * FROM [GVRCount1]"
rs.Open SQL
.Cells(I, 8).CopyFromRecordset rs
End If
Next
End With
With ThisWorkbook.Sheets(2)
.Range("A1").CopyFromRecordset rs
End With
conn.Close
End Sub
uj5u.com熱心網友回復：
本質上，問題出在LIKE運營商身上。每當您通過 ODBC/OLEDB 連接運行 Access 查詢時，要使用的通配符是當前的 ANSI 版本%。但是，在 Access GUI 中，通配符使用舊版本*. 請參閱MSDN 檔案討論此通配符用??法。
要在 Excel 和 Access（VBA 或 GUI）之間兼容，請考慮將未記錄的ALIKE運算子僅使用%. 此外，使用 ADO 命令使用 ADO 引數化并避免將值連接到 SQL 陳述句。下面替換第一個LIKEwith=因為沒有使用通配符并且INTO洗掉了 make-table 操作 using 。此外，New從任何Dim行中洗掉。
Dim DatabaseFileName As String, connectionstring As String, SQL As String
Dim conn As ADODB.Connection, rs As ADODB.Recordset, ExecSQL As ADODB.Command
Dim I As Long
connectionstring = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" _
& DatabaseFileName & "; Persist Security Info=False;"
Set conn = New ADODB.Connection
conn.Open connectionstring
' PREPARED STATEMENT WITH ? PLACEHOLDERS
SQL = "SELECT COUNT(v.SITE_ID) AS GCOUNT " _
& "FROM VRSC_CUSTOMER_SITES v " _
& "WHERE v.SITE_STREET = ? " _
& " AND v.SITE_ST = ? " _
& " AND v.SITE_PHONE NOT ALIKE '999%';" _
For I = 2 To 1235
If ThisWorkbook.Sheets(1).Cells(I, 7) <> "" Then
Set ExecSQL = New ADODB.Command
With ExecSQL
.ActiveConnection = conn
.CommandText = SQL
.CommandType = adCmdText
' BIND PARAMETERS
.Parameters.Append .CreateParameter("street_param", adVarchar, adParamInput, 255, ThisWorkbook.Sheets(1).Cells(I, 7))
.Parameters.Append .CreateParameter("st_param", adVarchar, adParamInput, 255, ThisWorkbook.Sheets(1).Cells(I, 5))
' EXECUTE QUERY AND BIND INTO RECORDSET
Set rs = .Execute
End With
ThisWorkbook.Sheets(1).Cells(I, 8).CopyFromRecordset rs
End If
Next I
With ThisWorkbook.Sheets(2)
.Range("A1").CopyFromRecordset rs
End With
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/399422.html