Getting the results of an Access SQL query in VBA 1

[Algernon83]

For a form I'm building, I need to run a query on one of the tables in my database, and enable or disable a pair of buttons based on the result. However, I can't seem to figure out how to run an arbitrary select query and view the results. Just opening a stored query won't do, since there has to be a parameter passed to the query. How can I run a select query in VBA?

Forgive me if this is a dumb question. It seems like this should be simple, but I just can't figure it out.

 

[Zygor]

If in Access-

DoCmd.OpenQuery "Query Name", acViewNormal

or

DoCmd.RunSQL "Select table.* from Table;

 

[Algernon83]

I'm aware of these methods. However, I need to access the results of the query after it's executed. Or rather, just the number of rows returned. How can I do that?

 

[Zygor]

instead of using the two methods, I would create a recordset and use the .recordcount property.

 

[PHV]

You may also consider the DCount function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Zygor]

something like -

Dim rstRecordsetAs ADODB.Recordset
Set rstRecordset= New ADODB.Recordset

With rstRecordset
.Source = "SELECT tblTable.* FROM tblTable;"
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenDynamic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open
If Not .BOF And Not .EOF Then
msgbox .recordcount
End If

End With

rstRecordset.Close
Set rstRecordset= Nothing

 

[Algernon83]

Forgive my ignorance, but I don't know how to populate a recordset with the results of a query. How can I do this?

 

[Algernon83]

Ah! Beaten to the punch. Thank you very much. That did it.