2

Hope you can help - I'm having issues getting the last ID after INSERT.

So the environment - Access 2016, SQL Server and VBA 

Dim db As DAO.Database
Dim RS As New ADODB.Recordset
Dim sql As String

I have theses declared and then private sub. 

Private Sub CreateOrderHeader()
Dim CustomerID As Integer
Dim OrderDate As String
Dim OrderStatus As String
Dim OrderValue As Double
Dim OrderNotes As String
Dim OrderPostageID As String
Dim OrderAddressID As String
Dim OrderBatchID As Integer
Dim OrderPayment As String
Dim OrderCourierID As String
Dim OrderAgentID As Integer
Dim OrderOutstanding As Double

CustomerID = tbxCusID
OrderDate = Format(Now)
OrderStatus = "InProcess"
OrderValue = txtTotal.value
OrderNotes = tbxNotes.value
OrderPostageID = txtPostage.Column(0)

If tbxCustomerAddress = tbxDeliveryAddress Then
    OrderAddressID = 3 'default customers address
Else
    'NEED TO GET CUSTOMER ADDRESS TO DO
End If
OrderBatchID = cmbBatch.Column(0)
OrderPayment = sPayMethod
OrderCourierID = cbxShipping.Column(0)
OrderAgentID = 0
OrderOutstanding = txtTotal.value

 Dim testvar As String

sql = "INSERT INTO dbo_OrderHeader " _
      & "(OrdCusID, OrdDate, OrdStatus, OrdValue, OrdNotes, OrdPostageID, OrdDelAddID,OrdBatchID,OrdPaymentMethod, OrdCourierID,ordAgentID, OrdOutstanding,OrdSource) " _
      & " VALUES ('" & CustomerID & "' ,'" & OrderDate & "', '" & OrderStatus & "', '" & OrderValue & "', '" & OrderNotes & "', '" & OrderPostageID & "','" & OrderAddressID & "','" & OrderBatchID & "','" & OrderPayment & "','" & OrderCourierID & "','" & OrderAgentID & "','" & OrderOutstanding & "', 1)"



DoCmd.RunSQL (sql)

sql = "SELECT @@IDENTITY As IDT"
RS.Open sql, CurrentProject.Connection, adOpenStatic, adLockReadOnly
IDT = RS!IDT

MsgBox ("Succes - OrderHeader" & " '" & IDT & "'  ")
End Sub

I was expecting a result from this code: 

sql = "SELECT @@IDENTITY As IDT"
RS.Open sql, CurrentProject.Connection, adOpenStatic, adLockReadOnly
IDT = RS!IDT

But that gives me "0" as result.

Can you help please.

Thanks

Parfait
  • 104,375
  • 17
  • 94
  • 125
Azmodan
  • 107
  • 2
  • 10
  • Perhaps relevant, see also the Access specific link embedded in this answer: https://stackoverflow.com/a/186563/84206 – AaronLS Dec 29 '17 at 16:44
  • I recommend a stored procedure, to prevent the possible problems I see with SQL injection. The proc can do the insert and have an output parameter that gives you the ID. – Ctznkane525 Dec 29 '17 at 16:51
  • Thanks, I will have to check - i'm not familiar with Store Procedures. – Azmodan Dec 29 '17 at 17:00
  • Consider also using [parameterization](https://stackoverflow.com/a/47064696/1422451) with Access' `PARAMETERS` clause as some of those form values look to be open-ended for a clever user to inject. – Parfait Dec 29 '17 at 17:19

1 Answers1

2

You can try this :

Set db = CurrentDB
db.Execute(sql)

IDT = db.OpenRecordset("SELECT @@IDENTITY")(0)
Set db = Nothing

NOTE

Don't execute your insert query like DoCmd.RunSQL (sql) Instead follow the above approach.

Md. Suman Kabir
  • 5,243
  • 5
  • 25
  • 43
  • Thanks - but this still returns 0. – Azmodan Dec 29 '17 at 16:59
  • It's great that your problem is sorted out. Don't forget to mark this answer as useful(upvote) :) – Md. Suman Kabir Dec 29 '17 at 17:06
  • I would change @@IDENTITY to Scope_Identity() if possible. @@Identity is not limited to the scope of your transaction so it is possible to get an incorrect number returned. – twoleggedhorse Dec 29 '17 at 17:12
  • @twoleggedhorse ... `Scope_Identity()` is [not supported in MS Access](https://stackoverflow.com/questions/186544/identity-after-insert-statement-always-returns-0/186565#comment11363929_186565). OP is running the Access engine to SQL Server linked table. – Parfait Dec 29 '17 at 17:15