CODEWITHSUNDEEP
ms-accessvba
user824232
user824232

Reputation: 83

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

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

Upvotes: 2

Views: 41858

Answers (3)

HansUp
HansUp

Reputation: 97101

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.

Upvotes: 6

Igor Turman
Igor Turman

Reputation: 2205

Use DAO Recordset instead:

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?

Upvotes: 2

Todd
Todd

Reputation: 922

I think you're missing a semi-colon at the end of your query.

Upvotes: 0

Related Questions