access sorting questions 2

[Jdoggers]

Hi,
I have a small problem. I would like to create a delete routine in visualbasic 6.0 that takes an access database that has the primary key as 'ID' which is an autoindex integer that becomes disorganized as records are taken out of the list. I would like to take the index number, and press a button that makes all the indexes sequential again.
example:

original database:

ID Name Phone etc....
1 joe 21342134 ...
4 sally 12313 ...
7 bob 23423 ....
11 jimbob 1123 ...

After Conversion button pressed:

ID Name Phone etc....
1 joe 21342134 ...
2 sally 12313 ...
3 bob 23423 ....
4 jimbob 1123 ...

i originally defined a recordset named 'myRS' but when i tried to go through a loop and change each index number, i got an error. I have a listbox that has a copy of all of the entries in the database. I use it to delete an entry in the database every time there is a click event in the listbox, then it is supposed to reorganize the index numbers every time an entry is deleted. The code that i used was:

for i = 1 to mylistbox.listcount - 1
myRS.edit
myRS!ID = i
myRS.update
next i


Can anyone help me?
thanks

 

[fredericofonseca]

DO NOT DO that everytime a record is deleted. Autonumbers should be reorganized only once in a while.

The easiest way to do that is to do as follows
1- Create a similar table to the one you are trying to reorganize, also with auto numbering.

2- copy all records from the original table into the new table
e.g. insert into my_new_table select * from my_old_table (where the autonumber is excluded)

3- Delete all the records on the original table
4- do step2 but from the new table into the original table.


Records will not be reorganized.






Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[Jdoggers]

another duplicate table...an interesting idea. The only reason that i need to reorganize the table is because i refer to the index number in the code so that the index number will be the same in the database as in the listbox, as to be easy code to write for the deletion. in order to copy one record from a table to another table, do i need a loop that goes from start to finish of table and copys the code from one to another, then deletes it..?
I was wondering what the code for copying from one table to another, then deleting the current record I was thinking
that i could write some kind of a loop. Any suggestions?

 

[MichaelRed]

Back to the beginning all over again?

WHY would your EVER NEED to Re-organize? The AutoNumber is not an integer, so you are not limited to 32K records - more like 2Billion+, so it isn't going to become a problem in the value sense. Wheather the sequence is contiguious or not shouldn't matter. If you want to use it (the AutoNumber field) as a reference / index from a combo or list box, just include it as a hidden column and access it as a column(N) property of the selected record.



MichaelRed

 

[Jdoggers]

Its not that i want everything to be sequential. I would like for the numbers whether the ID or another hidden column to be able to be indexed from visualbasic so that the whole list is always sequencial in this field. The problem arises when one entry is deleted from the middle of the list an then there is a missing index and the index numbers arent sequencial. Is there any way, maybe a loop, to go through every time there is a deletion to make the rest of the numbers sequencial again?

 

[fredericofonseca]

The solution here is to either use a control that allows for hidden columns, or for index/user defined indexes and you use those to hold your table key, or you use a standard listbox, and at the same time you use a array to hold the key. In this last case everytime you delete/add a entry on the listbox you go and perform the same operation on the array.

this way you always know which entry of the list box corresponds to which key on the DB table.

The sequential order is the listbox index, and whether the autonumber ir ordered does not matter.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com