• 0

[Access 2003 VBA] Return AutoNumberID of SQL Insert Result?


Question

Is there a way to get the AutoNumber ID value of the record that was just inserted via a SQL INSERT query?

EDIT:

First, get what the next AutoNumber will be:

SELECT MAX(OrderID)
FROM tblOrders;

Then insert records referencing that AutoNumber ID.

Is there a better solution...?

Edited by magik

16 answers to this question

Recommended Posts

  • 0

I think you can use either

SELECT @@IDENTITY FROM tbl

or maybe... MAYBE

SELECT SCOPE_IDENTITY() FROM tbl

I'm not certain that you can do multiple queries in one statement in Access. It's been a long time, thankfully.

Edit:

skyfox beat me to it! :)

  • 0
  skyfox01_99 said:
If using Jet 4.0 use SELECT @@IDENTITY.

Is that possible within MS Access VBA? Could I trouble you for some more detailed information/example? Forgive me, it's been a while since I have used Access VBA...

Edited by magik
  • 0

Try something like this:

Dim rs as DAO.Recordset

CurrentDB.Execute ("INSERT INTO .....")
Set rs = CurrentDB.OpenRecordset("SELECT @@IDENTITY")

If Not rs.EOF Then
	MsgBox rs.Fields(0)
Else
	MsgBox "No AutoNumber generated."
End If

rs.Close()

  • 0

Related question: Would this work with multiple records?

ie: Is the following code valid?

Dim rs As DAO.Recordset

Call CurrentDb.Execute(strSQL)
Set rs = CurrentDb.OpenRecordset("SELECT @@IDENTITY")

rs.MoveFirst
Do While Not rs.EOF
	MsgBox rs.Fields(0)
	rs.MoveNext
Loop

  • 0

The code is valid but it won't produce the result you want; SELECT @@IDENTITY returns the last AutoNumber that was generated. Therefore the loop and the "rs.MoveNext" are unnecessary. The "rs.MoveFirst" is not required as the Recordset will be open at the first record anyway (or EOF if no AutoNumber was generated).

  • 0
  skyfox01_99 said:
The code is valid but it won't produce the result you want; SELECT @@IDENTITY returns the last AutoNumber that was generated. Therefore the loop and the "rs.MoveNext" are unnecessary. The "rs.MoveFirst" is not required as the Recordset will be open at the first record anyway (or EOF if no AutoNumber was generated).

What would you suggest for that desired result, then? I basically need a recordset of the affected rows.

Is the

SELECT SCOPE_IDENTITY() FROM tbl

That azcodemonkey suggested a plausible solution?

  • 0
  magik said:
What would you suggest for that desired result, then? I basically need a recordset of the affected rows.

Is the

SELECT SCOPE_IDENTITY() FROM tbl

That azcodemonkey suggested a plausible solution?

I'm fairly certain that Access 2003 doesn't support the SCOPE_IDENTITY function, unfortunately. All the searches I did alluded to that.

  • 0

You can only insert one row at a time (unless you're doing an "INSERT INTO ... SELECT FROM ..." but that's a different matter) so there should only ever be one generated autonumber. Can you paste some of your existing code so that we can get an idea of what you want?

  • 0
  skyfox01_99 said:
You can only insert one row at a time (unless you're doing an "INSERT INTO ... SELECT FROM ..." but that's a different matter) so there should only ever be one generated autonumber. Can you paste some of your existing code so that we can get an idea of what you want?

Actually, I am doing an INSERT INTO ... SELECT FROM. Here is some sample code, hope it helps....

INSERT INTO tblWorkOrder(Artist, Title, Vendor)
SELECT w.Artist, w.Title, w.Vendor
FROM tblWorkOrder w WHERE w.SalesOrderID=intSOID;

Which, by the way, doesn't seem to be a valid query since Access complains that it didn't add X number of records because of "key violations". No idea why that is. The table structure is like so:

tblWorkOrder

WorkOrderID (PK), SalesOrderID (FK), Artist, Title, Vendor... etc

The other table in question is, tblSalesOrder, which can be thought of as a "parent table"

tblSalesOrder

SalesOrderID (PK), Date, Customer, Address, ... etc

Basically what I am trying to do is create a "Reorder" function, which copies all the necessary fields and duplicates them. I have a similar update query for the tblSalesOrder table that works fine:

INSERT INTO tblSalesOrder
SELECT s.PONumber, s.Customer, s.BillAddress, s.Priority FROM tblSalesOrder s
WHERE s.SalesOrderID = curSOID

:wacko:

Thanks for any and all help...

  • 0

I'd guess that your key violation errors are due to attempts to insert duplicate values into a column with a unique index/key. There's no Access function to return all of the autonumbers generated by a mass-insert. You'll have to run a select after the insert to pull out the IDs of the new records.

Something like this:

  1. Duplicate the Sales Order,
  2. "SELECT @@IDENTITY" to get the new sales order ID,
  3. Duplicate the work order details,
  4. Run "SELECT WorkOrderID FROM tblWorkOrder WHERE SalesOrderId = <New_SOID>" to get the new WorkOrderIDs

  • 0
  skyfox01_99 said:
I'd guess that your key violation errors are due to attempts to insert duplicate values into a column with a unique index/key. There's no Access function to return all of the autonumbers generated by a mass-insert. You'll have to run a select after the insert to pull out the IDs of the new records.

Something like this:

  1. Duplicate the Sales Order,
  2. "SELECT @@IDENTITY" to get the new sales order ID,
  3. Duplicate the work order details,
  4. Run "SELECT WorkOrderID FROM tblWorkOrder WHERE SalesOrderId = <New_SOID>" to get the new WorkOrderIDs

