Query in datasheet view yields different results than opening it as a recordset

[Jason Seaver]

Really peculiar problem - I've got a module that outputs a web page
based upon the results of a query, but the recordset I'm using is
returning some bad values. The procedure is pretty straightforward:

Rs.Open "[Placement Count All Sorted]",
CurrentProject.AccessConnection
Rs.MoveFirst

Do Until Rs.EOF
Placements(i, 1) = Nz(Rs!People)
Placements(i, 2) = Nz(Rs!Jobs)

.... but when I step through it, the values for Rs!People and what's
indicated when I open the query up in Datasheet view is different, and
the data is not that dynamic. This doesn't happen on every record,
but it does on approximately 340 out of 4900.

Admittedly, the query in question is complicated - it references other
queries about four layers deep - but I'm completely at a loss to why
the same query should come up with two different results.

 

[ChrisJ]

As a first test, try converting the query to a maketable
query, then run your procedure on the new table to see if
you get the same problem

 

[Jason Seaver]

As a first test, try converting the query to a maketable
query, then run your procedure on the new table to see if
you get the same problem

Okay, that at least seems to be returning the right data now. Is it
just something about opening a complex query in a recordset?

-----Original Message-----
Really peculiar problem - I've got a module that outputs a web page
based upon the results of a query, but the recordset I'm using is
returning some bad values. The procedure is pretty straightforward:

Rs.Open "[Placement Count All Sorted]",
CurrentProject.AccessConnection
Rs.MoveFirst

Do Until Rs.EOF
Placements(i, 1) = Nz(Rs!People)
Placements(i, 2) = Nz(Rs!Jobs)

.... but when I step through it, the values for Rs!People and what's
indicated when I open the query up in Datasheet view is different, and
the data is not that dynamic. This doesn't happen on every record,
but it does on approximately 340 out of 4900.

Admittedly, the query in question is complicated - it references other
queries about four layers deep - but I'm completely at a loss to why
the same query should come up with two different results.
.

 

[fuzzyocelot]

Hi,

It sounds like a friend of mine is having a similar problem. When he runs a simple select query on one table, it returns a certain result set. However, when he changes it to a make-table query it returns a different result set. The select query seems to show different data each time he runs it.

We know it's not the database table (SQL Server 2000) because I can run the same query just fine on my PC. Meaning, it consistently returns the same result set as a select query and as a make-table query. We both have Access 2002 as well and are both running Windows XP.

Have you run across a possible solution?

Thanks,
Rebecca

As a first test, try converting the query to a maketable
query, then run your procedure on the new table to see if
you get the same problem

Okay, that at least seems to be returning the right data now. Is it
just something about opening a complex query in a recordset?

-----Original Message-----
Really peculiar problem - I've got a module that outputs a web page
based upon the results of a query, but the recordset I'm using is
returning some bad values. The procedure is pretty straightforward:

Rs.Open "[Placement Count All Sorted]",
CurrentProject.AccessConnection
Rs.MoveFirst

Do Until Rs.EOF
Placements(i, 1) = Nz(Rs!People)
Placements(i, 2) = Nz(Rs!Jobs)

.... but when I step through it, the values for Rs!People and what's
indicated when I open the query up in Datasheet view is different, and
the data is not that dynamic. This doesn't happen on every record,
but it does on approximately 340 out of 4900.

Admittedly, the query in question is complicated - it references other
queries about four layers deep - but I'm completely at a loss to why
the same query should come up with two different results.
.