sql in vb (1 Viewer)

[Massoud]

Hi.
I want to run a sql command in my vb code using:
docmd.runsql "sql command string"
Is there any way to put the result in recordset variable as we do with tables:

dim docs as recordset
set docs= currentdb.openrecordset(tablename)....

Thanks again.

 

[ecniv]

dim strSql as string
dim rstDocs as recordset

'---- set the sql statement and open the recordset 
'----   (instead of dbengine(0)(0) put currentdb)
strsql="Select * from tblDocs"
set rstDocs = dbengine(0)(0).openrecordset(strsql)

'---- do processing

'---- close recordset
rstdocs.close

'---- clear up
set rstDocs=nothing

Try that


Vince

 

[Mile-O]

Hi.
I want to run a sql command in my vb code using:
docmd.runsql "sql command string"

Is there any reason why you won't just use a stored query definition?

And, if you are trying to do this with a SELECT query then it won't work as the RunSQL methos only works for ACTION queries.

 

[Massoud]

Thank you. It works well.
The reason to use this method is that I don't want to make the query list a long one. and this way i can manage to develop the program easier since every thing is in the code and in hand.

 

[Pat Hartman]

SQL strings in VBA are less efficient than stored querydefs and contribute to database bloat.

 

[Massoud]

Thank you for your consideration,Pat.
So we have another question. Suppose we have two forms: form1 & form2.
There is also a query (myquery) to be run and there is a criteria in this query based on a vlaue in form1. can we run the same query with the same criteria based on a value in form2?

 

[Pat Hartman]

The query can only have a single set of parameters so it can only refer to fields on a single form. If you want to use the same query as the RecordSource for multiple forms, they all need to share the same parameter form. I do this frequently. The form contains controls for criteris and a combobox or option group to select which form to open.

If you are using Jet tables, it is fine to use the where argument of the OpenForm Method. That way the query doesn't need any selection criteria.