Why is my recordset result accessed repeatedly and

Question

I have a DAO.Recordest called products which I assign like this

Set products = db.OpenRecordset("Product URLs for Sitemap")

"Product URLs for Sitemap" is a query which when ran makes use of a custom VBA function to populate one of it's columns.

What I am expecting to happen is that products will contain the contents of the query after it has ran, like a table. However this does not seem to be the case.

Once I have my products recordset I am then looping over it and creating some XML from it

Do While Not products.EOF
    Dim prdUrl As String
    Dim prdUpdated As String

    prdUrl = products!url
    prdUpdated = products!updated

    XML = XML & createUrlXml(products!url, products!updated)
    products.MoveNext
Loop

However during this loop it is calling the function used in "Product URLs for Sitemap" during each loop. This should only need to be done once - at the time that I populate products by calling Set products = db.OpenRecordset("Product URLs for Sitemap")

Why is this getting called every time I loop through the products recordset and how do I stop this?

Thanks

Answer 1

How to use GetRows(), simple example:

Dim queryText As String
queryText = "SELECT *, YourVBAFunc([URLs_SOURCE]) AS URL FROM Table1;"

Dim products As DAO.Recordset
Set products = CurrentDb.OpenRecordset(queryText)

products.MoveLast
products.MoveFirst

Dim data As Variant
data = products.GetRows(products.RecordCount)

' then all data are in the array 'data' ...

More information here: http://bytes.com/topic/access/insights/789969-retrieving-data-dao-recordset-using-getrows

Answer 2

I would suggest turning the query "Product URLs for Sitemap" into a table, which will then force the execution of the function for all rows at that time. So:

SELECT columnA, columnB ... yourFunction(args) 
INTO newTableName
FROM tableSpecification

Then your loop will not execute the function each iteration.

If this information is going to change (as I'm guessing) drop it after you're done with it, and recreate next time this logic needs to run.

If the data needs to be user- or session- specific, you can generate a random number for the tablename using the timer:

newTableName = "myTableName" + replace(Timer,".","")

Hope this suggestion helps.