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?
This question is related to
ms-access
vba
ms-access-2010
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
I know some things have changed in AC 2010. However, the old-fashioned ADODB is, as far as I know, the best way to go in VBA. An Example:
Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim rs As ADODB.Recordset
Dim colReturn As New Collection
Dim SQL As String
SQL = _
"SELECT c.ClientID, c.LastName, c.FirstName, c.MI, c.DOB, c.SSN, " & _
"c.RaceID, c.EthnicityID, c.GenderID, c.Deleted, c.RecordDate " & _
"FROM tblClient AS c " & _
"WHERE c.ClientID = @ClientID"
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
Set prm = .CreateParameter("@ClientID", adInteger, adParamInput, , mlngClientID)
.Parameters.Append prm
End With
Set rs = cmd.Execute
With rs
If Not .EOF Then
Do Until .EOF
mstrLastName = Nz(!LastName, "")
mstrFirstName = Nz(!FirstName, "")
mstrMI = Nz(!MI, "")
mdDOB = !DOB
mstrSSN = Nz(!SSN, "")
mlngRaceID = Nz(!RaceID, -1)
mlngEthnicityID = Nz(!EthnicityID, -1)
mlngGenderID = Nz(!GenderID, -1)
mbooDeleted = Deleted
mdRecordDate = Nz(!RecordDate, "")
.MoveNext
Loop
End If
.Close
End With
cn.Close
Set rs = Nothing
Set cn = Nothing
Source: Stackoverflow.com