I'm not an Access expert, but am an SQL expert. I inherited an Access front-end referencing a SQL 2005 database that worked OK for about 5000 records, but is failing miserably for 800k records...
Behind the scenes in the SQL profiler & activity manager I see some kind of Access query like:
SELECT "MS1"."id" FROM "dbo"."customer" "MS1" ORDER BY "MS1"."id"
The MS prefix doesn't appear in any Access code I can see. I'm suspicious of the built-in Access navigation code:
DoCmd.GoToRecord , , acNext
The GoToRecord has AcRecord constant, which includes things like acFirst, acLast, acNext, acPrevious and acGoTo.
What does it mean in a database context to move to the "next" record? This particular table uses an identity column as the PK, so is it internally grabbing all the IDs and then moving to the one that is the next highest???
If so, how would it work if a table was comprised of three different fields for the PK?
Or am I on the wrong track, and something else in Access is calling that statement? Unfortunately I see a ton of prepared statements in the profiler.
THanks!
