Access Primary Key not in order

[magic_andrew]

I've got a section of my forms program that enables a user to delete records from an access database using a DataGridView and a button. In my database there have been 2 users which haven't been deleted from the start, however I have had multiple test data which I have deleted. My primary key has the format 00, with the first two records being 01 and 02 (they appear as 1 and 2 in the DataGrid for some reason) but afterwards the next record has primary key 50 for example. Anyone know how to fix it so that once a record is deleted, the primary key it had becomes available again?

[wes4dbt]

What data type is the primary key? If it's an Autonumber then the answer it no. Well, I believe there is a way but it envolves something like removing all the records and compact and repair the data, I remember checking into that a long time ago and realized it wasn't reasonable. If it's not an Autonumber then sure you can reuse the number.

[kebo]

Yea, you can use anything you want for a primary key so long as it's the right data type and is unique. If you have an auto increment primary, then I'm not sure what is to be gained by resetting though. If you want to though..
http://support.microsoft.com/kb/812718
http://stackoverflow.com/questions/4...able-in-access

[si_the_geek]

This is one of those things where the design is there to protect you.

If you re-use the numbers it can cause problems in a variety of ways, such as:

• if you have another table with related data that links to this one, and somehow you forget to delete the related records in the other table... now the replacement record you create has related data that it shouldn't have.
• if a user is editing a record but gets distracted, then somebody else comes along and deletes a record and adds a new one, then the original user finishes their edit... what happens? (bad data)
• if somebody writes/prints the record id and asks to have the record updated, the wrong one will now be updated.

Having new numbers instead avoids these kinds of issues.

On top of that, what do you think should happen if you have records 1,2,3,4,5, and somebody deletes record 3? There is no nice solution to it.


I assume that your reason for doing this is to keep things "pretty" on screen, in which case you should probably use an alternative method instead (such as putting the numbers directly into the DataGridView yourself, or not having them at all).

In general AutoNumber/Identity fields should only be used as a Unique reference, not some kind of sequence indicator.

[jmcilhinney]

I've got a section of my forms program that enables a user to delete records from an access database using a DataGridView and a button. In my database there have been 2 users which haven't been deleted from the start, however I have had multiple test data which I have deleted. My primary key has the format 00, with the first two records being 01 and 02 (they appear as 1 and 2 in the DataGrid for some reason) but afterwards the next record has primary key 50 for example. Anyone know how to fix it so that once a record is deleted, the primary key it had becomes available again?

Why should this be an issue at all? Who cares about test data? Your test data is completely irrelevant to the deployed application... unless you haven't done things properly.

When you want to use an Access database, you first create the database in Access in then add it to your project in VS. When you add the database to the project, you will be prompted to copy the database file into the project file. You do accept the offer and then ignore the original file you created. The database file in the project folder becomes your source file and if you want to make any schema changes you should make them to that source file.

When you run your project, the default behaviour is for the source database file gets copied into the output folder along with your EXE and for you to connect to that copy at run time. As a result, any test data you insert, update or delete will be in the output copy, NOT the source. You can recopy the source file to the output folder as often as you like, thus replacing the copy polluted with test data with a clean copy of the source, complete with all AutoNumber columns set to their initial seed values. When you're ready to deploy, you create a Release build instead of a Debug build and, again, you get a clean copy of the source file and that copy has no knowledge of any previous test data, let alone contain any of it.

[magic_andrew]

Thanks for all the replies. I realize now that it would cause problems. I'll just recreate the table when it comes to actually handing this in, thanks!

[Shaggy Hiker]

What's the goal? A primary key should be just that and nothing more. If you need some kind of sequential number that has no gaps in it, then add an integer field and refill it as necessary. Don't make it the key, though, leave that as it is. Expecting every slot in a primary key to be filled in order is putting a constraint on the field that is unreasonable and likely to cause trouble down the road. Let the key be the key and let the rest of the table meet whatever other requirements you have.

[techgnome]

I'll just recreate the table when it comes to actually handing this in, thanks!

Sounds like all the OP wanted was a "clean" db to hand in for the assignment.

-tg

[jmcilhinney]

Thanks for all the replies. I realize now that it would cause problems. I'll just recreate the table when it comes to actually handing this in, thanks!

What you should do is recreate the database now and then use it like I described. There will never be a need to recreate anything again after that.