.MoveLast does not work properly in ADODB recordset

[R_Ewing]

I have a table to which I am appending records using an ADODB recordset. The
primary key is a long integer which I increment each time I append a record.
I open the recordset referencing the table and issue a .MoveLast, then obtain
the primary key, increment, and store with the new record. This has worked
well until I appended the 222th record. .MoveLast points to the 220th record
and so I end up with an error for a redundant primary key. This happens
independently of the values I chose for the primary key and for the actual
length of the table. .MoveLast just does not see records beyond the 220th. I
am using a program I developed using Access 2002 with Access 2000 file
structure running under Access 2003. Any help will be appreciated. I am
stuck for things to try.

 

[Douglas J. Steele]

Rather than relying on MoveLast, try using SELECT MAX(Id) FROM MyTable.

 

[R_Ewing]

Thank you for your suggestion. I made a number of attempts at implementing
it but could not: 1) set a variable equal to the SELECT value, or 2) open a
recordset using your suggested SELECT procedure. I would like more
information on how to use SQL SELECT procedure to get the results I want.
However, on looking at why my use of MOVELAST was not working I found that I
needed to preceed it with SORT. For some reason appended records are not
getting written in order of ascending primary key value. Thanks again for
your help.

 

[Douglas J. Steele]

Well, it may make more sense if you alias the field:

SELECT MAX(Id) AS MaxValue FROM MyTable

Then, once you've opened the recordset, you can refer to the MaxValue field
to get the value. Otherwise, you could simply refer to rs.Fields(0)