How can I get the last row that was inserted in a table

  • Thread starter Thread starter Squik27
  • Start date Start date
S

Squik27

hi, I Have an small problem trying to get the id of the last inserted row in
a table so i can use it as a value in a related table. I have to do this
through code in access 2003. I can insert a new row in my table but i need to
recover the id to use it in another insertion.
right now i'm doing it by using the MAX function in SQL, but how would I do
it generally?

Thank you for any answer
 
It sounds like you have what should be set up as a one-to-many relationship
and use a form/subform for data entry/display.
If that is the case make the ID of first table the primary key and then set
the relationship to the other table with Referential Integerity and Cascade
Update.
Set the Master/Child link of the form/subform using the ID. When you add a
new record in the subform it automatically inserts the ID for you.
 
Thank You for your reply, but I'm not using a form. I have to set this
routine to run automatically every week so i can't use a form.

I need to recover the id of the row that was inserted by using
currentdb.excute(INSERT ...). I need the id to use it in another
currentdb.excute(INSERT ...) statement for a related table.

Thank you for any answer
 
hi, I Have an small problem trying to get the id of the last
inserted row in a table so i can use it as a value in a related
table. I have to do this through code in access 2003. I can insert
a new row in my table but i need to recover the id to use it in
another insertion. right now i'm doing it by using the MAX
function in SQL, but how would I do it generally?

Thank you for any answer
Click to expand...

Is there some natural primary key that you could use instead of an
autonum?
 
I believe that Access now supports Select @@identity and Select
scope_identity() as the means to retrieve the last autonumber entered into
the database, with scope_identity being specific to the connection. These
were originally sql server-specific. I haven't tested this in Access but
searching on those terms should get specifics.

Here was one excerpt I found for ADO code (code was in ASP, which is why the
constants aren't there for the command type):
sql = "INSERT someTable(IntColumn) VALUES(" & fakeValue & ")"
conn.execute sql,,1 '1=adCmdText
sql = " SELECT @@IDENTITY"
set rs = conn.execute(sql,,1) 'always tell ADO the command type
 
Back
Top