Questions regarding deleting and indexing an access database online.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a large database that is primary keyed by a field that contains a unique serialized number. I do not use autonumber because if a new record add is aborted via the web I end up with a blank record with an assigned ID. So, when I add a record I do a count(*) and increment the count by one for the new record.

Now for the question..

If I delete a record in the middle of the database I leave a hole in the primary key count. I need to be able to have sql verify that the numbers in this field are indeed in order and if they are not in order I need to correct the field from the deleted record to the end of the database. Or, I need a way to make sql locate the hole and add my next inserted record in the missing records location. I would prefer to do the first and always add to the end of the database, while maintaining an accurate serialized ID_Num field.

Any help would be greatly appreciated. If possible send email

Thanks Matt
 
Instead of doing Count(*) you should do Max(YourPrimaryKeyField) + 1

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Security FAQ: http://support.microsoft.com/support/access/content/secfaq.asp



Matt said:
I have a large database that is primary keyed by a field that contains a
Click to expand...
unique serialized number. I do not use autonumber because if a new record
add is aborted via the web I end up with a blank record with an assigned ID.
So, when I add a record I do a count(*) and increment the count by one for
the new record.
Now for the question...

If I delete a record in the middle of the database I leave a hole in the
Click to expand...
primary key count. I need to be able to have sql verify that the numbers in
this field are indeed in order and if they are not in order I need to
correct the field from the deleted record to the end of the database. Or, I
need a way to make sql locate the hole and add my next inserted record in
the missing records location. I would prefer to do the first and always add
to the end of the database, while maintaining an accurate serialized ID_Num
field.
 
Back
Top