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 :

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?