Return the AutoNumber assignment after an SQL INERT INTO.

[Offace]

Hello News Group,

I'm constructing an Access 2003 backend database containing tables and
queries. All queries are SQL expressions, I trying to figure out if I can
insert into a table that has an AutoNumber PK field, via an append query and
retrieve the new number generated by this field in more or less one hit. To
be more precise,

Table1 (or SelectQuery1)
ID (AutoNumber, Random), SomeName (String (120))

Query1
INSERT INTO Table1 ([SomeName])
VALUES ('thename');

Am I able to structure the insert into query such that I can get the new ID
value without another (separate) query to do so? Something like this that
doesn't work (just added it in for semantics)...

INSERT INTO [Table1]
SELECT [ID], thename
FROM [Table1];

Would a sub-query do it?

Thanks for the consideration news group and thanks in advance.

Regards,
Offace.

 

[Van T. Dinh]

Sorry, no. Access Queries / SQL is single action, either appending or
selecting, not both.

Since you are more than like to use VBA code to run the Append Query / SQL
String, you can use the LastModified Property to navigate to the recently
added Record and retrieve the [ID].

See Access VB Help on the LatModified Property. There is a sample code in
the Help topic to do exactly what you described.

 

[GVaught]

Jet 4 extensions support for querying the last-assigned Auto-Number value
using the same syntax as SQL Server.
SELECT @@IDENTITY.

You would use this most likely with VBA code rather through a straight
query.

 

[Offace]

My humble gratitude to you all. Thank you very much.

Offace.

:
: Hello News Group,
:
: I'm constructing an Access 2003 backend database containing tables and
: queries. All queries are SQL expressions, I trying to figure out if I can
: insert into a table that has an AutoNumber PK field, via an append query
and
: retrieve the new number generated by this field in more or less one hit.
To
: be more precise,
:
: Table1 (or SelectQuery1)
: ID (AutoNumber, Random), SomeName (String (120))
:
: Query1
: INSERT INTO Table1 ([SomeName])
: VALUES ('thename');
:
: Am I able to structure the insert into query such that I can get the new
ID
: value without another (separate) query to do so? Something like this that
: doesn't work (just added it in for semantics)...
:
: INSERT INTO [Table1]
: SELECT [ID], thename
: FROM [Table1];
:
: Would a sub-query do it?
:
: Thanks for the consideration news group and thanks in advance.
:
: Regards,
: Offace.
:
: