OpenRecordSet 的数据类型不匹配错误
使用 Access 2007。我正在尝试编写一个 VBA 函数，该函数将根据作为参数传递的表名称、字段和值构造查询。我不断收到“运行时错误‘3464’：条件表达式中数据类型不匹配。”
这是代码：
Function getPrimaryFromForeign(db As Database, table As String, field As String, _
value As Long, _
field2 As String, value2 As Long) As Long
Dim sStr As String
Dim istr As String
Dim rs As Recordset
sStr = "select * from " & table & " where " _
& field & "='" & value & "' and " & field2 & "='" & value2 & "'"
istr = "insert into " & table & "(" & _
field & "," & field2 & ") values ('" & value & "','" & value2 & "')"
Set rs = db.OpenRecordset(sStr)
If rs.RecordCount < 1 Then
db.Execute (istr), dbFailOnError
Set rs = db.OpenRecordset(sStr)
End If
getPrimaryFromForeign = rs("id")
End Function
错误发生在以下行： Set rs = db.OpenRecordset(sStr)
我认为与Value和Value2的变量类型有关。但我使用 typename() 检查了它们，当调用 OpenRecordSet() 时它们都是 Long 。该查询针对两个字段均为 Number 类型的表。那么为什么会出现类型不匹配呢？
Using Access 2007. I'm trying to write a VBA function that will construct a query from the table name, fields, and values that I pass as parameters. I keep getting a "Run-time error '3464': Data type mismatch in criteria expression."
Here's the code:
Function getPrimaryFromForeign(db As Database, table As String, field As String, _
value As Long, _
field2 As String, value2 As Long) As Long
Dim sStr As String
Dim istr As String
Dim rs As Recordset
sStr = "select * from " & table & " where " _
& field & "='" & value & "' and " & field2 & "='" & value2 & "'"
istr = "insert into " & table & "(" & _
field & "," & field2 & ") values ('" & value & "','" & value2 & "')"
Set rs = db.OpenRecordset(sStr)
If rs.RecordCount < 1 Then
db.Execute (istr), dbFailOnError
Set rs = db.OpenRecordset(sStr)
End If
getPrimaryFromForeign = rs("id")
End Function
The error occurs at the line:
Set rs = db.OpenRecordset(sStr)
I think it has something to do with the variable types of Value and Value2. But I've checked them using typename(), and they're both Long when OpenRecordSet() is called. The query is on a table where both of those fields are of type Number. So why is there a type mismatch?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
如果您的参考库中同时有 DAO 和 ADO，则可能会发生这种情况。
在这种情况下，Dim 语句和引用窗口中的声明顺序都很重要。
1) “必须引用并使用 DAO 和 ADO Recordset 对象，明确标注对象的尺寸如下：
2) “确保 DAO 对象库的引用在“引用”对话框中具有更高的优先级，或者清除对Microsoft ActiveX 数据对象。”
请参阅此处：
https://support.microsoft.com/en-us/help/181542/you-receive-a-type-mismatch-error-when-you-run-the-openrecordset-metho
This can occur if you have both DAO and an ADO in you reference library.
In this case declaration order in both your Dim statements and in the references window matters.
1) "You must reference and use both DAO and ADO Recordset objects, dimension the objects explicitly as follows:
2) "Make sure that the reference for the DAO object library has a higher priority in the References dialog box, or clear the reference to Microsoft ActiveX Data Objects."
see Here:
https://support.microsoft.com/en-us/help/181542/you-receive-a-type-mismatch-error-when-you-run-the-openrecordset-metho
我已经很长时间没有使用 Access 了，但我认为问题是因为您将
Value和Value2用单引号括起来在 SQL 语句中，不要将 WHERE 子句中的字段括在方括号中。试试这样：
I haven't used Access in a long time, but I think that the problem is because you're enclosing
ValueandValue2in single quotes in your SQL statement, and not enclosing the fields in the WHERE clause in brackets.Try it like this: