How do I put the results of my query into a recordset?

[OhioSteve]

I know how to use docmd.runsql to run an sql. That's fine if it is an insert, update, or delete statement. But now I want to run a select sql statement from within a VBA function. I want the results to go into a recordset.

How do I declare the recordset, instantiate it, and fill it with the results of my query?

 

[TheAceMan1]

How are ya OhioSteve . . .

Have a look at [blue]QueryDef[/blue] and the [blue]QueryDefs Collection[/blue] . . .

See Ya! . . . . . .

 

[JerryKlmns]

Dim rst As ADODB.Recordset ' Or DAO.Recordset

Set rst = New ADODB.Recordset 
'Many ways, one of which
With rst 
  .ActiveConnection = myCnxn 'An already open connection to a data source
  .CursorLocation = adUseServer
  .CursorType = adOpenStatic
  .LockType = adLockReadOnly
  .Source = "YourSelectQuery" ' Or a valid sql SELECT statement
  .Open
   If Not (.EOF And .BOF) Then
         MsgBox "Records Found"
         Do Until .EOF
            Debug.Print .Fields(0), .Fields("ID") 'Ordinal position, or a field name
           .MoveNext
         Loop
   Else
         MsgBox "No Records Found"
   End If
   .Close
End With
Set rst = Nothing

 

[webturner]

Hey OhioSteve, you don't say what's in this select statement of yours. Does it have any parameters? Is it being run against an SQL server?

It could be as simple as:

Dim rst As New ADODB.Recordset
Dim strSQL As String

strSQL = "SELECT strForename FROM tblPerson;"
rst.Open strSQL, CurrentProject.Connection

 

[abaldwin]

In Steves suggestion he has a line

.Source = "YourSelectQuery" ' Or a valid sql SELECT statement

YOURSELECTQUERY is the name of a query in his database. YOu can replace it with a valid sQL select statement.



Andy Baldwin

"Testing is the most overlooked programming language on the books!"

Ask a great question, get a great answer. Ask a vague question, get a vague answer.
Find out how to get great answers FAQ219-2884.

 

[OhioSteve]

webturner, I assume "currentProject.connection" needs to be declared and instantiated beforehand. And it needs a valid connection string.

Actually, I already knew how to do it using a connection string. It just seems odd to me that the DB needs to connect to itself. Particularly since they give us a way (docmd.runsql) to skip those steps with insert, update, and delete statements.

 

[webturner]

No, it's built in, you can just use it.

 

[Golom]

[blue]I assume "currentProject.connection" needs to be declared and instantiated beforehand[/blue]
If Access is running ADO then "currentProject.connection" is instantiated when Access starts (i.e. it is a property of the running instance of Access.) You don't have to do anything to create it.

[blue]It just seems odd to me that the DB needs to connect to itself.[/blue]
It isn't really ... it is connecting ADO to the JET tables in the database.

[blue]... insert, update, and delete ...[/blue]
Those are ways to modify data in a table and yes ... you can also do that with a recordset. What you cannot do with insert, update, and delete is examine the data in the database and that's what recordsets are used for.

 

[JerryKlmns]

Andy,

my name is Gerassimos while Jerry is my codename

------------------
OhioSteve,

Dim myCnxn As ADODB.Connection
Set myCnxn = CurrentProject.Connection
...
Or

Dim myCnxn As ADODB.Connection
Set myCnxn =New ADODB.Connection
myCnxn.ConnectionString = you know what goes here
myCnxn.Open
...

------------------
Golom

 

[OhioSteve]

Thanks for everyone's input!