VB6, ACCESS 2002-2003, sometimes AUTONUMBER field is not generated

Question

Good morning. I developed an application 20 years ago using VB6 and Access 2002 2003. I have a table with an AUTONUMBER field and the following statements have always returned the value of the AUTONUMBER field (Progressivo_Tagliando).

Dim adoTaglInserimentoRS As Recordset
StmtInsert = "select * from Plico where Progressivo_Tagliando=0" 'No record have Progressivo_Tagliando=0, only to open RecordSet
adoTaglInserimentoRS.Open StmtInsert, db, adOpenStatic, adLockOptimistic, adCmdText

VarCampiTagliando = Array("Nota", "Progressivo_Distinta", "Data_Stato_Plico", "Progressivo_Destinatario", "Progressivo_Corriere", "Contenuto_Plico", "Rifpr_Numsin", "Progressivo_Tramite", "Stato_Plico", "Username_Inserimento", "Data_Inserimento")

VarValoriTagliando = Array(adoTagliandoRSNote, adoTagliandoRSProgressivo_Distinta, adoTagliandoRSData_Stato_Plico, adoTagliandoRSProgressivo_Destinatario, adoTagliandoRSProgressivo_Corriere, adoTagliandoRSContenuto_Plico, adoTagliandoRSRifPr_NumSin, adoTagliandoRSProgressivo_Tramite, adoTagliandoRSStato_Plico, adoTagliandoRSUsername_Inserimento, adoTagliandoRSData_Inserimento)

db.BeginTrans
adoTaglInserimentoRS.AddNew VarCampiTagliando, VarValoriTagliando

txtPrg_Tagliando.Text = adoTaglInserimentoRS!Progressivo_Tagliando

adoTaglInserimentoRS.Close

db.CommitTrans

For a few days the AUTONUMBER value is sometimes generated and sometimes returns NULL and the instruction

txtPrg_Tagliando.Text = adoTaglInserimentoRS!Progressivo_Tagliando`

generates error.

I also tried using an INSERT instead of the RecordSet, then using a SELECT to find the max(AUTONUMBER), but even so it sometimes fails.

Any suggestions? Thank you.

Answer 1

Like MS SQL, MS Access also supports the system function @@IDENTITY.

So after an INSERT do something like

' Retrieve new ID
sTable = "TheInsertTable"
Set rs = New ADODB.Recordset
sSQL = "SELECT @@IDENTITY AS ID FROM " & sTable & ";"
Call rs.Open(sSQL, gobjApp.DBCon)
ID = rs.Fields("ID").Value
Call rs.Close
Set rs = Nothing

I never had a problem with it when using with an Access database (which also was Access 2003).