Create LAST ID

In any database table, record id is very much important.
In MS access, there is a facility of AUTO NUMBER.

There is a simple method. Get the maximum value of record id and then add one.
This method has some drawbacks. If the last record has recently been deleted and if this value is sitting in other tables, inconsistency  of database will occur.

so, the best way is maintain a separate table for keeping the last record id of  required tables.

the code is like this:

If Request("job")="addNew" Then

'******CREATE NEW RECORD ID**********

Set ObjRsId=Myconn.Execute("select LAST_ID from eetp_last_id_master where field_name='BATCH_ID'")
If ObjRsId.EOf Then
Myconn.Execute("insert into eetp_last_id_master(last_id,field_name)values(1,'BATCH_ID')")
new_batch_id=1
Else
last_id=cint(ObjRsId("last_id"))
new_batch_id=last_id +1
End If
Myconn.Execute("update eetp_last_id_master set last_id=" & new_batch_id & " where field_name='BATCH_ID'")
'******CREATE NEW RECORD ID**********

 So the new record id is ready and you can use that id as given below:

     set objRsAddNew=Server.CreateObject("ADODB.Recordset")
objRsAddNew.CursorLocation=3
If session("database_type")="ms access" then
sql="select top 1 * from batch_master "
Else
sql="select * from  batch_master where rownum=1"
End If
objRsAddNew.open sql,myconn,1,3
ObjRsAddNew.addNew


ObjRsAddNew("batch_id")=new_batch_id
         ........