Re: Is it possible to determine the record ID when using INSERT INTO?

Hi, Tom.

If you're going to open a recordset anyway, I would use it to insert
as well.

For Access 97, there's only one reliable choice in determining the
AutoNumber of a newly inserted record: AddNew, set the value of each of the
Required columns (and perhaps the rest of the columns), and then Update the
Recordset. Read the value of the AutoNumber column at any time after the
AddNew operation, but before moving to another record or closing the
Recordset.

For Access 2000 and later, that method is going to be slower than first
executing the append query via the Execute procedure and then opening the
Recordset to read the value of the AutoNumber column.

In other words, I wouldn't insert records using a Recordset unless that's my
only choice or I need a slower database application.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.


"Tom van Stiphout" <no.spam.tom7744@xxxxxxx> wrote in message
news:d8ds13h1kgd7gu0a683l917bjsi4apoqsv@xxxxxxxxxx
On Thu, 12 Apr 2007 20:45:03 +0800, "Allen Browne"
<AllenBrowne@xxxxxxxxxxxxxx> wrote:

If you're going to open a recordset anyway, I would use it to insert
as well.
Then rather than just reading the value of the key field, I think you
need to do this:
set rs=db.OpenRecordset("SomeTable", dbOpenTable)
rs.AddNew
...
rs.Update
rs.Move 0, rs.LastModified
ShowIdentity = rs!PK_Field

-Tom.


In Access 2000 and later, you can do this:

Function ShowIdentity() As Variant
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = DBEngine(0)(0)
db.Execute "INSERT INTO MyTable ( MyField ) SELECT 'nuffin' AS Expr1;"

Set rs = db.OpenRecordset("SELECT @@IDENTITY AS LastID;")
ShowIdentity = rs!LastID
rs.Close

Set rs = Nothing
Set db = Nothing
End Function

In any version of Access, you can OpenRecorset, AddNew, and read the value
of the key field.



.

Relevant Pages

  • Re: DAO Recordset Help
    ... the autonumber on the table should not advance." ... Autonumber fields would all be changed from sequential to random. ... to a recordset). ... Set the form's recordsource to a query that will give you the desired ...
    (microsoft.public.access.modulesdaovba)
  • Re: copy autonumber from one table to another
    ... Recordset operations, or SQL Append queries? ... the autonumber field value is assigned the ...
    (microsoft.public.access.formscoding)
  • Re: Autonumbers in SQL Server Recordsets
    ... LastModified property is not available with ADO recordset, however, this ... Sylvain Lafontaine, ing. ... autonumber value to a variable for future use. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: autonumber
    ... Why do you need to know the AutoNumber before the adding of the record? ... Dim RS As Recordset ... Dim CNN As Connection ... Set CNN = New Connection ...
    (microsoft.public.vb.database.ado)
  • Re: Is it possible to determine the record ID when using INSERT INTO?
    ... AutoNumber of a newly inserted record: AddNew, set the value of each of the ... Recordset. ... Function ShowIdentity() As Variant ...
    (comp.databases.ms-access)