Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2004
    Location
    Kampala, Uganda
    Posts
    32

    Unanswered: INSERTing a record and getting its ID in one operation

    The table I'm inserting data into has an autonumber primary key. Is there any way I can do the insert statement and retrieve the ID of the record just created in a single operation. Previously I've done the insert and then just done a SELECT MAX(id) to get the id just created, but I reckon there must be a proper way to do it. Also if there's loads of inserting going on and a network delay I'm concerned I'll retrieve the wrong ID.

    This if the rather slcak method I use at the moment;

    Set cnnLocal = CurrentProject.Connection
    rst.Open "INSERT INTO tblDespatch (CustomerName) " & _
    "VALUES ('" & strCustomerName & "')", cnnLocal, adOpenKeyset, adLockPessimistic

    rst.Open "SELECT MAX(DespatchID) AS DespatchID FROM tblDespatch", cnnLocal, adOpenStatic, adLockReadOnly
    intNewDespatchID = rstDespatch.Fields("DespatchID").Value

  2. #2
    Join Date
    Jul 2003
    Posts
    73
    If you actually use the AddNew method of the recordset - you can just get the new index as follows:

    Code:
    rst.AddNew
    intNewDespatchID = rst!DespatchID
    rst!CustomerName = strCustomerName
    ' etc
    rst.Update
    I'm doing this away from home - so syntax may be a little off - but you get the idea.
    Joel Dixon
    Analyst Programmer
    Melbourne, Australia

  3. #3
    Join Date
    Apr 2004
    Location
    Kampala, Uganda
    Posts
    32
    Thanks mate, works great, and the syntax was OK.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •