sbelean
asked on
Last ID generated with INSERT for AutoNumber Key
How can I know the last ID generated in a Jet Table with AutoNumber field type key after INSERT from VB a new record?
Thank you, sbelean.
Thank you, sbelean.
IN ADODB+Tables:
YourTable.AddNew
YourTable.Update
ID = YourTable.Field(ID).Value
With ADODB+SQL:
Connection.BeginTrans
Connection.Execute (INSERT...)
ID = Connection.Execute (SELECT (MAX(ID) FROM ... ).Fields(0).Value
Connection.CommitTrans
YourTable.AddNew
YourTable.Update
ID = YourTable.Field(ID).Value
With ADODB+SQL:
Connection.BeginTrans
Connection.Execute (INSERT...)
ID = Connection.Execute (SELECT (MAX(ID) FROM ... ).Fields(0).Value
Connection.CommitTrans
ASKER
AzraSound and angelIII, both of you answered me, but if New Value for AutoNumber field is Random how can I know the last ID generated? The first who will answer to this will receive the points if you agree that is fair enough . Thank you, sbelean
sbelean.. AutoNumber by definition..is a sequentially numbered field.. ie.. 1.. 2.. 3.. 4.. 5.. etc.
The only hope you would have in retrieving the random number would be..
Select LAST(MyRandomNumber) From MyTable
then you will get the last record.
The only hope you would have in retrieving the random number would be..
Select LAST(MyRandomNumber) From MyTable
then you will get the last record.
wsh2: in access, you can have Autonumber which are generated randomly, so Max() won't work.
sbelean:
If you want to know the ID generated in every case, then you need to use the schema i mentioned already:
YourTable.AddNew
yourTable.Fields("ColumnNa me").value = FirstValue
YourTable.Update
ID = YourTable.Field(ID).Value
because with this method, the field value will be set after .Update()
sbelean:
If you want to know the ID generated in every case, then you need to use the schema i mentioned already:
YourTable.AddNew
yourTable.Fields("ColumnNa
YourTable.Update
ID = YourTable.Field(ID).Value
because with this method, the field value will be set after .Update()
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry for late, thank you all,sbelean
select max(autonumberfield) from tablename