Some things you hope you never have to know. Well today I needed to call a query in a legacy Access database (insert) and return the ID of the new record.
I had some normal frustrations with parameters, data types and the like. One piece of advice here – always make sure you explicitly set data types for parameters to Access queries, otherwise you’ll likely get issues when you make the call from ADO.NET, and the error message probably won’t tell you much at all.
When all that was resolved, I discovered that it’s actually possible to use the same connection you used for your insert, to get the new ID (for an Autonumber field), by simply executing the following SQL
SELECT @@IDENTITY
Surprising and simple