How to check the result of a SELECT query

[Guest]

I have a SELECT query and use .EXECUTE to execute. How can I check if any record has been selected by this query? Thanks.

 

[Douglas J. Steele]

You can't use .EXECUTE with a SELECT query: it's only intended to be used
with Action queries (INSERT INTO, UPDATE, DELETE)

With a SELETE query, you need to open a recordset, and work with it.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a SELECT query and use .EXECUTE to execute. How can I check if

any record has been selected by this query? Thanks.

 

[Guest]

Thanks. Do you have any sample code that I can follow?

 

[Wayne Morgan]

This uses DAO, you may need to go to Tools|Refernces in the code editor and
check the box next to DAO, depending on your version of Access.

Dim rst As DAO.Recordset, strSQL As String
strSQL = "SELECT ....." 'fill in the rest of your query statement
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
'manipulate the recordset here to do what you want
rst.Close
Set rst = Nothing

Lookup the Recordset Object in the code editor's help to get the methods and
properties available for the object.

 

[Guest]

Thanks Wayne. What about ADO? Is there any corresponding to OpenRecordset? I am writing ADO and my db is SQL server.

 

[Douglas J. Steele]

Check http://msdn.microsoft.com/library/en-us/ado270/htm/mdmthopenx.asp

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Wayne. What about ADO? Is there any corresponding to

OpenRecordset? I am writing ADO and my db is SQL server.

 

[Albert D. Kallal]

If you setup the linked table already...then here is an example. In this
folwing case, table contacts is on sql server..but is linked...


'An example DAO vs ADO recordset loop, you'll see how similar they are:

'--- begin DAO ---
Dim rst As dao.Recordset

Set rst = CurrentDb.OpenRecordset("select * from contacts")
Do While rst.EOF = False
Debug.Print rst!FirstName
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
'--- end DAO ---

'--- begin ADO ---
Dim rs As New ADODB.Recordset

rs.Open ("select * from contacts"), CurrentProject.Connection
Do While rs.EOF = False
Debug.Print rs!FirstName
rs.MoveNext
Loop
rs.Close
Set rs = Nothing

 

[Guest]

Thanks.