Retrieving autonumber

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Paulo Roberto - 29 Aug 2007 02:08 GMT

Hi, how can I retrieve a ID from a AutoNumber field on MS Access after an
insert into instruction?

Is it possible to be done?

Thanks!

Reply to this Message

Bob Barrows [MVP] - 29 Aug 2007 11:53 GMT

> Hi, how can I retrieve a ID from a AutoNumber field on MS Access
> after an insert into instruction?

http://databases.aspfaq.com/general/how-do-i-get-the-identity/autonumber-value-f
or-the-row-i-inserted.html
http://support.microsoft.com/default.aspx/kb/221931

Signature

Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Reply to this Message

Jon Paal [MSMD] - 29 Aug 2007 16:27 GMT

The access example provided from link fails. 2 part SQL doesn't work ??

<%
fakeValue = 5
set conn = CreateObject("ADODB.Connection")
conn.open "<conn string>"
sql = "INSERT someTable(IntColumn) values(" & fakeValue & ")" & _
VBCrLf & " SELECT @@IDENTITY"
set rs = conn.execute(sql)
response.write "New ID was " & rs(0)
rs.close: set rs = nothing
conn.close: set conn = nothing
%>

>> Hi, how can I retrieve a ID from a AutoNumber field on MS Access
>> after an insert into instruction?
>>
> http://databases.aspfaq.com/general/how-do-i-get-the-identity/autonumber-value-f
or-the-row-i-inserted.html
> http://support.microsoft.com/default.aspx/kb/221931

Reply to this Message

Bob Barrows [MVP] - 29 Aug 2007 16:41 GMT

> The access example provided from link fails. 2 part SQL doesn't work
> ??
[quoted text clipped - 5 lines]
> sql = "INSERT someTable(IntColumn) values(" & fakeValue & ")" & _
> VBCrLf & " SELECT @@IDENTITY"

Oh! he never corrected that!! I brought it up with him two years ago! Of
course, he (Aaron) doesn't own the site anymore. Perhaps you could
report this to the current site owners ... ?

Signature

Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Reply to this Message

Mark J. McGinty - 06 Sep 2007 12:48 GMT

>> The access example provided from link fails. 2 part SQL doesn't work
>> ??
[quoted text clipped - 9 lines]
> course, he (Aaron) doesn't own the site anymore. Perhaps you could
> report this to the current site owners ... ?

I don't think the site is being maintained anymore. One of the articles on
it includes a script to generate test data, that reportedly takes 7 minutes
to run. I submitted a revision that generates 28% more test rows, with
identical distribution, in under 15 seconds.

Usually, improving the performance of some op by almost a factor of 30 is
pretty attractive, from a programmer's perspective. :-) I guess I must
assume that the new owners of the site are not actually programmers?

-Mark

Reply to this Message

Jon Paal [MSMD] - 07 Sep 2007 15:25 GMT

if they had allowed addition of user comments, or put the database into a wiki style format, it would continue to improve. As it is
now, the locked structure is too much to maintain.

> I don't think the site is being maintained anymore. One of the articles on it includes a script to generate test data, that
> reportedly takes 7 minutes to run. I submitted a revision that generates 28% more test rows, with identical distribution, in
> under 15 seconds.
>
> Usually, improving the performance of some op by almost a factor of 30 is pretty attractive, from a programmer's perspective. :-)
> I guess I must assume that the new owners of the site are not actually programmers?

Reply to this Message

Webmaster Forum / ASP / Database Access / September 2007

Retrieving autonumber