MS Access VBA ADODB Recordset.Open table OK but SQL fails
ms-access, vba
Solution
Test your function with this change:
strSQL = "Select * from tblComments where DB='" & theDB & "' AND " _
& "[Module]='" & theModule & "' AND CommentID='" & theCommentID & "'"
I surrounded `Module` with square brackets because it is a Jet reserved word. See Problem names and reserved words in Access.
A SELECT statement with that un-bracketed name causes the ADO recordset `.Open` method 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 `Module` with brackets; I don't understand why it failed for you.
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.
You can download Allen Browne's free Database Issue Checker Utility and use it to examine your database for reserved words. It will also warn you about other potential problem issues with your database.
Problem
I have an MS Access 2007 table: Columns are DB, Module, CommentID, and Comment I have an SQL query that returns the correct record in MS Access Query Design. I have confirmed in the Immediate window that the sql is identical in Ms Access Query Design and the VBA module "Module1". 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 ``` Select * from tblComments where DB='db1.accdb' AND Module='Module1' AND CommentID='1' ``` If I replace "strSQ" with the "tblComments" the functions returns fine. But I am getting an error at the rst.open with strSQL Method 'Open' of Object '_Recordset' failed ``` 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