CODEWITHSUNDEEP
excelvba
Michael Tuma
Michael Tuma

Reputation: 57

Create unique id for each SQL record in VBA

I have a query to get the MAX recordId from the table in SQL. I then put the value in an array and add 1 to the variable. I then use the variable to create the recordId in SQL. How do I increase the recordId by 1 for each loop?

query3 = "SELECT MAX(tbl_eInvoice_Main.RecordID) AS RecordID, " & _
            "MAX(tbl_ImportDate.toolImportId) As toolImportId FROM tbl_eInvoice_Main " & _
            "INNER JOIN tbl_ImportDate ON tbl_eInvoice_Main.ToolImportID = tbl_ImportDate.ToolImportID;"
    rs.Open query3, con, adOpenStatic, adLockReadOnly, adCmdText
    arr = rs.GetRows(1)
    RecordID = arr(0, 0) + 1
    ToolImportId = arr(1, 0) + 1

Worksheets("DATA").Activate
    Dim rng As Range: Set rng = Application.Range("A2:IP1000")
    Dim row As Range
    For Each row In rng.Rows

query2 = "INSERT INTO tbl_eInvoice_Main (RecordID) values (" & RecordID & ")"

con.Execute query2
    Next row

Upvotes: 0

Views: 160

Answers (1)

Brian M Stafford
Brian M Stafford

Reputation: 8868

You could move your RecordID logic inside the loop like this:

For Each row In rng.Rows
   arr(0, 0) = arr(0, 0) + 1
   query2 = "INSERT INTO tbl_eInvoice_Main (RecordID) values (" & arr(0, 0) & ")"
   con.Execute query2
Next row

However, this approach won't work well if you have multiple users. Just something to keep in mind.

Upvotes: 1

Related Questions