• 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

    • Intel vs AMD? Microsoft seemingly has a clear recommendation for Windows 11 Pro PC upgrade by Sayan Sen Microsoft and its partners are now quite actively and regularly promoting the upgrade to Windows 11. Asus, for example, recently published blog posts about the "mandatory Windows 11 upgrade" that is coming as the Windows 11 end of support date nears. Microsoft itself, from time to time, urges users to upgrade to its newest OS. Back in February 2024, Microsoft released an advert highlighting the best things about Windows 11 over Windows 10. Later, in June in the same year, the tech giant busted "myths and misconceptions" surrounding a Windows 11 upgrade. And towards the end of 2024, in December, Microsoft put up a blog post outlining the gaming features a user enjoys on 11 if they were to upgrade from Windows 10. While technically there is nothing wrong with a company promoting its own product, sometimes these campaigns make little sense and they fall flat. For example, in January earlier this year, Microsoft shared a blog post headlined "Free Upgrade to Windows 11 (For a Limited Time Only)" which did not make sense as it offered little information about it being a "free upgrade," and it was rightfully, later taken down. The company is back again with a new commercial about Windows 11. This time it is aimed mainly at IT professionals and enterprises as the advert talks about upgrading to Windows 11 Pro from Windows 10. This landed a few days after Microsoft released a new backup tool for organizations for such a purpose. What is interesting is that the company is promoting Intel's vPro processors and there is no mention of AMD's Ryzen PRO parts. The commercial is posted on the Windows official YouTube channel and has been titled "Right side of risk | Windows 11 Pro and Intel". The video description says, "Windows 10 support ends October 14. Stay on the right side of risk—upgrade now to the power of Windows 11 Pro PCs with Intel vPro®." AMD does have a support article about the subject headlined "Support Your Customers’ Move to Windows 11, With AMD Ryzen™ PRO Processors" and you can find it here. This is not the first time Microsoft has promoted Intel CPUs over AMD ones. Back in 2021, the company also put up a full page explaining how users should "look for the Intel EVO badge" on a new device before making a purchase decision because such PCs are "verified wonderful" which was a bit of an odd language. Like the limited upgrade time article, the page above was taken down after we reported on it (can be viewed via the archive) and replaced with something else. The new commercial was published about a couple of days ago, and it is possible that Microsoft may have a dedicated AMD advert too in the pipeline scheduled for a later release, and that would only be fair if both companies get a similar treatment.
    • Don’t blame web developers for the downfall of Firefox. 😂
    • Microsoft, Indian police bust AI-powered tech support scam ring targeting elderly in Japan by Paul Hill Pop-up scams pretending to be Microsoft Working with India’s Central Bureau of Investigation (CBI), Microsoft recently assisted in busting a scam network that was targeting the elderly in Japan. The CBI raided 19 locations on May 28, leading to the arrest of six key operatives and the taking down of two call centers. The scammers were impersonating Microsoft specifically and using tech support scams against Japanese seniors. The raid led to the seizure of both digital and physical infrastructure, including computers, storage devices, and phones. The scammers were targeting older adults, who are more vulnerable to fraud. To put this activity to an end, Microsoft’s Digital Crimes Unit (DCU), the Japan Cybercrime Control Center (JC3), Japan’s National Police Agency (NPA), and India’s CBI conducted significant cross-border collaboration to trace the criminals. Thanks to the internet, cross-border crimes like these have been around for a while and multinational tech firms like Microsoft are making significant efforts to help law enforcement agencies crack down on cybercrime. Artificial intelligence is also starting to be used to make more sophisticated scams. The evolving threat This case reveals an evolution in how Microsoft’s DCU addresses cybercrime involving tech support fraud. Thanks to AI, scammers have been able to scale their operations. In response, Microsoft has moved away from focusing on individual call centers to target the heads of criminal operations and disrupting their technical infrastructure. Notably, Microsoft’s collaboration with JC3 is the first time the DCU has partnered with a Japan-based organization to assist victims. Microsoft is continually getting tips from JC3 about malicious pop-ups urging recipients to call fake technical support lines that claim to be Microsoft. This data has allowed Microsoft to shut down 66,000 malicious domains and URLs globally since May 2024. Microsoft noted that artificial intelligence is now being used by criminals to scale their operations. Some ways in which these entities leverage AI are for victim identification, writing convincing scam emails and building fake web pages, as well as for convincing translations. Anyone can use AI for malicious purposes so it could increase the number of people or groups carrying out attacks. It also makes attacks much more sophisticated and harder to detect and necessitates better consumer protections and more sophisticated security tools such as passkeys to reduce hacks. Protecting vulnerable populations and what readers can do Tech support fraud attacks have been found by the FBI to disproportionately affect older people, resulting in $590 million in losses in 2023 for just older Americans alone. In this operation that targeted Japanese victims, around 90% of the 200 affected people were over 50. If you’ve ever received suspicious communications from a party claiming to be Microsoft, you should know that Microsoft never sends unsolicited emails or makes phone calls requesting personal or financial information, and it doesn’t offer unsolicited tech support. If you do get any suspicious communications, then you should report it to Microsoft so that it can take action.
  • 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
      433
    2. 2
      +FloatingFatMan
      239
    3. 3
      snowy owl
      213
    4. 4
      ATLien_0
      211
    5. 5
      Xenon
      157
  • Tell a friend

    Love Neowin? Tell a friend!