how can i run a select statment with vba and use the values it returns?

[Zodiacal]

how can i run a select statment with vba and use the values it returns?

[KnooKie]

have a look at QueryDef and openrecordset in the help files.

[xordevoreaux]

vba.net and vba 6.x aren't implemented the same as far as recordset manipulation.

Which version of VBA, and an Access project (.adp) or an Access db (mdb)?

The help files really do not give a good explanation of potential overhead requirements based on your cursor type, cursor location, and locktype.

In 6.x under Jet, a forward-only cursor has a small footprint but is limited in functionality, your recordset.cachesize defaults to 1 but is more efficiently set to 10 or 100, your version of the MDAC and Jet you're using is important to how cursors work in VBA and the errors raised, etc.

Completely different story with vba.net.
In vba.net, there's 3 little cogs that work together retrieve data in ado.net rather than having you sit there and select among four cursor types and other settings.

There's also a big difference in the performance/functionality if you have set "ANSI SQL-92" compatibiliity on in MS Access 2002. ANSI-92 compatibility in Access 2002, which is Sql-server compatible and affects what you can do in a QueryDef, is different than Access 2000's implementation, where the application defaults to ANSI SQL-89 (which does not permit aggregating functions on a distinct row, etc.) and the only way to change it in 2000 is through calls made using a reference to ADOX.

So, with very little of this explained in the help files, what is the version of MS Access you're running, are you using it native in Jet with ADO, or are you using MS Access as a project linked to a data store through MDSE, Sqlserver, or the sybase or oracle drivers?

Your options in VBA vary widely based on the setup, and the help in 2000 nor 2002 does a good job of explaining the comprehensive relationship across the different portions of your setup.

Access 2000 or 2002?
MDAC 2.1, 2.5, 2.6, 2.7, or 2.8?
MSJet 40.Dll 4.0.7328 or earlier?
Native Access database or an Access project?

If 2000, VBA 5.x or VBA 6.x?
If 2002, did you leave the default to ANSI SQL-89 or switch it to ANSI-SQL 92 under Options, and did you leave the database format as 2000 or set it to 2002?

I can give you examples of easy recordset manipulation for 2000, 2002 for native access databases and what to watch out for depending on your setup, very little of which you can find in the help. The help in 2002 does talk about Ansi-SQL 92, but doesn't talk about the efficiency of cachesize in the same breath as cursor overhead. A good reference is "Professional ADO 2.5 Programming" published by WROX and contributed to by David Sussman, James Conard, Brian Matsik, and others.

If you're using ADO 2.1, there is a similar book entitled "ADO 2.1 Programmer's Reference" also by Wrox but it's probably no longer in print. It had an excellent evaluation and discussion on the efficiency and use of various cursortypes and the effects of cachesize on each cursortype.
--X