That is exactly what I am doing. The problem is, a sales order can have multiple Work Orders related to it. There are no unique keys besides the WorkOrderID Primary Key in the tblWorkOrder table, so I am not sure why Access is giving that error. :dontgetit:

Also, since there is no way to determine all of the autonumbers generated by a mass-insert then I guess this function is impossible? Perhaps a solution with individual recordset iterations and a per-record update would be the only way to go about it? Man, I was hoping for a more efficient, clean solution. :(

  • 0

I figured out why Access was complaining about the "key violations" for the tblWorkOrder INSERT query. The following fixes the issue:

INSERT INTO tblWorkOrder(SalesOrderID, Artist, Title, Vendor)
SELECT w.SalesOrderID, w.Artist, w.Title, w.Vendor
FROM tblWorkOrder w WHERE w.SalesOrderID=oldSalesOrderID;

I need to be able to specify the value of SalesOrderID to be placed in that INSERT operation, namely it needs to hold the value of newSalesOrderID (that was created in the previous INSERT INTO tblSalesOrder and retrieved via SELECT @@IDENTITY) in the tblSalesOrder table.

The problem now is, how can I specify the newSalesOrderID to be inserted in the INSERT INTO tblWorkOrder statement?

  • 0

You can specify a static value in the SELECT clause:

INSERT INTO tblWorkOrder(SalesOrderID, Artist, Title, Vendor)
SELECT 1234, w.Artist, w.Title, w.Vendor
FROM tblWorkOrder w WHERE w.SalesOrderID=oldSalesOrderId;

You can do this by building up the SQL statement via string concatenation or by creating a QueryDef. I prefer the latter approach:

First create a query using the following SQL and save it:

INSERT INTO tblWorkOrder(SalesOrderID, Artist, Title, Vendor)
SELECT newSalesOrderId, w.Artist, w.Title, w.Vendor
FROM tblWorkOrder w WHERE w.SalesOrderID=oldSalesOrderId;


...and in VBA:

Dim qry as QueryDef
Set qry = CurrentDb.QueryDefs("procDuplicateWorkOrder")

qry.Parameters("newSalesOrderId").Value = newSalesOrderId
qry.Execute

Set qry = Nothing

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.
  • Posts

    • That explains my recent issues with classic Outlook. I can see the FORMS2 folder has resolved the issue on my system.
    • Brave 1.79.126 by Razvan Serea Brave Browser is a lightning-fast, secure web browser that stands out from the competition with its focus on privacy, security, and speed. With features like HTTPS Everywhere and built-in tracker blocking, Brave keeps your online activities safe from prying eyes. Brave is one of the safest browsers on the market today. It blocks third-party data storage. It protects from browser fingerprinting. And it does all this by default. Speed - Brave is built on Chromium, the same technology that powers Google Chrome, and is optimized for speed, providing a fast and responsive browsing experience. Brave Browser also features Brave Rewards, a system that rewards users with Basic Attention Tokens (BAT) for viewing opt-in ads. This innovative system provides an alternative revenue model for content creators and a way to support the Brave community. Brave 1.79.126 changelog: Fixed crash when clicking on shields panel in certain cases. Fixed crash on navigation when a Google sign-in request has been triggered. (#46769) Fixed crash when Leo generates empty list when clicking on “Suggest questions” while using BYOM (Bring Your Own Model). (#46843) Fixed issue importing from Trezor wallet when Trezor Connect is outdated. (#46660) Upgraded Chromium to 137.0.7151.119. (#46911) Download: Brave Browser 64-bit | 1.2 MB (Freeware) Download: Brave Browser 32-bit View: Brave Homepage | Offline Installers | Screenshot Get alerted to all of our Software updates on Twitter at @NeowinSoftware
    • LeafView 3.6.5 by Razvan Serea LeafView is a fast, open-source image viewer built with Electron. It offers a sleek, minimal UI for fast and efficient image browsing. Supporting various formats, LeafView ensures a smooth viewing experience with essential features like zoom, rotation, and slideshow mode. Designed for simplicity and performance, it utilizes hardware acceleration for smooth rendering and supports touch gestures for seamless navigation. LeafView key features: Lightweight & Open-Source – Minimal resource usage with a clean, efficient design Electron-Based – Cross-platform compatibility with modern UI Multiple Image Format Support – Opens JPG, PNG, GIF, BMP, and more Smooth Rendering – Hardware acceleration for fast performance Essential Image Controls – Zoom, rotate, and slideshow mode Touch Gesture Support – Seamless navigation on compatible devices Minimal UI – Focused on simplicity and ease of use LeafView 3.6.5 changelog: Update electron to v36.5.0 Download: LeafView 3.6.5 | Portable | ~100.0 MB (Open Source) View: LeafView Website | Other operating systems | Screenshot Get alerted to all of our Software updates on Twitter at @NeowinSoftware
    • Which is why I continue to use Open Shell for my start menu. your best course of action is to install a third-party Start menu
  • Recent Achievements

    • First Post
      Fuzz_c earned a badge
      First Post
    • First Post
      TIGOSS earned a badge
      First Post
    • Week One Done
      slackerzz earned a badge
      Week One Done
    • Week One Done
      vivetool earned a badge
      Week One Done
    • Reacting Well
      pnajbar earned a badge
      Reacting Well
  • Popular Contributors

    1. 1
      +primortal
      700
    2. 2
      ATLien_0
      280
    3. 3
      Michael Scrip
      208
    4. 4
      +FloatingFatMan
      196
    5. 5
      Steven P.
      130
  • Tell a friend

    Love Neowin? Tell a friend!