DoCmd.RunSQL with SELECT

[almoes]

Hi all!

Is it possible at all to Use DoCmd.RunSQL to make a SELECT on a table and retrieve the info? If yes, how? the RunSQL is a Sub so I don't know how to retrieve the data result of the query :-( thanxs!

cheers,
alej

 

[RoyVidar]

No - the runsql method of the docmd object is only good for runnig action queries. You'd need a recordset.

Simple sample (requires a reference to Microsoft DAO 3.# Object Library - In VBE - Tools | References)

[tt]dim rs as dao.recordset
set rs=currentdb.openrecordset("select field1, field2 from sometable")
do while not rs.eof
debug.print rs.fields("field1"), rs.fields("field2")
rs.movenext
loop
rs.close
set rs=nothing[/tt]

- hit ctrl+g to study the results, for more samples, do a search here...

Roy-Vidar

 

[almoes]

Cool! does this influence in performance? thanxs!

alej

 

[RoyVidar]

Everything you do, influence performance.

Using a recordset to fetch information is among the faster methods. But everything will depend on setup, number of records, indexes, linked vs native tables, selecting the minimum number of columns vs "select *"...

Here's a couple of performance links Microsoft Access Performance FAQ, Making Access Faster - 168 Tips to Speed Up Your Apps!.

Roy-Vidar

 

[almoes]

OK cool!

now if I have the following SQL: Select MAX(ID) from... the recordset has no field name! How can I get this value???

thanxs,
alej

 

[almoes]

got it now: Fields(0)!