How does Access 2007's moveNext/moveFirst/, etc., feature work?

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 prepa开发者_Python百科red statements in the profiler.

THanks!

First is literally the first row in the Recordset. In general, Access accesses data via the equivalent of cursors. So, Next and Previous are moving forward and backwards in the Recordset one row at a time just as you can with SQL Server's cursors. Be careful about depending on the sequence of the rows without an ORDER BY statement in the construction of the Recordset. Although Access is an ISAM, you should not rely on the rows coming in any particular order. Depending on the cursor type, Access will not pull the entire table down but will generally ask for one record at a time. That said, I have seen Access pull entire tables for whatever reason.

You have to distinguish between automating Access objects and working with recordsets in code.

In a form, this command has meaning:

  DoCmd.GoToRecord , , acNext

It is nonspecific, and it is not predictable what record it will go to unless you know the ordering of the underlying recordset in the form and the starting record. It navigates you through the recordset stored in the form's edit buffer (which is loaded in the OnOpen event of the form). The command would be used, for instance, in the code behind a command button whose purpose is to navigate records loaded into the form that currentlyl has the focus. I would never leave out the optional arguments if I were to use that command (I almost never do). Instead, I'd identify the specific form I wanted it to apply to:

  DoCmd.GoToRecord acForm, "MyForm", acNext

In traversing a DAO recordset, .MoveNext likewise has no predefined meaning except if you know the ordering and starting record. When you are walking a recordset (something you shouldn't do very often, since it's pretty inefficient; but it depends on the task you need to perform) and need to hit each record, you'd certainly call .MoveNext as part of your loop:

  With rs
    .MoveFirst ' technically not required, as it's the default starting point
    Do Until .EOF
      [do something]
      .MoveNext     
    Loop
  End With

Nothing mysterious there. It's most likely going to be used in code with small numbers of records (large recordsets really oughtn't be navigated sequentially).

In answer to your specific question:

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

...as I said, the next record is determined by the ordering of the recordset being traversed and the starting position. In the case of the form, it's the edit buffer that's being traversed, and as the current record bookmark changes in the edit buffer, the form is updated to load the data for that record. The dynaset is bound to the underlying data table, and when the form's edit buffer is saved, the edited data is written back to the server. While it's being edited, locks may or may not be maintained on the record on the server, but Access/Jet/ACE does keep track of the state of the existing record on the server and the record in the edit buffer and will inform you at save time in Access if the record on the server has been changed since it was loaded into the form's edit buffer.

Now, in a comment, you say the form is bound to the whole table. This is a terrible design no matter whether your data is stored in a Jet/ACE back end data file or in a server database like SQL Server. The only reason Access can get away with it is because it and Jet are rather efficient about pulling data from the data source.

I properly-designed client/server Access front end will not load full tables in forms, but instead ask what filtered recordset you want to load, either 1 or more records at a time. This is only marginally more complicated than binding to a whole table.

As to knowing what cursor types are being used, you shouldn't be worrying about it. By default, Access forms use what Access/Jet/ACE calls dynasets. Each form has a RecordsetType property, and it's set to dynaset by default (read the help file on the meaning of the different recordset types). If you want more control of that, you can (but likely shouldn't) create your recordsets in code and assign them to the form's .Recordset property. This is useful in a few circumstances, such as when you'd like to bind a form to a disconnected recordset, but the point of Access is leveraging its capabilities working with bound data. Assigning your own recordsets still gets you bound controls, and the form events, but is more work than is usually necessary.

Basically, change your forms to load only the subset of records the user needs to work with (that may be one record at a time), and then let everything else get done with Access's default behaviors. If something causes a bottleneck, then troubleshoot that and replace the default behavior with something more efficient.

In other words, avoid premature optimization -- let Access be Access.

And don't worry about what Access is doing behind the scenes unless/until Access does something inappropriate.