我正在嘗試使用以下 VBA 代碼在 MS Access 中運行選擇查詢。
Private Sub ManuReport_Click()
Dim dbs As DAO.Database
Dim rsSQL As DAO.Recordset
Dim StrSQL As String
Set dbs = CurrentDb
strSQL = "SELECT " & _
"dbo_VENDOR1.ITEM_NO," & _
"dbo_VENDOR1.ITEM_PRICE," & _
"dbo_VENDOR2.ITEM_NO," & _
"dbo_VENDOR2.ITEM_PRICE," & _
"dbo_VENDOR1.MANUFACTURER_ITEM_NO," & _
"dbo_VENDOR1.MANUFACTURER," & _
"dbo_VENDOR1.ITEM_NAME " & _
"From dbo_VENDOR2 " & _
"INNER JOIN dbo_VENDOR1 " & _
"ON dbo_VENDOR2.MANUFACTURER_ITEM_NO = dbo_VENDOR1.MANUFACTURER_ITEM_NO " & _
"WHERE dbo_VENDOR1.MANUFACTURER IN ('MANUFACTURER CODE') " & _
"And dbo_VENDOR1.ITEM_PRICE > dbo_VENDOR2.ITEM_PRICE "
Set rsSQL = dbs.OpenRecordset(strSQL, dbOpenDynaset)
End Sub
我已將此添加到 MSACCES 中的按鈕中，以從鏈接的 SQL 資料庫中提取此資訊。我在添加對表單文本框的參考時也遇到了問題，但我可以將其作為一個單獨的問題提交。每當我按下按鈕時，什么都沒有發生。我什至沒有收到錯誤螢屏。我已經看到其他答案，問題似乎是 OpenRecordSet 的使用方式，但我無法理解如何將其應用于此代碼。當我在 Access 中創建單獨的查詢時，查詢本身確實有效，所以我不確定問題出在哪里。我重新格式化了代碼的 SQL 部分，以便在此處更容易閱讀，但我將其格式化為實際 VBA 代碼中的單行。
uj5u.com熱心網友回復：
看起來您想在 Access 中打開一個查詢以根據 SQL 字串進行顯示
以下函式將根據 SQL 字串創建查詢
Function createQry(qryName As String, sSQL As String)
Dim qdf As QueryDef
' Delete existing query
On Error Resume Next
CurrentDb.QueryDefs.Delete (qryName)
On Error GoTo 0
Set qdf = CurrentDb.CreateQueryDef(qryName, sSQL)
End Function
如果您在發布的代碼中使用此代碼
Private Sub ManuReport_Click()
Dim dbs As DAO.Database
Dim rsSQL As DAO.Recordset
Dim StrSQL As String
Set dbs = CurrentDb
StrSQL = "SELECT " & _
"dbo_VENDOR1.ITEM_NO," & _
"dbo_VENDOR1.ITEM_PRICE," & _
"dbo_VENDOR2.ITEM_NO," & _
"dbo_VENDOR2.ITEM_PRICE," & _
"dbo_VENDOR1.MANUFACTURER_ITEM_NO," & _
"dbo_VENDOR1.MANUFACTURER," & _
"dbo_VENDOR1.ITEM_NAME " & _
"From dbo_VENDOR2 " & _
"INNER JOIN dbo_VENDOR1 " & _
"ON dbo_VENDOR2.MANUFACTURER_ITEM_NO = dbo_VENDOR1.MANUFACTURER_ITEM_NO " & _
"WHERE dbo_VENDOR1.MANUFACTURER IN ('MANUFACTURER CODE') " & _
"And dbo_VENDOR1.ITEM_PRICE > dbo_VENDOR2.ITEM_PRICE "
'Set rsSQL = dbs.OpenRecordset(StrSQL, dbOpenDynaset)
Dim qryName As String
qryName = "qryTest"
' close the query in case it is open in Access
DoCmd.SetWarnings False
DoCmd.Close acQuery, qryName
DoCmd.SetWarnings True
' Create the query based on the SQL string
createQry qryName, StrSQL
' Open the query in Access for display
DoCmd.OpenQuery qryName, acNormal, acReadOnly
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/505142.html