Access 2010 VBA query a table and iterate through results

Question

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

User · Answer

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     amp         c RaceID  c EthnicityID  c GenderID  c Deleted  c RecordDate    amp         FROM tblClient AS c    amp         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

User · Answer

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    amp         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

User · Answer

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

[ms-access] Access 2010 VBA query a table and iterate through results

Examples related to ms-access

Examples related to vba

Examples related to ms-access-2010