runtime error 3065 Cannot execute a select query

[ckib]

Hi folks,

Need some help! I'm getting this error:

Run-time error '3065': Cannot execute a select query.

Here is the code:

' if no contacts set for delete, skip the cleanup

strSQL = "SELECT * FROM tblContact " & _
"WHERE (((tblContact.cont_projectID)='" & strProjectID & "' Or (tblContact.cont_projectID) Is Null) AND ((tblContact.cont_active_status)='D'));"

Set myCountQuery = myDB.CreateQueryDef("", strSQL)

myCountQuery.Execute ' Run temporary QueryDef

Is it the SQL string? I have updated tblContact earlier on in the code, using a DAO recordset, also using a transaction and have definitely commited the updates.

Any help would be greatly appreciated! Thanks.

 

[ckib]

Okay, I just pulled out my dev guide and see that the execute method is used for an action query. What I was trying to do was create a temp query so that I could count the records using DCount with the query name. I removed the .execute statement and tried to do the DCount:

lngCount = DCount([contactID], "myCountQuery") ' count recs in query

but now get:

Run-time error '2465': Can'f find the field '|' in your expression. I know I must be close, but can't get the syntax right ... or if someone can tell how to accomplish what I'm trying to do, that would be much appreciated. Thanks.

 

[ckib]

working around this by using DAO recordset:

' if no contacts set for delete, skip the cleanup

strSQL = "SELECT * FROM tblContact " & _
"WHERE (((tblContact.cont_projectID)='" & strProjectID & "' Or (tblContact.cont_projectID) Is Null) AND ((tblContact.cont_active_status)='D'));"

lngCount = 0
Set rsContact = myDB.OpenRecordset(strSQL)
If rsContact.RecordCount < 1 Then ' no contacts set to delete
.
.
.

But there must be a better way that this!

 

[SydneyGeek]

I think the DAO Recordset approach is the way to go in this case. You can't use RecordCount until you move to the last record, and where it is null, you will get an error. So you can either (1) check to see that you are not at the end of the recordset -- see below or (2) try to go to the first record. If no error, you're OK. If you get one, trap the error and move on from there. So...

Try replacing

myCountQuery.Execute ' Run temporary QueryDef

with

Set rst=myCountQuery.OpenRecordset

If not rst.EOF Then 'Records exist..
   'continue processing
End if

Denis

 

[ckib]

Hi Denis,

Thanks for the post. Since I am using a table-type recordset, I believe it is not necessary to move to the end of the recordset before using the recordcount property. The following code appears to work great:

' if no contacts set for delete, skip the cleanup

strSQL = "SELECT * FROM tblContact " & _
"WHERE (((tblContact.cont_projectID)='" & strProjectID & "' Or (tblContact.cont_projectID) Is Null) AND ((tblContact.cont_active_status)='D'));"

lngCount = 0
Set rsContact = myDB.OpenRecordset(strSQL)
If rsContact.RecordCount < 1 Then ' no contacts set to delete for this project or not assigned

' write an audit trail message
strMessage = strProjectID & ": No contacts set to delete for this project or unassigned"

strSQL = "INSERT INTO tblMessages ( msg_form, msg_date, msg_time, msg_seq_num, msg_contactID, message ) " & _
"VALUES('frmProject', #" & Date & "#, #" & Time & "#, " & lngSeqNum & ", " & lngCount & ", '" & strMessage & "');"

DoCmd.SetWarnings False
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings True
lngSeqNum = lngSeqNum + 1 ' increment audit sequence number
GoTo CmdCloseProject_update_contacts

End If

lngCount = rsContact.RecordCount
.
.
.

Actually, I don't really care how many records are in the set, only if there are NO records in the set. I guess I could also use .EOF ...

Thanks again! C

 

[SydneyGeek]

You're welcome

Glad you got an answer.

Denis