Trying to replace a saved query with sql statement

[Fred Boer]

Hello!

I am trying to replace the use of a saved query in some code.

Current situation:

Saved query called Qry_CheckforAuthor

SELECT Tbl_Library.*
FROM Tbl_Library LEFT JOIN Tbl_BookAuthor ON Tbl_Library.Book_ID =
Tbl_BookAuthor.Book_ID
WHERE (((Tbl_BookAuthor.Book_ID) Is Null));

Code using query:

If DCount("*", "Qry_CheckForAuthor") > 0 Then
MsgBox "Records of books without authors exist in the table! Please contact
the Database Administrator immediately.", vbOKOnly + vbCritical, "W. Ross
Macdonald School"
End If

What I want to do is to replace this saved query with a SQL statement
created on the fly. I don't know how to do this. I *can* do this if I want
to run an update query, but I don't know how to modify this so that I can do
a DCount against the SQL query... Am I on the right track by doing something
like the following?

Dim db As Database, sSQL As String, rs As Recordset

sSQL = "SELECT Tbl_Library.* FROM Tbl_Library LEFT JOIN Tbl_BookAuthor ON
Tbl_Library.Book_ID = Tbl_BookAuthor.Book_ID WHERE Tbl_BookAuthor.Book_ID Is
Null"

Set db = CurrentDb()
rs=db.Execute sSQL, dbFailOnError

If DCount("*","rs")>0...

 

[Fred Boer]

Or is this?

Thanks!
Fred

P.S. Obviously there are tons of errors in this and my previous post... I'm
really just looking to see if I am on the right track...


Dim db As Database
Dim rs As Recordset
Set db = CurrentDb

Set rs = db.OpenRecordset("SELECT Tbl_Library.* FROM Tbl_Library LEFT JOIN
Tbl_BookAuthor ON Tbl_Library.Book_ID = Tbl_BookAuthor.Book_ID WHERE
Tbl_BookAuthor.Book_ID Is Null")

If DCount("*", "rs") > 0 Then
MsgBox "Records of books without authors exist in the table! Please contact
the Database Administrator immediately.", vbOKOnly + vbCritical, "W. Ross
Macdonald School"
End If

 

[Allen Browne]

To get a count from a SQL statement created on the fly:
sSQL = "SELECT Count(Tbl_Library.BookID) AS CountOfBookID FROM ...
Set rs = dbEngine(0)(0).OpenRecordset(sSQL)

For an example of a SQL statement created on the fly that works like
DLookup() does, see:
http://allenbrowne.com/ser-42.html

 

[Fred Boer]

Thanks Allen!

Fred

To get a count from a SQL statement created on the fly:
sSQL = "SELECT Count(Tbl_Library.BookID) AS CountOfBookID FROM ...
Set rs = dbEngine(0)(0).OpenRecordset(sSQL)

For an example of a SQL statement created on the fly that works like
DLookup() does, see:
http://allenbrowne.com/ser-42.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

can Tbl_BookAuthor.Book_ID