我有以下代码:
Private Sub lst1Model_Operation_Click()
Dim db As Database
Dim sSQL As String
Dim rst As Recordset
Set db = CurrentDb
sSQL = "SELECT * FROM qryOrder_Model_Operation_Value WHERE Model_Operation_ID = " & CInt(Me![lst1Model_Operation].Value)
Debug.Print sSQL 'when pasted this into a query SQL, it works flawlessly.
Set rst = db.OpenRecordset(sSQL) 'error line
'some code here
rst.Close
Set db = Nothing
End Sub
我无所适从。
Debug.Print看起来像:SELECT * FROM qryOrder_Model_Operation_Value WHERE Model_Operation_ID = 748
就像我说的，如果我将
Debug.Print粘贴到Access本身的查询中，它将产生所需的结果。我尝试在值周围加上''，但是使用CInt()已经确保将其解析为整数。 Model_Operation_ID还希望获得一个整数(否则它也将无法在单独的查询中使用)。
编辑:
qryOder_Model_Operation_Value如下:
SELECT tbl1Model_Operation.Model_Operation_ID, tbl1Model_Operation.Model_ID, tbl1Model_Operation.Operation_Value_ID, tbl2Operation_Value.Operation_Name_ID, tbl3OperationsList.Operation_Name, tbl1Order_Model.Quantity AS [Počet párov], tbl1Order_Model.Order_ID
FROM tbl3OperationsList INNER JOIN (tbl2Operation_Value INNER JOIN (tbl1Model_Operation INNER JOIN tbl1Order_Model ON tbl1Model_Operation.Model_ID = tbl1Order_Model.Model_ID) ON tbl2Operation_Value.Operation_Value_ID = tbl1Model_Operation.Operation_Value_ID) ON tbl3OperationsList.Operation_ID = tbl2Operation_Value.Operation_Name_ID;
最佳答案
确保Access理解您希望第一个成为DAO.Recordset而不是ADODB.Recordset:
'Dim rst As Recordset
Dim rst As DAO.Recordset
ADO和DAO对象模型都包括
Recordset对象。尽管在某些方面相似，但不能互换使用。当您的Access VBA项目引用包含ADO(ActiveX数据对象)版本并且该引用的优先级高于DAO引用时，
Dim rst As Recordset将为您提供ADODB.Recordset。通过始终在适当时用Recordset或DAO限定ADODB声明，避免不受欢迎的意外。