• 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

    • Dead By Daylight and three more games join Xbox Free Play Days this weekend by Pulasthi Ariyasinghe As the weekend approaches, Microsoft has brought along another Free Play Days promotion for Game Pass subscribers to jump into. This time, Dead by Daylight, Headbangers: Rhythm Royale, Trailmakers, and Synduality Echo of Ada are all available for Xbox Game Pass Ultimate, Standard, and Core members for no extra cost. As always, any progress made during the weekend also carries over automatically if you decide to purchase a game afterward. From the four games available, Dead by Daylight should be the most familiar to most gamers. The multiplayer four-versus-one asymmetric survival horror game has you assuming the roles of survivors or the killer to see who can come out on top. Next, Headbangers: Rhythm Royale lands as a light-hearted, rhythm-based battle royale game. Up to 30 players can be in a single round, all represented as pigeons, before various mini-games involving increasingly difficult rhythm challenges get thrown at the group. The last remaining pigeon becomes the "Master Headbanger." Meanwhile, Trailmakers is a popular sandbox experience where you and your friends construct physics-based vehicles. A wide range of modular parts are offered for making these vehicles, with an exploration element also adding more options to search for. Adventure, sandbox, and racing modes arrive as options for playing either solo or in multiplayer. Lastly, the Synduality Echo of Ada is a sci-fi extraction shooter experience where you control mechs as you search for loot while fighting AI and other players. Here are the store links for the announced titles and their supported platforms: Dead by Daylight - $11.99 (Xbox Series X|S, Xbox One) Headbangers: Rhythm Royale - $3.99 (Xbox Series X|S, Xbox One, PC) Trailmakers - $14.99 (Xbox Series X|S, Xbox One, PC) SYNDUALITY Echo of Ada - $29.99 (Xbox Series X|S) This Free Play Days promotion will end on Sunday, June 22, at 11:59 pm PT. Following this, expect another round of games to enter the program next Thursday, June 26.
    • I'm not simping, I'm telling it how it is. This is the norm and has been forever. Profits need to keep growing, it doesn't matter if you are valued at a trillion or a million. As long as you are part of public trading that's the name of the game. If it's cheaper for the company to outsource, they will, and you'll get laid off. Welcome to capitalism 101. Like I said, the problem is the system itself.
    • Samsung may offer Galaxy AI free for life on the Galaxy Z Fold7, Z Flip7, and Z Flip7 FE by Sagar Naresh Bhavsar Samsung is all set to unveil its latest foldables: the Galaxy Z Fold7, Z Flip7, the affordable Z Flip7 FE, along with the Galaxy Watch8 series at the Unpacked event, possibly in New York City. The Galaxy Z Fold7 is expected to be the thinnest book-style foldable from the company, while the Galaxy Z Flip7 is tipped to come with a full-screen cover display. These devices are almost confirmed to launch with Android 16-based One UI 8 out of the box, the beta of which is already live for the Galaxy S25 series. Samsung is also expected to give a glimpse of its first XR headset, the Galaxy XR, dubbed "Project Moohan," at the event. However, there may be something more exciting coming for upcoming foldable fans. A reliable leaker, PandaFlashPro on X, has tipped that you might get all Galaxy AI features for free for life with the upcoming Galaxy Z Flip7 and Galaxy Z Fold7. Notably, Galaxy AI will also reportedly be permanently free on the affordable Galaxy Z Flip7 FE. The leaker also claimed that the Galaxy Z Fold7, Z Flip7, and Z Flip7 FE will also come with Gemini Advanced plus 2TB of cloud storage, free for six months. This is similar to theoffer that Samsung offered the Galaxy S25 series earlier this year. However, the company cleared the air that the Galaxy AI features on the Galaxy S25 series will be free till the end of 2025, after which a subscription-based model will be introduced. If true, then you will be able to enjoy Galaxy AI features such as Sketch to Image, Generative Image, Note Assist, Live Translate, Interpreter, Chat Assist, etc., free for a lifetime on the upcoming foldables, setting it apart from its competitors.
    • Summary for all: Some WD SSDs need firmware updates for 24H2.
    • Next-gen AMD GPUs reported to support HDMI 2.2 with up to 80Gbit/s bandwidth by Aditya Tiwari Some beans have spilled around AMD's rumored upgrades for its next-generation graphics architecture. The American silicon giant is expected to support up to 80 Gbps mode on HDMI 2.2 for its upcoming graphics architecture, internally codenamed GFX13, and expected to launch as RDNA 5 or UDNA. The latest information comes from the leaker @Kepler_L2, who took to their social media account on X (via VideoCardz). It strengthens the hopes of the latest HDMI standard and support for faster bandwidth making their way to the next generation of AMD GPUs. The HDMI 2.2 standard was announced earlier this year and released to manufacturers in the first half of 2025. It pushes the maximum allowed bit rate to 96 Gbps and supports Latency Indication Protocol (LIP) to improve audio and video synchronization. However, according to Kepler's post, RDNA 5 GPUs are expected to support only 64 Gbps and 80 Gbps modes. This suggests that the 96 Gbps mode may not be supported on the new architecture, while it remains unclear whether this is due to some hardware limitation or will be added later. Although the new piece of information pertains to GFX13, it doesn't clarify whether it's related to the PRO or Radeon RX series. The company has previously used UHBR 13.5 (54 Gbps) for its consumer-focused GPUs to reduce costs while supporting UHBR 20 (80 Gbps) on PRO cards, according to the leaker. It remains to be seen whether AMD treats the two product lines the same or differently. Another standard announced earlier this year was DisplayPort 2.1b, which keeps the same maximum bandwidth of 80 Gbps but supports three times the cable length for UHBR20 GPU-to-display connections compared to existing VESA-certified DP80 passive cables. The new standard is supported on NVIDIA's RTX 50 and RTX PRO Blackwell series GPUs. For now, there is no update on whether AMD will bring DisplayPort 2.1b support to the GFX13 architecture.
  • Recent Achievements

    • First Post
      MikeK13 earned a badge
      First Post
    • One Month Later
      OHI Accounting earned a badge
      One Month Later
    • Week One Done
      OHI Accounting earned a badge
      Week One Done
    • First Post
      Thornskade earned a badge
      First Post
    • Week One Done
      Higante88 earned a badge
      Week One Done
  • Popular Contributors

    1. 1
      +primortal
      709
    2. 2
      ATLien_0
      269
    3. 3
      Michael Scrip
      202
    4. 4
      +FloatingFatMan
      178
    5. 5
      Steven P.
      128
  • Tell a friend

    Love Neowin? Tell a friend!