MS Access VBA ADODB Recordset.Open table OK 但SQL 失败
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10264764/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MS Access VBA ADODB Recordset.Open table OK but SQL fails
提问by user824232
I have an MS Access 2007 table:
我有一个 MS Access 2007 表：
Columns are DB, Module, CommentID, and Comment
列是 DB、Module、CommentID 和 Comment
I have an SQL query that returns the correct record in MS Access Query Design.
我有一个 SQL 查询，它在 MS Access 查询设计中返回正确的记录。
I have confirmed in the Immediate window that the sql is identical in Ms Access Query Design and the VBA module "Module1".
我已经在立即窗口中确认，Ms Access Query Design 和 VBA 模块“Module1”中的 sql 是相同的。
I call the function as below:
我调用函数如下：
?LookupComment(currentproject.Name,Application.VBE.ActiveCodePane.CodeModule,"1")
Subsequently strSQL in the function is confirmed in the immediate window as
随后函数中的strSQL在立即窗口中确认为
Select * from tblComments where DB='db1.accdb' AND Module='Module1' AND CommentID='1'
If I replace "strSQ" with the "tblComments" the functions returns fine.
如果我用“tblComments”替换“strSQ”，函数返回正常。
But I am getting an error at the rst.open with strSQL
但是我在使用 strSQL 的 rst.open 时遇到错误
Method 'Open' of Object '_Recordset' failed
对象“_Recordset”的方法“打开”失败
Public Function LookupComment(theDB, theModule, theCommentID As String) As String
Dim cn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSQL As String
Set cn = CurrentProject.Connection
Set rst = New ADODB.Recordset
strSQL = "Select * from tblComments where DB='" & theDB & "' AND " _
& "Module='" & theModule & "' AND CommentID='" & theCommentID & "'"
rst.Open strSQL, cn, adOpenDynamic, adLockReadOnly
' rst.Open "tblComments", cn, adOpenDynamic, adLockReadOnly
If rst.EOF = False Or rst.BOF = False Then
rst.MoveFirst
LookupComment = rst!Comment
End If
Set rst = Nothing
Set cn = Nothing
End Function
Thoughts?
想法？
TIA
TIA
回答by HansUp
Test your function with this change:
使用此更改测试您的功能：
strSQL = "Select * from tblComments where DB='" & theDB & "' AND " _
& "[Module]='" & theModule & "' AND CommentID='" & theCommentID & "'"
I surrounded Modulewith square brackets because it is a Jet reserved word. See Problem names and reserved words in Access.
我Module用方括号括起来，因为它是一个 Jet 保留字。请参阅Access 中的问题名称和保留字。
A SELECT statement with that un-bracketed name causes the ADO recordset .Openmethod to fail. As you reported, it succeeds when the same SELECT statement is used for a query opened in the query designer. And Igor's DAO recordset suggestion also worked for me whether or not I enclosed Modulewith brackets; I don't understand why it failed for you.
具有该未加括号的名称的 SELECT 语句会导致 ADO 记录集.Open方法失败。正如您所报告的，当相同的 SELECT 语句用于在查询设计器中打开的查询时，它会成功。无论我是否Module用括号括起来，Igor 的 DAO 记录集建议也对我有用；我不明白为什么它对你失败了。
It's difficult to predict exactly when using reserved words as db object names will bite you in the butt. It's safer to avoid using them entirely. If you can't avoid them, enclose those names in square brackets in your queries to reduce the likelihood of confusing the db engine.
很难准确预测何时使用保留字，因为 db 对象名称会让您大吃一惊。完全避免使用它们更安全。如果您无法避免它们，请将这些名称括在查询中的方括号中，以减少混淆数据库引擎的可能性。
You can download Allen Browne's free Database Issue Checker Utilityand use it to examine your database for reserved words. It will also warn you about other potential problem issues with your database.
您可以下载 Allen Browne 的免费数据库问题检查器实用程序并使用它来检查您的数据库中的保留字。它还会警告您有关数据库的其他潜在问题。
回答by Igor Turman
Use DAO Recordset instead:
改用 DAO 记录集：
Public Function LookupComment(theDB, theModule, theCommentID As String) As String
Dim rst As Recordset
Dim strSQL As String
strSQL = "Select * from tblComments where DB='" & theDB & "' AND " _
& "Module='" & theModule & "' AND CommentID='" & theCommentID & "'"
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
If rst.EOF = False Or rst.BOF = False Then
rst.MoveFirst
LookupComment = rst!Comment
End If
Set rst = Nothing
End Function
On the side note, are you sure you need CommentID as string/text type?
在旁注中，您确定需要 CommentID 作为字符串/文本类型吗？
回答by Todd
I think you're missing a semi-colon at the end of your query.
我认为您在查询末尾缺少分号。
