How to refer to returned recordsets after executing SQL query

[sjh]

Hi there,
I want to execute a SQL query in VBA such as
strSQL = "SELECT EmpName FROM Employee"
DoCmd.RunSQL strSQL

My questions are:
1. After I run the query, how do I access the returned recordset, i.e. "EmpName"?
2. Are there other ways to execute SQL query instead of DoCmd.RunSQL strSQL?
3. Is there a good guide/tutorial on this type of topic?

Thanks in advance!
-sjh

 

[DseeDm]

Sounds like you want to open a recordset...

What version of Access are you using?

 

[newfrontiers]

sjh,

I usually use ADODB. Here is an example:

Dim cnn1 as new ADODB.Connection
Dim rst1 as New ADODB.Recordset
Dim strSQL as String

Set cnn1 = CurrentProject.Connection

'Set up SQL string
strSQL = "SELECT EmpName FROM Employee"

'Open the recordset. Make sure you reference the connection otherwise you will receive an error.
rst1.open strSQL,cnn1, adOpenKeyset, adLockOptimistic

'Now that the recordset is open, we can do a bunch of different things. Just look into how to manage recordsets within Help or MSDN site. In this example, we set up loop to go through all records and print the Employee name in the Immediate window in VBA.
Do until rst1.EOF

Debug.Print "Employee: " & rst1("EmpName&quot

rst1.MoveNext

Loop

rst1.Close
cnn1.Close

Hope this helps. I am sure people at this site could improve on this, but it is a start.

Mediocrity is the death of a species.

 

[sjh]

(sorry for asking such a basic question....but)

What is the difference between ADODB and DAO(data access objects)?? I am using Access 2002

Thanks! - sjh

 

[sjh]

Another question for newfrontiers:

I am using your sample code.
Can I execute another SQL statement inside the Do Until Loop? Can I use the same cnn1 connection?

Thanks!

 

[newfrontiers]

sjh,

MS is phasing out DAO in favor of ADODB. As for executing another SQL statement sure. When I need to do this and I cannot use the available strSQL I just create another variable (usually strSQL2, strSQL3, ...) and use the existing connection created in cnn1. I do not think there is a limit on the number of recordsets you can open and you should even be able to open recordsets from the same table at the same time.

Let me know if this helps.



Mediocrity is the death of a species