MS Access/VBA Same code same data different results

Question

This one has got me stumped but good. Using VBA in MS Access, I sometimes get different results when running the same code against the same table. I can run the code 2, 5, 6, 10 times and get the same results, then run it again and get a different result. I can run the code twice and get the same results and then I can run the code twice and get different results - all with the same code against the same table.

The code is used to group trips so they can be billed correctly. I do this by taking the raw SQL data and putting it into an Access table, then via several sorts and some cross-checking, I label each trip in the access table with a GR or an ML in the last field of the table. The result set is all trips for the specified time frame which are now labeled: ML (multi-loaded), GR (Grouped) or blank (demand).

I have even tried putting in MoveLast/MoveFirst to make sure the table is fully loaded each time (per suggestion from others).

Here is a link to the code and data after 2 runs of the same code on the same data: Code&Data

I removed the trip ID and client ID data for privacy concerns. The trip ID is unique but the client id will be used many times depending on how many trips the client took during the time period.

Any and all help you can give to make this code produce the same results each time it is run is GREATLY appreciated. I don't want to have to go back to doing this report labeling by hand. This is the smallest of 4 that must be done twice a month.

Thanks!
David R. Mohr
.................................................end of line........................................................................

Answer 1

First of all, Thanks to everyone that gave an answer. Your prompts guided me to find the solution.

1st problem is that I was directly editing the TABLE while being under the impression that my MAKE TABLE ORDER BY command was actually creating a table in the order I specified - it only worked most of the time and we can't have that.

So, after digging deeper I found more and more evidence that trying to sort the actual table - especially with a MAKE TABLE command is not good practice and can give unpredictable results as well as generates a lot more overhead. I am now basing my positioning and updating on a QUERY of the table and not the actual table. I.E. changed this:

Set InTable = dbsBilling.OpenRecordset("t_BillableTrips", dbOpenTable)
Set InTable2 = dbsBilling.OpenRecordset("t_BillableTrips", dbOpenTable)
Set InTable3 = dbsBilling.OpenRecordset("t_BillableTrips", dbOpenTable)
Set InTable4 = dbsBilling.OpenRecordset("t_BillableTrips", dbOpenTable)

to this:

Set InTable = dbsBilling.OpenRecordset("q_BillableTripsSort1B", dbOpenDynaset)
Set InTable2 = dbsBilling.OpenRecordset("q_BillableTripsSort1B", dbOpenDynaset)
Set InTable3 = dbsBilling.OpenRecordset("q_BillableTripsSort1B", dbOpenDynaset)
Set InTable4 = dbsBilling.OpenRecordset("q_BillableTripsSort1B", dbOpenDynaset)

So far, this seems to have fixed the problem and, of course, the proc runs much faster since it does not have to create the table twice to run/update for two different sorts.

Answer 2

When opening t_BillableTrips, I do not think it is safe to assume that the data will be sorted the way you want. That could potentially change from run to run. I would suggest to use a query with an explicit sort order instead of opening the table directly. My second suggestion is to use the Recordset Clone method to get Intable2 and Intable3. The recordsets will be sharing the same underlying in memory data but will be able to be positioned at different records.