Need Help : Insert record and adjust number sequence

[morbid.ivan]

Series -- Name
E-001---- John
E-002---- Phil
E-003---- Bill
------- > Going to insert record here (I need this to become E-004)
E-004---- Ron
E-005---- Han
E-006---- Joanne
E-007---- Millet

Result I am looking for :

Series -- Name
E-001---- John
E-002---- Phil
E-003---- Bill
E-004---- Paul ------- > Newly inserted
E-005---- Ron
E-006---- Han
E-007---- Joanne
E-008---- Millet

I need the rest to adjust.

Code :

Dim rsInsert As ADODB.Recordset
Dim str_Insert As String
    
    str_Insert = "INSERT INTO TBL_TRANSACTION (TRANSACTION_NO,FULLNAME) " _
            & "VALUES('E-004','Paul')"
    
    Set rsInsert = New ADODB.Recordset
        rsInsert.CursorLocation = adUseClient
        rsInsert.CursorType = adOpenForwardOnly
        rsInsert.LockType = adLockOptimistic
        
        rsInsert.Open str_Insert, connSavings
            
            
    Set rsInsert = Nothing

so far, this is what I have.

Any help?

[Arnoutdv]

That's some strange request.
Adding a new record with an already existing TransactionNumber and then updating all other records to modify their TransactionNumber also.
What is the use for having a TransactionNumber at all??

[hamza.saleem]

Series -- Name
E-001---- John
E-002---- Phil
E-003---- Bill
------- > Going to insert record here (I need this to become E-004)
E-004---- Ron
E-005---- Han
E-006---- Joanne
E-007---- Millet

Result I am looking for :

Series -- Name
E-001---- John
E-002---- Phil
E-003---- Bill
E-004---- Paul ------- > Newly inserted
E-005---- Ron
E-006---- Han
E-007---- Joanne
E-008---- Millet

I need the rest to adjust.

Code :

Dim rsInsert As ADODB.Recordset
Dim str_Insert As String
    
    str_Insert = "INSERT INTO TBL_TRANSACTION (TRANSACTION_NO,FULLNAME) " _
            & "VALUES('E-004','Paul')"
    
    Set rsInsert = New ADODB.Recordset
        rsInsert.CursorLocation = adUseClient
        rsInsert.CursorType = adOpenForwardOnly
        rsInsert.LockType = adLockOptimistic
        
        rsInsert.Open str_Insert, connSavings
            
            
    Set rsInsert = Nothing

so far, this is what I have.

Any help?

Personally i dont think so that with simple insert query you can do this, it can be possible if you set the 'Series' to AutoNumber (in access). but with these conditions you are with.. I don't think so.

[morbid.ivan]

Personally i dont think so that with simple insert query you can do this, it can be possible if you set the 'Series' to AutoNumber (in access). but with these conditions you are with.. I don't think so.

Aside on Autonumber I also have another field to hold series number.

[hamza.saleem]

Aside on Autonumber I also have another field to hold series number.

Ok then the simplest answer for this conservation is that you cannot do such type of modification to all records at same time.

[techgnome]

If the names were alphabetical order, I could see how it might make sense and be doable. But the names aren't in alphabetical order so to be honest the resquest doesn't make any sense what so ever. The insertion point is random and arbitrary.

-tg

[Phill.W]

"I need the rest to adjust."
No, you absolutely don't.

Yes, every record should have a unique value but the actual value you use should not matter a fig!
It doesn't matter that they're not in order or, at least, it shouldn't matter. Certainly, your database won't care.

What if you have another file, somewhere else, that also has these values in it, but with (say) millions of rows. Are you seriously going to go through all of those and change them all instead of just inserting one new "record" with the next available number?
It's a huge performance hit (potentially millions of rows being updated instead of just one) and a massive risk of data corruption (what if those identifiers somehow "come to rest" linked to the wrong person ("my" transactions suddenly become "yours")?

Regards, Phill W.

[techgnome]

"I need the rest to adjust."
No, you absolutely don't.

I had to laugh when I read that... sounds a lot like some of the requirement meetings I sit in on. With an appropriate response.

Something else to keep in mind, you have no control over the actual insertion point of the record. It's going to put the record in the spot as dictated by the design of the table. It could be in the spot you've indicated, it may not be. It depends on what ever clustered index you have, or if there isn't one, the PKey Indexing, which is still likely to put it at the end. So that means to get it in the spot you want it, it's up to the sorting when you return the records. Since there doesn't appear to be a sort mechanism, the only option is the Sequence field... but it's not automatic, you'll need to insert & adjust, or adjust then insert... two steps. But it simply just is not going to happen magically on its own.

-tg

[hamza.saleem]

"I need the rest to adjust."
No, you absolutely don't.

Yes, every record should have a unique value but the actual value you use should not matter a fig!
It doesn't matter that they're not in order or, at least, it shouldn't matter. Certainly, your database won't care.

What if you have another file, somewhere else, that also has these values in it, but with (say) millions of rows. Are you seriously going to go through all of those and change them all instead of just inserting one new "record" with the next available number?
It's a huge performance hit (potentially millions of rows being updated instead of just one) and a massive risk of data corruption (what if those identifiers somehow "come to rest" linked to the wrong person ("my" transactions suddenly become "yours")?

Regards, Phill W.

I hope someday any banker made mistake and send all the money in an millionaire account into mine