I have a query that I want to execute against a table. With the results I want to do something. In my head the pseudo code is:
var q = "select * from table where some condition";
var results = db.getResults(q);
foreach (row r in results )
do something with result
How would I so something similar with vba?
To see the query results, on the Design tab, click Run. Access displays the results of your query in Datasheet view. To make further changes to the query, click Home > View > Design View to switch back to Design view.
DAO is native to Access and by far the best for general use. ADO has its place, but it is unlikely that this is it.
Dim rs As DAO.Recordset
Dim db As Database
Dim strSQL as String
Set db=CurrentDB
strSQL = "select * from table where some condition"
Set rs = db.OpenRecordset(strSQL)
Do While Not rs.EOF
rs.Edit
rs!SomeField = "Abc"
rs!OtherField = 2
rs!ADate = Date()
rs.Update
rs.MoveNext
Loop
Ahh. Because I missed the point of you initial post, here is an example which also ITERATES. The first example did not. In this case, I retreive an ADODB recordset, then load the data into a collection, which is returned by the function to client code:
EDIT: Not sure what I screwed up in pasting the code, but the formatting is a little screwball. Sorry!
Public Function StatesCollection() As Collection
Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim colReturn As New Collection
Set colReturn = New Collection
Dim SQL As String
SQL = _
"SELECT tblState.State, tblState.StateName " & _
"FROM tblState"
Set cn = New ADODB.Connection
Set cmd = New ADODB.Command
With cn
.Provider = DataConnection.MyADOProvider
.ConnectionString = DataConnection.MyADOConnectionString
.Open
End With
With cmd
.CommandText = SQL
.ActiveConnection = cn
End With
Set rs = cmd.Execute
With rs
If Not .EOF Then
Do Until .EOF
colReturn.Add Nz(!State, "")
.MoveNext
Loop
End If
.Close
End With
cn.Close
Set rs = Nothing
Set cn = Nothing
Set StatesCollection = colReturn
End Function
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With