MS Access auto-incrementing primary key

[ballisticnylon]

I'm up a certain creek without a certain paddle, and I'm hoping someone can help. Here's the situation:

 

I've built a relational database in Access, where there's a one-to-many relationship between two tables. I have a table, and it's primary key is auto incrementing (meaning the database is supposed to assign a unique value for the primary key for each row when a new row is added). The only problem is that when I build my dataset (the one I'll be adding rows to), I'm only pulling certain rows from the database. Which is fine until I want to add a new row. As far as I can tell, the only way to get the assigned value for my new row's primary key is to get the value back from the function that adds all the column values in one line:

 

Dim thePrimaryKeyOfTheRowIJustAdded as Integer = MyDataSet.MyTable.AddMyTableRow(a, b, c, d, e)

 

...which returns the sought-after primary key of the row I just added. But the problem is this:

 

The dataset isn't populated with every record, but with records specified by a WHERE clause. So not every record that was in the original database is in the dataset that I created. It appears that a value for the primary key is being assigned that is NOT unique to the database that my dataset was built from. In other words, buh-bye data integrity. Do I actually have to build a dataset that contains every record so that when I add a row, the primary key will be unique? Or am I missing something? Please tell me I'm missing something!

[hammerman]

Let ADO.net handle it

 

what you want to do is create a strong typed dataset based on your access table. Create an autonumber column. Insert,update,edit your records in the datatable - then when you are finished call the update on the dataset and it will handle assigning proper PK in your access table and it will pass and update your datatable with the key value assigned by access.

[bri189a]

Yes I have that problem too... it's an Access design flaw, rather they meant it to be that way. Even though it's a terrible idea I clear the table and then call Fill again to get the properly assinged ID's for using in other WHERE clauses after submitting additions.

[pendragon]

If you have Access 2000 or Later and version 4 of the JET OLE DB provider then you should be able to use SELECT @@IDENTITY with the dataadapers objects RowUpdate event.

 

This wil return the last Auto number generated.

 

If you search for "Retrieving Identity or Autonumber Values" in the help it will give you an example