• 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

    • I maintain that the Cybertruck was a social experiment.  "Can we convince the massively insecure petrol guzzling, Dodge Ram loving, right wing idiots to buy a poorly built, underpowered, underspecified, electric vehicle that they know everyone will laugh at them in"
    • Wish you could set the WiFi country without setting an SSID. This would stop the warning on every boot.
    • Huawei's Mate XT successor launches this year with a performance and camera boost by Hamid Ganji Last year, Huawei reimagined our perception of folding smartphones by launching the world's first tri-folding device, Mate XT. Contrary to regular folding smartphones like the Samsung Galaxy Z Fold 6, the Huawei Mate XT utilizes a dual-hinge mechanism that allows the device to fold into a "Z" shape. The success of the Mate XT has allegedly prompted Huawei to release a sequel this year. Chinese tipster Digital Chat Station on Weibo says Huawei will release a successor in the second half of 2025. While the folding and screen solution remain unchanged, the tipster claims the second-generation Mate XT gets a faster processor and upgraded camera setup. Digital Chat Station says Huawei will also improve the device's accessories. Last year's Mate XT included an Aramid Fiber Case with an integrated rotating stand in the box as official accessories. Though the tipster doesn't mention quality improvements, Huawei could reinforce the phone's folding endurance. As for the release date, the first-generation Mate XT was released in September 2024, and the Mate XT 2 might also be released at the same time. Interestingly, Huawei announced its Mate XT, the world's first tri-folding device, on September 10 last year, just one day after Apple announced its iPhone 16 series. If Huawei wants to tease Apple again, we can expect the Mate XT 2 to drop simultaneously with the iPhone 17 series. While these devices are not in the same category, Huawei's rivalry with Apple is common knowledge. Tri-folding smartphones provide bigger screens at a more compact size compared to tablets. For example, the Huawei Mate XT has a screen size of 10.2 inches when it's fully open and 7.9 inches when folded once. Also, when you fold it twice, you get a 6.4-inch screen like regular smartphones. Huawei is now the biggest contender in the tri-folding segment, but Samsung may also join the race with a tri-fold version of its Galaxy Fold devices.
    • You can now measure internet speed and download videos using PowerToys Run by Taras Buria PowerToys Run is among my favorite modules, which I use daily (some of them should be integrated into Windows 11). This powerful utility is great for finding stuff, launching apps, navigating the web, and a lot more. Third-party modules help expand PowerToys Run's functionality and add additional capabilities like controlling Spotify, asking ChatGPT, and more. Now, there are three new modules you may find useful: speed test, video downloader, and vocabulary. The SpeedTest module uses Ookla Speedtest CLI for local execution without third-party tracking or ads. It has a modern, theme-aware user interface, and runs with a single command: just open Run and type spt to measure your internet connection speed. The result will show up in a new window with the ability to copy the result URL. The module also supports x64 and ARM systems. The VideoDownloader plugin lets you download videos from YouTube and other websites. You can specify the video quality and format (audio-only, for example) and select where to save the video. To make it work, open PowerToys Run, type dl and add a video URL. Finally, the Definition plugin from the same developer works as a powerful vocabulary that offers word definitions, word pronunciations, phonetics and synonyms, usage examples, and more. Note that these modules are not available in PowerToys out of the box. You need to download them from GitHub and install them manually. Here is how to do that: Download SpeedTest, VideoDownloader, or Definition plugins from their GitHub repositories. Extract downloaded plugins into %LOCALAPPDATA%\Microsoft\PowerToys\PowerToys Run\Plugins\ Open PowerToys Run (Alt + Space is the default shortcut) and launch plugins using their corresponding shortcuts (spt, dl, or def, all three can be customized in settings). You can also configure plugins in PowerToys Run settings. You can find more third-party modules for PowerToys Run on GitHub.
    • Showing people how to self host their own media is harmful, according to YouTube by David Uzondu YouTube has taken down a video from tech creator Jeff Geerling that demonstrated how to use LibreELEC, a lightweight operating system for turning devices into media centers, on a Raspberry Pi 5 for 4K video playback. The video, titled "I replaced my Apple TV—with a Raspberry Pi", originally published in May 2024, was removed in June 2025 under YouTube's "Harmful or dangerous content" policy. According to the violation notice, YouTube claimed the video showed "how to get unauthorized or free access to audio or audiovisual content, software, subscription services, or games that usually require payment." Image via Jeff Geerling Geerling strongly refuted YouTube's claims. He stated clearly, "I purposefully avoid demonstrating any of the tools that are popularly used to circumvent purchasing movie, TV, and other media content." He also emphasized that his own Network Attached Storage, or NAS, contains only legally acquired content. This isn't Geerling's first run-in with YouTube over self-hosted media tools. Last October, his tutorial titled "Better than Disney+: Jellyfin on my NAS" was hit with a similar strike for showing how to set up Jellyfin, an open source media server for organizing and streaming personal media. That strike was quickly overturned after an appeal. But this time, YouTube rejected his appeal, even though the LibreELEC video had been live for over a year, had racked up over half a million views, and contained no promotion of anything illegal. This whole thing feels a lot like what happened with youtube-dl. It's a simple command-line tool for downloading videos, used by tons of people for perfectly legal reasons like saving public domain content or backing up their own uploads. But that didn't stop the RIAA from hitting it with a DMCA takedown on GitHub, calling it a piracy tool. The community pushed back hard, and eventually it was brought back, thanks in part to support from groups like the Electronic Frontier Foundation who pointed out that not everything that can be misused is automatically bad. Side note, the youtube-dl project appears to be unmaintained (the last release was in 2021), if you're looking for an alternative, consider its very popular fork, yt-dlp. After the appeal was rejected, YouTube required Geerling to complete "policy training" to avoid a more serious, permanent strike on his channel. He eventually gave in and took the training. Anyways, if you're interested, he has uploaded the removed LibreELEC video to Internet Archive for anyone to watch.
  • Recent Achievements

    • Week One Done
      luxoxfurniture earned a badge
      Week One Done
    • First Post
      Uranus_enjoyer earned a badge
      First Post
    • Week One Done
      Uranus_enjoyer earned a badge
      Week One Done
    • Week One Done
      jfam earned a badge
      Week One Done
    • First Post
      survivor303 earned a badge
      First Post
  • Popular Contributors

    1. 1
      +primortal
      439
    2. 2
      +FloatingFatMan
      247
    3. 3
      snowy owl
      226
    4. 4
      ATLien_0
      212
    5. 5
      Xenon
      152
  • Tell a friend

    Love Neowin? Tell a friend!