MSSQL Record ID Blank.............. (1 Viewer)

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 07:09
Joined
Jul 9, 2003
Messages
16,245
I have an MS Access form linked to an MSSQL table via ODBC. When I create a new record on the Access form, the record ID remains blank, I know this is expected behaviour in MSSQL.

I need the record number (ID) of the newly created record for my VBA code.

I know there is a way of obtaining this number, but I can't find the method/code! The problem is I don't know what I'm looking for, in other words none of my searches have been successful hence the reason for me posting this question!

Any help, observations or criticism welcome.

Cheers Tony
 

mdlueck

Sr. Application Developer
Local time
Today, 03:09
Joined
Jun 23, 2011
Messages
2,631
I have an MS Access form linked to an MSSQL table via ODBC.

...

I need the record number (ID) of the newly created record for my VBA code.

I know there is a way of obtaining this number, but I can't find the method/code!

Here you go for one example:

Retrieve Autonumber ID after adoCMD INSERT via adoRS query to Access tables
http://www.access-programmers.co.uk/forums/showthread.php?t=225703

(In the case that you insist on doing things via Linked Table objects.)

Better would be to use Stored Procedures and grab the value after the INSERT

ADO way to capture IDENTITY value with OUTPUT in an INSERT SQL
http://www.access-programmers.co.uk/forums/showthread.php?t=214872#post1094341
 

Users who are viewing this thread

Top Bottom