VB structure and looping through records (1 Viewer)

[alcifer_c]

Good day,

Need some help in structuring a VB function that will loop through records using the specific value of one field in each record as the criteria in a seperate recordset.

I have created a SQL statement that gets the records that I need via an ODBC link (for the sake of example, lets say it contains a list of various groceries and their properties). Once the records are obtained, I would like to save the recordset in a temporary table for the duration of the operation in order to minimize ODBC calls.

A resident table contains a specific field value which will be used as a criteria to generate a recordset which I will individualy export to an excel spreadsheet (as example the criteria field value might be meats, veggies etc).

In the end, I would end up with one excel spreadsheet with all meats, another with all veggies etc.

Where I'm stuck is how to structure the VB code so that it obtains the ODBC linked data only once and loops through each criteria.

I can't get my head around opening two recordsets and evaluating one based on another.

There are approximately 120000 records to be evaluated approximately 500 times so performance is definitely and issue as well.

Many thanks for any assistance,
Al

 

[WayneRyan]

Al,

I hope it's something like this:

Dim dbs As DAO.Databse
Dim rstODBC As DAO.Recordset
Dim rstLocal As DAO.Recordset
Dim rstNew As DAO.Recordset
Dim sql As String

Set dbs = CurrentDb

Set rstLocal = dbs.OpenRecordSet("LocalTable")
Set rstNew = dbs.OpenRecordSet("NewTable")

While Not rstLocal.EOF And Not rstLocal.BOF
   sql = "Select * " & _
         "From   ODBCTable " & _
         "Where  ProductType = '" & rstLocal!ProductType & "'"
   Set rstODBC = dbs.OpenRecordset(sql)
   While Not rstODBC.EOF And Not rstODBC.BOF
      rstNew.AddNew
      rstNew!ProductType = rstLocalProductType
      rstNew!SomeField = rstODBC!SomeField
      rstNew.Update
      rstODBC.MoveNext
      Wend
      rstLocal.MoveNext
   Wend

Wayne

 

[alcifer_c]

Thanks Wayne,
I've been trying something similar, but using ADO not DAO.
I seem to be having problems with the criteria string. I'll be trying again tonight with your reasonable suggestion and let you know how it goes. If it works I will be very happy as it's been bugging me all week.

Many thanks, Al