How to run a select query in VBA (Not a saved Query) 1

[vbvbvbvb]

Hi All,

I am unable to find out how to run a select query in VBA.
I am building a select query based on the input given by the users through Text boxes on the Form(at run time).
Then assigning it to a string variable, say strSQL.

When the user fills in the Text boxes and clicks a button, I need to execute the query and retrieve the records at run time.

By using "DoCmd.OpenQuery", I could run only a saved query.
And using "DoCmd.RunSQL", its possible only to update/delete records and not to select.

Could anyone plz help me with this.

Thanks in advance.

 

[PHV]

Have a look at Recordset (either DAO or ADODB)

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

 

[vbvbvbvb]

PHV, Thank you for responding.

I have been trying to extract records using Recordset, but couldn't find a way to run select query.
The only method I found to run select query is DoCmd.OpenQuery.
But, it could be used only to run a stored query.

I am taking input from user, and using those values to construct the select query at run time.
But, I couldn't find a way to run that query.

So, could anyone please explain in more detail.

 

[earthandfire]

Try something like:

Dim strSQL As String
Dim rs As Recordset
strSQL = "SELECT FieldList FROM Table"
set rs = CurrentDb.OpenRecordset strSQL
then you can use rs to access the data

NB this presumes DAO

Hope this helps.

 

[vbvbvbvb]

Thank you earthandfire.
I am able to get the recordset for my query.
Can you sugest a way to view that recordset in datasheet view.

i.e., say, rs is the recordset object which contains the result of my query.
I want to show the records in datasheet view, so that the users could see the records and modify the field values.
I do not want to show the records in Debug window.

so, please help me with the VBA code for viewing the recordset in datasheet view.

Thank you