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

  • Thread starter Thread starter Jason Seaver
  • Start date Start date
J

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.
 
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
 
ChrisJ said:
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
Click to expand...

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.
.
Click to expand...
Click to expand...
 
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


Jason Seaver said:
ChrisJ said:
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
Click to expand...

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.
.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top