MS Access AutoNumber

  • Thread starter Thread starter Graeme Richardson
  • Start date Start date
G

Graeme Richardson

Hi, I have an MS Access data base with an AutoNumber primary key
(uidMember).
I've generated a DataForm using the wizard [I'm at the learning stage]..
On creating a new record the uidMember field is populated with the next
greatest number for the field (say the current maximum is 3, the field will
show 4, irrespective of if the AutoNumber 4 has been deleted/abandoned).
On saving the record the table is populated with the next available
AutoNumber (say 5).
The form still shows 4. How do I get it to show 5? [Note, there are no
alternate keys in the table]
 
Use @IDENTITY.
OleDbCommand selectIdentityCommand;

selectIdentityCommand = new OleDbCommand();

selectIdentityCommand.Connection = connection;

selectIdentityCommand.CommandText = "SELECT @@IDENTITY";

Below is a fragment from microsoft ADO.NET ebook

Working with Access 2000

If you're working with an Access database, you can also use the SELECT @@IDENTITY query to retrieve new autoincrement values. This feature was added in version 4.0 of the Jet OLE DB provider and works only with databases formatted for Access databases version 2000 or later. Like its SQL Server counterpart, the SELECT @@IDENTITY query returns the last autoincrement value generated on your connection.

Access databases do not support output parameters on QueryDefs-stored queries that are similar to views and stored procedures. The Jet OLE DB provider does not support batch queries. So the only way to fetch newly generated autoincrement values is to use the DataAdapter object's RowUpdated event, as shown earlier in the chapter.
 
Back
Top