MS Access - getting the autonumber field from a record created using SQL INSERT method

I’m using MS Access as an ODBC database. I can create a record in a table with an autonumber field as the primary key. Is there a method to obtain the value of the autonumber field as soon as it is created? I used to use .LastRowId back in the olden days, but this doesn’t seem to a part of the ODBC implementation.

I don’t know of a quicker way, but you could always

select max(ID) from table

If you have a DAO connection set up, there is a recordset LastModified property that will steer you through the recordset

myRS.Move 0, myRS.LastModified // Now use the value of the ID field

Max(ID) may not always work in a multiuser environment as someone else could add a record in the meantime/at same time. I write to a field in the record, the logged on user, terminal, seconds - the seconds bit may be overkill. I then search for that string and grab the autoindex then I remove the temporary string.

Not tested but you can try

SELECT @@IDENTITY FROM table

or

SELECT @@IDENTITY

These must be run on the same connection as the insert was

Dennis

This StackOverflow question seems to confirm what Dennis suggests, to use “SELECT @@IDENTITY”.

https://stackoverflow.com/questions/1628267/autonumber-value-of-last-inserted-row-ms-access-vba

Thanks folks, I’ll give it a try. I might suggest to the people asking for the program that we might find a “better” database. They like the ability to design reports on the fly within Access.

I totally redesigned an Access front end for a legacy application in XOJO as changing the back end database was not an option although a proper server would be better in our case. The same software can now be run on Mac, Web (mobile) and original Windows, probably Linux as well but i havnt tried that. If you are allowed to change the DB, Postgres or Maria db as a server, or sqlite to replace the .mdb, .accd but this will be limited on multi user capability.

If your requirements are relatively modest you could migrate to SQL Server Express on the back end. Access can Upsize the database with a minimum of fuss. Clients could still use Access for reporting (using Linked Tables), and Xojo can just talk directly to the server using OLEDB or ODBC.

I’ve done a number of these Access Application migrations and it works very well. As Paul mentioned, you could then offer Mac, Linux, and Web front ends. Even better, you can stop using Access VBA! (Gawd, how I hated Access programming).

[quote=410537:@Tanner Lee]If your requirements are relatively modest you could migrate to SQL Server Express on the back end. Access can Upsize the database with a minimum of fuss. Clients could still use Access for reporting (using Linked Tables), and Xojo can just talk directly to the server using OLEDB or ODBC.

I’ve done a number of these Access Application migrations and it works very well. As Paul mentioned, you could then offer Mac, Linux, and Web front ends. Even better, you can stop using Access VBA! (Gawd, how I hated Access programming).[/quote]

i have an client that move from Access version to a CubeSQL backend and Xojo front end. They insist of using Access as a Report Writer. They are using a CubeSQL ODBC driven in their Access Report Program and get data from CubeSQL.