0

I have an MS Access application running on Microsoft SQL Server. I have some VBA automation that manipulates the database.

My example: Shipping Orders (tblSalesOrders)

When I click "Ship Order", there are some validation checks and if everything passes, I want to create a Shipment (tblShipments) and Shipment Items (tblShipmentDetails). Let's not dig to deep into my design, I want to keep this quite generic if possible.

Of course I want to use SQL Transactions to ensure data integrity.

This is my VBA Code (Conn is my ADO Connection, Exec is my custom built function to execute SQL command within Conn):

intSalesOrderID = Forms!frmSalesOrderDetails!SalesOrderID
        
Conn.BeginTrans

Exec "INSERT INTO tbl1Shipments (CustomerID, DateShipped) " & _
        "VALUES (" & DLookup("CustomerID", "dbo_tbl1SalesOrders", "SalesOrderID=" & intSalesOrderID) & ", " & _
                     "GETDATE())"
               
intShipmentID = DMax("ShipmentID", "dbo_tbl1Shipments", "CustomerID=" & DLookup("CustomerID", "dbo_tbl1SalesOrders", "SalesOrderID=" & intSalesOrderID))

Exec "INSERT INTO tbl1ShipmentDetails (ShipmentID, ---RandomColumn1---, ---RandomColumn2)"
        "VALUES (" & intShipmentID & ", " & _
                GetRandomColumn1Somewhere,
                GetRandomColumn2Somewhere)

Conn.CommitTrans

The problem is that I need to extract the ShipmentID from the previously created Shipment, so I can assign the Foreign Key to Shipment Details. My best idea so far was to use DMAX using the CustomerID as a parameter, but that's probably a bad thing to do...

It doesn't work anyway because I'm still inside the transaction and the Shipment hasn't been truly created yet.

My question is - how to do this correctly? Thanks a lot in advance.

Tomas

ThomassoCZ
  • 73
  • 6
  • 3
    part of doing the above correctly would also be **parametrising** your query; it looks wide open to SQL injection attacks. – Thom A Mar 22 '23 at 11:56
  • 1
    Surely this is easier to put both INSERTS into a SPROC and have the transaction handling inside that? Assuming that ShipmentID is an IDENTITY value, you can then use SCOPE_IDENTITY() to capture the ID from the first INSERT to then use in the second INSERT – planetmatt Mar 22 '23 at 11:58
  • As for the injection attacks: Thanks for the input. This is stuff I dealt with here before and we agreed that it's not important for my particular application to be concerned with. Furthermore, every piece of the information is generated automatically, not by a user. – ThomassoCZ Mar 22 '23 at 11:59
  • 1
    is the stuff executed against sql server? You could use SELECT SCOPE_IDENTITY() to get the last generated identity and then get it back in the same code as the insert, or even combine both in a single exec command? – siggemannen Mar 22 '23 at 12:29
  • IF DMAX (whatever that is) is executed in the same transaction, it should see the maximum value btw. – siggemannen Mar 22 '23 at 12:31
  • Yes, it's against SQL Server. I will definitely look into the SCOPE_IDENTITY() thing. DMAX is a VBA function. It appears that the data are not saved into the actual SQL Server table until the transaction is committed, therefore MS Access doesn't see the value. – ThomassoCZ Mar 22 '23 at 12:41
  • Injection attacks are not just about security, it's also about correctness. What happens if someone puts a `'` in one of the fields? – Charlieface Mar 22 '23 at 14:40

1 Answers1

-1

I found the solution based on the comments provided. The comments suggested that I should use SCOPE_IDENTITY() to capture the ID. However, this wouldn't work because it captures the last ID inserted in ANY table. If I inserted more rows in tbl1ShipmentDetails, then the SCOPE_IDENTITY would indicate the last ID of a ShipmentDetail, not the Shipment itself.

I used IDENT_CURRENT('tbl1Shipments') instead and works perfectly.

The final working code:

intSalesOrderID = Forms!frmSalesOrderDetails!SalesOrderID
        
Conn.BeginTrans

Exec "INSERT INTO tbl1Shipments (CustomerID, ShippingMethodID, ShipToID, CarrierID) " & _
        "VALUES (" & DLookup("CustomerID", "dbo_tbl1SalesOrders", "SalesOrderID=" & intSalesOrderID) & ", " & _
                DLookup("ShippingMethodID", "dbo_tbl1SalesOrders", "SalesOrderID=" & intSalesOrderID) & ", " & _
                DLookup("ShipToID", "dbo_tbl1SalesOrders", "SalesOrderID=" & intSalesOrderID) & ", " & _
                DLookup("CarrierID", "dbo_tbl1SalesOrders", "SalesOrderID=" & intSalesOrderID) & ")"

Exec "INSERT INTO tbl1ShipmentDetails (ShipmentID, SalesOrderDetailID, Quantity) " & _
        "SELECT IDENT_CURRENT('tbl1Shipments'), SalesOrderDetailID, ToShip " & _
            "FROM ##TempShipOrder"

Conn.CommitTrans
ThomassoCZ
  • 73
  • 6
  • "it captures the last ID inserted in ANY table" yes but the idea is you can store the `SCOPE_IDENTITY` in a variable immediately after inserting. `IDENT_CURRENT` doesn't work properly when triggers are involved, or when another session is inserting at the same time. Your other option is an `OUTPUT` clause, see linked question. – Charlieface Mar 22 '23 at 14:37
  • Yeah, i mentioned you should get the two scripts together into one... IDENT_CURRENT is as bad as using MAX, because someone else might beat you and insert another value – siggemannen Mar 22 '23 at 16:05