By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,660 Members | 1,127 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,660 IT Pros & Developers. It's quick & easy.

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

P: n/a
MLH
Suppose, in a multi-user environment, you
have append query SQL in a VBA procedure
that looks like INSERT INTO MyTable...
and the next line reads MyVar=DMax("[MyKeyField]","MyTable...

You can never be certain that MyVar will be
set to the key-field value that was created
when the Append query ran. Now, there are
other ways to do it - I know - that will ensure
you 'nab' the correct record. But I was wondering
if there was any retun value in an Append query
situation that Access will return something that
can be used to uniquely identify the record that
was appended???
Apr 12 '07 #1
Share this Question
Share on Google+
10 Replies


P: n/a
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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"MLH" <CR**@NorthState.netwrote in message
news:eq********************************@4ax.com...
Suppose, in a multi-user environment, you
have append query SQL in a VBA procedure
that looks like INSERT INTO MyTable...
and the next line reads MyVar=DMax("[MyKeyField]","MyTable...

You can never be certain that MyVar will be
set to the key-field value that was created
when the Append query ran. Now, there are
other ways to do it - I know - that will ensure
you 'nab' the correct record. But I was wondering
if there was any retun value in an Append query
situation that Access will return something that
can be used to uniquely identify the record that
was appended???
Apr 12 '07 #2

P: n/a
MLH
On Thu, 12 Apr 2007 20:45:03 +0800, "Allen Browne"
<Al*********@SeeSig.Invalidwrote:
>In any version of Access, you can OpenRecorset, AddNew, and read the value
of the key field.
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

I'm using A97. The OpenRecordset.AddNew will work 4 me.
Thankx Allen.
Apr 12 '07 #3

P: n/a
On Thu, 12 Apr 2007 20:45:03 +0800, "Allen Browne"
<Al*********@SeeSig.Invalidwrote:

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.
Apr 12 '07 #4

P: n/a
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/ex...ributors2.html for contact
info.
"Tom van Stiphout" <no*************@cox.netwrote in message
news:d8********************************@4ax.com...
On Thu, 12 Apr 2007 20:45:03 +0800, "Allen Browne"
<Al*********@SeeSig.Invalidwrote:

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.

Apr 12 '07 #5

P: n/a
On Thu, 12 Apr 2007 09:00:09 -0700, "'69 Camaro"
<Fo**************************@Spameater.orgZERO_SP AMwrote:

>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.
You may want to check the help file on LastModified.
>
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.
But then you can't guarantee that another user hasn't just added a
record. That was the OP's issue.

-Tom.
>
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/ex...ributors2.html for contact
info.
"Tom van Stiphout" <no*************@cox.netwrote in message
news:d8********************************@4ax.com.. .
>On Thu, 12 Apr 2007 20:45:03 +0800, "Allen Browne"
<Al*********@SeeSig.Invalidwrote:

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.
Apr 13 '07 #6

P: n/a
Hi, Tom.
You may want to check the help file on LastModified.
I can't find a compelling reason to move to the last modified record when
I'm already on it, having just executed the AddNew procedure. Just read the
value of the AutoNumber column immediately after the AddNew procedure, and
there's no need to take the extra step of moving to this record using the
LastModified bookmark before reading the AutoNumber column.
>>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.
But then you can't guarantee that another user hasn't just added a
record.
Allen's method is guaranteed to give the value of the AutoNumber column of
the record you just inserted, even if thousands of new records have been
inserted by other processes between the time you added this new record and
the time you read its AutoNumber value, as long you don't close the
Recordset, nor add more records in the code of that procedure between those
two times (because it retrieves the last one you inserted).

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/ex...ributors2.html for contact
info.
"Tom van Stiphout" <no*************@cox.netwrote in message
news:5c********************************@4ax.com...
On Thu, 12 Apr 2007 09:00:09 -0700, "'69 Camaro"
<Fo**************************@Spameater.orgZERO_SP AMwrote:

>>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.
You may want to check the help file on LastModified.
>>
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.
But then you can't guarantee that another user hasn't just added a
record. That was the OP's issue.

-Tom.
>>
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/ex...ributors2.html for contact
info.
"Tom van Stiphout" <no*************@cox.netwrote in message
news:d8********************************@4ax.com. ..
>>On Thu, 12 Apr 2007 20:45:03 +0800, "Allen Browne"
<Al*********@SeeSig.Invalidwrote:

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.

Apr 13 '07 #7

P: n/a
On Thu, 12 Apr 2007 23:57:11 -0700, "'69 Camaro"
<Fo**************************@Spameater.orgZERO_SP AMwrote:

From
http://msdn.microsoft.com/archive/de...etobjects.asp:

The LastModified property of the Recordset object provides a good
illustration of how to use a bookmark. The LastModified property
returns the bookmark of the last record in the Recordset to be added
or modified. To use it, set the DAO Bookmark property equal to the
LastModified property, as follows:

rstCustomers.Bookmark = rstCustomers.LastModified

This moves the current record position to the last record that was
added or modified. This is particularly useful when adding new
records, because after you add a new record, the current record is the
one you were on before you added the record. With the LastModified
property, you can move to the newly added record if thats what your
application expects.

-Tom.

>Hi, Tom.
>You may want to check the help file on LastModified.

I can't find a compelling reason to move to the last modified record when
I'm already on it, having just executed the AddNew procedure. Just read the
value of the AutoNumber column immediately after the AddNew procedure, and
there's no need to take the extra step of moving to this record using the
LastModified bookmark before reading the AutoNumber column.
>>>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.
But then you can't guarantee that another user hasn't just added a
record.

Allen's method is guaranteed to give the value of the AutoNumber column of
the record you just inserted, even if thousands of new records have been
inserted by other processes between the time you added this new record and
the time you read its AutoNumber value, as long you don't close the
Recordset, nor add more records in the code of that procedure between those
two times (because it retrieves the last one you inserted).

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/ex...ributors2.html for contact
info.
"Tom van Stiphout" <no*************@cox.netwrote in message
news:5c********************************@4ax.com.. .
>On Thu, 12 Apr 2007 09:00:09 -0700, "'69 Camaro"
<Fo**************************@Spameater.orgZERO_S PAMwrote:

>>>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.
You may want to check the help file on LastModified.
>>>
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.
But then you can't guarantee that another user hasn't just added a
record. That was the OP's issue.

-Tom.
>>>
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/ex...ributors2.html for contact
info.
"Tom van Stiphout" <no*************@cox.netwrote in message
news:d8********************************@4ax.com ...
On Thu, 12 Apr 2007 20:45:03 +0800, "Allen Browne"
<Al*********@SeeSig.Invalidwrote:

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.

Apr 13 '07 #8

P: n/a
Tom, after adding a new record, the current record is theoretically
undefined, so setting the Bookmark to LastModified guarantees that the newly
created is the current one. That always works.

But JET assigns the AutoNumber as soon as you *begin* adding the new record,
i.e. at the point when the AddNew executes. At that point, the recordset
buffer does contain the value of the AutoNumber. Gunny's point is that you
can read the value straight from the recordset buffer before the Update, so
setting the bookmark afterwards is an unnecessary step. Again, this always
works - provided the data is in Access tables.

However, if the recordset is attached from another data source, that may not
work. SQL Server, for example, doesn't assign the identity at the point when
the edit begins.

So, setting the bookmark is slightly more effort to code, but slightly safer
if the table could be another data source. Given that setting the bookmark
is almost instantaneous, there is no real performance difference either way.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Tom van Stiphout" <no*************@cox.netwrote in message
news:fh********************************@4ax.com...
On Thu, 12 Apr 2007 23:57:11 -0700, "'69 Camaro"
<Fo**************************@Spameater.orgZERO_SP AMwrote:

From
http://msdn.microsoft.com/archive/de...etobjects.asp:

The LastModified property of the Recordset object provides a good
illustration of how to use a bookmark. The LastModified property
returns the bookmark of the last record in the Recordset to be added
or modified. To use it, set the DAO Bookmark property equal to the
LastModified property, as follows:

rstCustomers.Bookmark = rstCustomers.LastModified

This moves the current record position to the last record that was
added or modified. This is particularly useful when adding new
records, because after you add a new record, the current record is the
one you were on before you added the record. With the LastModified
property, you can move to the newly added record if thats what your
application expects.

-Tom.

>>Hi, Tom.
>>You may want to check the help file on LastModified.

I can't find a compelling reason to move to the last modified record when
I'm already on it, having just executed the AddNew procedure. Just read
the
value of the AutoNumber column immediately after the AddNew procedure, and
there's no need to take the extra step of moving to this record using the
LastModified bookmark before reading the AutoNumber column.
>>>>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.
But then you can't guarantee that another user hasn't just added a
record.

Allen's method is guaranteed to give the value of the AutoNumber column of
the record you just inserted, even if thousands of new records have been
inserted by other processes between the time you added this new record and
the time you read its AutoNumber value, as long you don't close the
Recordset, nor add more records in the code of that procedure between
those
two times (because it retrieves the last one you inserted).

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/ex...ributors2.html for contact
info.
"Tom van Stiphout" <no*************@cox.netwrote in message
news:5c********************************@4ax.com. ..
>>On Thu, 12 Apr 2007 09:00:09 -0700, "'69 Camaro"
<Fo**************************@Spameater.orgZERO_ SPAMwrote:
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.
You may want to check the help file on LastModified.
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.
But then you can't guarantee that another user hasn't just added a
record. That was the OP's issue.

-Tom.


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/ex...ributors2.html for contact
info.
"Tom van Stiphout" <no*************@cox.netwrote in message
news:d8********************************@4ax.co m...
On Thu, 12 Apr 2007 20:45:03 +0800, "Allen Browne"
<Al*********@SeeSig.Invalidwrote:
>
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.
Apr 13 '07 #9

P: n/a
"Allen Browne" <Al*********@SeeSig.Invalidwrote in
news:46***********************@per-qv1-newsreader-01.iinet.net.au:
However, if the recordset is attached from another data source,
that may not work. SQL Server, for example, doesn't assign the
identity at the point when the edit begins.
But if you're using SQL Server, couldn't you use ADO, and does not
ADO provide a property that returns the PK of the last added record?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Apr 13 '07 #10

P: n/a
MLH
Yup, that sounds like the ticket to me
Thx, Tom.

Kind regards, mlh
Apr 13 '07 #11

This discussion thread is closed

Replies have been disabled for this discussion.