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

First thing to do is initialize all global variables before further investigation. — Tarik
– Tarik, Commented Sep 24, 2013 at 6:01
Wow, thanks! Here I thought my variables were all declared the same - I guess I didn't read far enough in the manual. Okay, I've properly declared all my variables - even used STATIC for some of the non-changing ones and I still get varried results. I've updated the code using the same link as above. — David R. Mohr
– David R. Mohr, Commented Sep 25, 2013 at 20:33

Tarik · Accepted Answer · 2013-09-26 16:49:59Z

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.

edited Sep 26, 2013 at 16:49

answered Sep 26, 2013 at 7:37

Tarik

11.2k2 gold badges31 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Christian Specht Over a year ago

+1 That's the right answer! The tables in any relational database are not guaranteed to be sorted automatically, so you MUST use an ORDER BY clause to get predictable results. If you load data without explicit ordering, the database returns the data in a random order which might look sensible and even may be the same on consecutive calls, but you don't know whether it will be ordered the same way after the next call.

David R. Mohr Over a year ago

Thank you for the great suggestions. I forgot to mention that my table is explicitly sorted via an ORDER BY statement as it is grabs the data from another table first. I will do some research on the RECORDSET CLONE method and give that a try and post results when complete. Thank you both for your help with this!

David R. Mohr Over a year ago

Still a no-go. I've updated the code and spreadsheets at the locations above so you can see the results. I ran the code several times and many times got the same results and many times received different results (first occurance can be seen on line 835 of sheet). UGG! I have added the query that makes the table just in case it's helpful: MakeTable1 Thanks again!

David R. Mohr Over a year ago

Still having the same problem, but have narrowed it down. The problem is that the ORDER BY clause only works most of the time when I run the MAKE TABLE query:

SELECT t_BillableTripsSort1.* INTO t_BillableTripsSort2 FROM t_BillableTripsSort1 ORDER BY Tripdate, RouteId, ADtime, APtime, DropHouseNumber, DropAddress1, PickHouseNumber, PickAddress1;

I have consistently been able to run the query and get a correctly sorted table as well as, while using the same code, getting an incorrectly sorted table. When I try ALTER TABLE tab1 ORDER BY field1 ACCESS gives me a syntax error.

Niall C. · Accepted Answer · 2013-12-06 21:42:31Z

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.

Collectives™ on Stack Overflow

MS Access/VBA Same code same data different results

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related