finding an autonumber after inserting data into its table??

[shef015]

I have an orders table with an autonumber key. I insert the orders data into the table and get the autonumber as its key. I need to get that key right after the insert though in order to use for reference and run inserts on my orders detail table. I am not sure how i can do this. Any info or suggestions would be great. Thanks

 

[johnwm]

In SQl get a return value equal to @@IDENTITY in your update query.

If it's a single user Access db, just query the db, sort ID column descending and use MoveFirst.
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[shef015]

ah ok so thats what @@Identity means. a friend sent me some code with that in it but i didn't understand if i should use that syntax or not. thanks i will try that then

 

[shef015]

ok, nevermind i am slightly stuck. How would I get this with @@Identity? Would i do a simple select statement like "Select @@Identity As Autonum from Orders" after I do my insert? or do i have to do something else in order to get the number created by the insert?

 

[DrJavaJoe]

Selects the last autonumber added but remember if another user adds another autonumber between the time that you add yours and execute this statement it'll will get that number. So this should be executed immediately after your insert statement.

Dim adoConn As ADODB.Connection
Dim adoRS As ADODB.Recordset
Dim lngID as Long
strSQL = "SELECT @@IDENTITY AS NewID"
Set adoRS = adoConn.Execute(strSQL)
lngID = adoRS.Fields("NewID&quot.Value

 

[sgf323]

Normally I pass an SQL string to a command object to update databases but in the case when I need to get an autonumbered ID I open the recordset with ADO.

With RS
.AddNew
.Fields(sField) = sData
.Update
'now the record is created
'get the ID
.Fields("ID&quot = lID
End With

My current project is a replicated access table with multi users so this is the only way that I have found to read back the ID successfully.

 

[shef015]

will this work with Access 97? I know @@Identity only works for Access 2000 and above

 

[shef015]

here is my code so far, how do i fit the ID part into all of this? I know the insert works and everything so i just need to pull that id number


order = "INSERT INTO ORDERS(CustomerPhone, EmployeeId, OrderDate, Payment," & "TotalAmount) VALUES ('" & txtPhone.Text & "'," & empNum & ",'" & oDate & "'," & _
"'" & oPay & "'," & oTotal & &quot"
Set rs_insertOrder = conn.Execute(order)

 

[CCLINT]

>will this work with Access 97

The problem is when you use an action query, no records are returned, and, if other users are adding records around the same time, you cannot determine which newly record was the one added by your action query.
If you do not add records using an action query, but do it with a recordset object (Server side cursor is a must), then after the AddNew method is called, and after the first field value is set, but before the Update method is executed, you can get the auto number field value being used for the new record:

rs.AddNew
rs("SomeField1&quot = "test"
Debug.Print "The new unique auto number is: " & rs("AutoNumberField0&quot
rs("SomeField2&quot = "test2"
rs.UpdateBatch

The new auto number should be reserved for this record, even before the actual update is made.

But, if you are not using a recordset object to add a record, but an action query (INSERT INTO in this case), you could still use a recordset object to get the number, as long as no other users are adding records around the same time, or you could identify the newly added record by some other means, such as another unique field, (a real time stamp, and/or a user name field), or a combination of fields which could be together be considered unique.

Create a recordset object on the same table using a server side static cursor, and for the active connection, you must use the same connection as was used for the action query. The recordset's query string should only contain the name of the auto number field, and an Order By clause on the same field needs to be used.

After executing the action query, use the Requery method of the recordset, and then the MoveLast method.

If this is a single user database, or no other users are adding records to the table around the same time, the recordset record position will be on the last added record and will return the number.

Other than that, if other users are adding records as well, you would need to use a unique field, or a combination of fields that would together produce a fairly unique value, in the criteria of the recordset.

[/b][/i][/u][sub]*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!

 

[1127]

Hi,

You can get the ID (autonum) value:

1. If You use mdb right before the UpdateBatch method

lngID=rst![ID]
rst.UpdateBatch

2. If You work with SQL right after the UpdateBatch

rst.UpdateBatch
lngID=rst![ID]


Tibi

 

[CCLINT]

1127:

shef015 is using an action query.



[/b][/i][/u][sub]*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!

 

[1127]

The database it's accessed at same time by more then one user?

 

[shef015]

actually i got it working though it wasn't really a good way of doing it. i am querying the database for records with the customerphone, date, and cost being the same as the record i just inserted, and have it return the orderid. works fine now but will try other ways when i have time, project has to be finished by tonight