Dataset returning different results

[Juan23]

Hello all,
Its been a few years since I've worked in Access (due to a job change), but I'm back in the saddle. My how things have changed! I'm currently using Access 7 with mixed feelings.

Anyway, to the problem as hand that really has me stumped.

I have a module that runs a ADOBD.recordset with the following SQL:

strSQL = "SELECT T1.ID, T1.S_ID, T2.Amount, T2.Delivery, T2.StartDate, T2.EndDate FROM T2 INNER JOIN T1 ON T2.S_ID = T1.S_ID WHERE T1.Active = -1;"

The result runs through a LOOP routine, evaluates the dates against some parameter dates, and runs an INSERT statement into another table.

If I cut and paste this same SQL into a QUERY and run it, I get a different result set.



The QUERY returns the correct results and the SQL in the module does not.

Any thought/suggestions? For what its worth, I used to run this routine in Access 2003 without any problems. I've tried a repair/compact on the database but no change.

[boblarson]

You need to add an ORDER BY clause because data in Access tables are not stored in any particular order and just selecting the data in various ways can return data in a different order than in the other. So, apply an ORDER BY to a field which would make sense to order it by, in both places, and it should then be consistent.

And just an FYI - when you look at a table (open a table) you are not really looking at the table. You are looking at a system VIEW (or query) that is generated by Access. Any sorting in the table properties only pertains to that particular view and does not extend to any use of it outside of that system view.

[Juan23]

Thanks Bob,
I actually have an ORDER By clause in my code (just forgot to post it). However, my issue was not with the order of the data but the difference in the individual records returned from running a simple query from the query builder versus running the query in code through an ADODB.Recordset object.

my code snippet for the recordset is:

Set rs = New ADODB.Recordset
rs.Open strSQL, CurrentProject.Connection, adOpenDynamic
With rs
Do
... a bunch of logic
Loop
End with

Shouldn't the data be the same regardless of how its being viewed?

[boblarson]

If the SQL is exactly the same, including the order by, then I would expect it to look the same.

[Juan23]

I tried a cut and paste from the query sql window (including all the parenthesis).

Then a cut and paste from code to the sql window (and stripped off all the quote and ampersands).

Alas, still had some differing results.

To prove my theory, I printed a query result set, and stepped through the code as it evaluated the data. Sure enough, a few records were completely missed in the code walk through. The data is very simple, but I double checked everything anyway. No nulls were seen to interfere with the select statement. The join is very simple and only one WHERE clause. Should I use a SELECT DISTINCT in code versus just a SELECT?

[boblarson]

It would be interesting to get a copy of the database (if the data is not sensitive or proprietary) and take a look myself.

I can't tell you at this point what you should do.

[boblarson]

Okay, I've seen your database, code and query and it would appear that you didn't pick up on my statement in post #4:

If the SQL is exactly the same, including the order by...

Since you had NO Order By clause, you cannot expect the results you expect. You may not realize this, but Access does NOT store data in the tables in any particular order. It may order them by PK when compacted but it does not care what it is that is there or where it goes. So, in order to get consistent results, you MUST apply an ORDER to the query (and for a report in the Sorting and Grouping because query sorts don't follow into the report).

So, I would suggest changing your query to look more like this:

strSQL = "SELECT WasteProfile.WPID, ScheduleMaster.Amount, ScheduleMaster.Delivery, ScheduleProfiles.ScheduleID, " _
& "ScheduleMaster.StartDate, ScheduleMaster.EndDate FROM WasteProfile INNER JOIN " _
& "(ScheduleMaster INNER JOIN ScheduleProfiles ON ScheduleMaster.ScheduleID = ScheduleProfiles.ScheduleID) " _
& "ON WasteProfile.WPID = ScheduleProfiles.WPID WHERE (((ScheduleMaster.Active)=-1)) ORDER BY WasteProfile.WPID, ScheduleMaster.StartDate, ScheduleMaster.EndDate;"

And then check to see if it is working correctly.

[boblarson]

Thanks Bob,
I actually have an ORDER By clause in my code

And actually you didn't.

[Juan23]

And actually you didn't.

Hmmm, you're right there, I just looked at the copy I sent you. It must have been one of my testing copies. My production version has an ORDER BY clause. My mistake.

However, I'm not too concerned about the order the records get evaluated in my code, it just made it simpler for viewing the resulting table that was created. All the sorting and grouping is done in the reports. I'm still a bit confused on why some records that should be there are getting missed.

The SQL statement you posted made me think of some query optimization and data normalization that I could probably do. So I'm working on that and see if I can resolve the problem.

Thanks again for taking the time to look at it.