Vba – MS Access VBA ADODB Recordset.Open table OK but SQL fails

ms-accessvba

enter image description here

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

Best Answer

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.

Related Topic