Temp Query

[Berny]

Can anyone tell me if and how to create a temporary query on the fly using
VBA in Access?

 

[Allen Browne]

You can CreateQueryDef(), but if it is just for temporary use, there is no
need. You can just use a string containing a SELECT query statement and
OpenRecordset(), or Execute the string it is an action query.

Example 1: SELECT query:
Dim strSql As String
Dim rs As DAO.Recordset
strSql = "SELECT Customers.* FROM Customers;"
Set rs = dbEngine(0)(0).OpenRecordset(strSql)
...

Example 2: DELETE query:
strSql = "DELETE FROM Customers;"
dbEngine(0)(0).Execute strSql, dbfailOnError

 

[Berny]

Thank you

However I'm new at this, I don't completely understand, could you tell me
what I'm doing wrong?

I'm getting a compile error: "User-defined type not defined"

And

rs As DAO.Recordset

Is highlighted

Private Sub DeleteRecords_Click()
On Error GoTo Err_DeleteRecords_Click

Dim strSql As String
Dim rs As DAO.Recordset
strSql = "SELECT tblVolunteers.blnVolDF FROM tblVolunteers WHERE
(((tblVolunteers.blnVolDF)=-1));"
Set rs = DBEngine(0)(0).OpenRecordset(strSql)


Exit_DeleteRecords_Click:
Exit Sub

Err_DeleteRecords_Click:
MsgBox Err.Description
Resume Exit_DeleteRecords_Click

End Sub

 

[Allen Browne]

From the code window, choose References on the Tools menu.
Check the box beside:
Microsoft DAO 3.6 Library.

You must be using Access 2000 or 2002. All other versions have a reference
to this library automatically.

More on references:
http://allenbrowne.com/ser-38.html

BTW, I presume you want to actually do something with the recordset when you
have it open? It does not display, but is useful for finding things out
programmatically. When your procedure is finished, you need to close it and
set the object to Nothing, i.e.:
rs.Close
Set rs = Nothing

If you just wanted to lookup a single value, you could use DLookup()
instead. More info:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

 

[Berny]

Thank you that was the problem with the DAO

But I'm still having trouble

What I'm trying to do is delete the records that meet the criteria
(tblVolunteers.blnVolDF)=-1) from the tblVolunteers Table, but I'm getting
and error "Invalid Operation"

If I use

strSql = "SELECT * FROM tblVolunteers;"

It will delete all the records

Private Sub DeleteRecords_Click()
On Error GoTo Err_DeleteRecords_Click

Dim strSql As String
Dim rs As DAO.Recordset
strSql = "DELETE tblVolunteers.blnVolDF FROM tblVolunteers WHERE
(((tblVolunteers.blnVolDF)=-1));"
Set rs = DBEngine(0)(0).OpenRecordset(strSql)
rs.Close
Set rs = Nothing

Exit_DeleteRecords_Click:
Exit Sub

Err_DeleteRecords_Click:
MsgBox Err.Description
Resume Exit_DeleteRecords_Click

End Sub

 

[Allen Browne]

The FROM clause (attempting to delete from one field) is probably the issue.

The excessive brackets Access gives you are superflous.

Assuming blnVoIDF is a yes/no field, try:

strSql = "DELETE FROM tblVolunteers WHERE (tblVolunteers.blnVolDF =
True);"

 

[Berny]

Thank You

That did the trick

Now I just need to build some custom error messages and I done.

Thank you again!

Private Sub DeleteRecords_Click()
On Error GoTo Err_DeleteRecords_Click

Dim strSql As String

strSql = "Delete FROM tblVolunteers WHERE (tblVolunteers.blnVolDF =
True);"
DoCmd.RunSQL strSql

Exit_DeleteRecords_Click:
Exit Sub

Err_DeleteRecords_Click:
MsgBox Err.Description
Resume Exit_DeleteRecords_Click

End Sub