ya we have a table in ms-access and we have assigned autonumber to the field srno.we have created a form in VB and there is a command button called delete now if we click on the button a record from MS-Access gets deleted but now since we have assigned autonumber now suppose if we have records srno say 1,2,3,4,5 and i delete srno=3 then in the ms-access table it should show me 1,2,4,5 it should show me 1,2,3,4, right how can we do that is there any code can somebody help me in coding tooooo the records should get adjusted automatically..
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
deleting records through VB in ms-access table
- Thread starter anuradha5
- Start date
You need to either loop though the records created from a recordset, ordered by the Auto Increment field in Acesnding order, and starting from the lowested number, change the value of that field to a counter value which increases in each loop,
or,
or,
This is simply an inappropriate use of the autonumber datatype. The purpose of autonumber is to supply a unique value (without re-use) for use as a row's key.
To do what you want, use an integer datatype for the numeric column and manage the values yourself in code; i.e., write code to renumber the index column when you delete a row.
To do what you want, use an integer datatype for the numeric column and manage the values yourself in code; i.e., write code to renumber the index column when you delete a row.
>This is simply an inappropriate use of the autonumber datatype
Even though I do not care for using an Auto Increment field, I would say that it is not inappropiate to use it that way, but any way suitable, just as any other tool.
Re-numbering it still keeps it's uniqueness, as long as there is no other user accessing the table at the time.
If someone wants 100% uniqueness with such a field, with respect to current and old deleted data, then re-numbering is of course not an option.
But again, it depends on what it is used for and what relation it should have with deleted data.
>and manage the values yourself in code
Increasing the value in the Auto Increment field is done by the dbms, so that, may be a more advantage than using a basic number field in order, to avoid conflicts (try doing what an Auto Increment fields manually, with multiple users accessing the same table and you will sooner or later run into conflicts).
>>This is simply an inappropriate use of the autonumber datatype
>...I would say that it is not inappropiate to use it that way, but any way suitable, just as any other tool.
From MS Access Online Help, the definition of the AutoNumber datatype:
"A unique sequential (incremented by 1) number or random number assigned by
Microsoft Access whenever a new record is added to a table. AutoNumber fields
can't be updated." (Right you are, Rick.)
I stand by my original statement above.
>>and manage the values yourself in code
>Increasing the value in the Auto Increment field is done by the dbms, so that, [blah, blah, blah]
CCLINT, aside from clarity of language, how does my suggestion differ from your
original post? Did you read what you yourself wrote, or are you simply on a
mission to exhibit hubris by derogating my post?
>...I would say that it is not inappropiate to use it that way, but any way suitable, just as any other tool.
From MS Access Online Help, the definition of the AutoNumber datatype:
"A unique sequential (incremented by 1) number or random number assigned by
Microsoft Access whenever a new record is added to a table. AutoNumber fields
can't be updated." (Right you are, Rick.)
I stand by my original statement above.
>>and manage the values yourself in code
>Increasing the value in the Auto Increment field is done by the dbms, so that, [blah, blah, blah]
CCLINT, aside from clarity of language, how does my suggestion differ from your
original post? Did you read what you yourself wrote, or are you simply on a
mission to exhibit hubris by derogating my post?
- Status
