Last insert id gebruiken in een sql statement

[Muiter]

In onderstaande code worden 2 querys uitgevoerd. In de eerste wordt serverside een id gegeneerd. Deze id wil ik in de 2e query gebruiken. Hoe moet ik dat aanpakken?

    Set wsCtcts = Worksheets("controleblad")
    Set rs = New ADODB.Recordset
    
    With shtControleblad
        Dim strsql_basis As String
            strsql_basis = "INSERT INTO is_calculatie (offerte_id) VALUES ('" & Sheets("controleblad").Range("D1").Value & "')"

            rs.Open strsql_basis, oConn, adOpenDynamic, adLockOptimistic
    End With
    
    Set wsCtcts = Worksheets("materialen")
    Set rs = New ADODB.Recordset
    
    With shtMaterialen
        Dim row_pl As Integer
        Dim strsql_pl As String
        row_pl = 2
        While Trim(.Cells(row_pl, 2)) <> ""
            strsql_pl = "INSERT INTO is_platen (cnc, lengte_genest, lengte_nodig, breedte_genest, breedte_nodig, dikte, materiaal, aantal, snijtijd, machine) " & _
            "VALUES ('" & esc(Trim(.Cells(row_pl, 1).Value)) & "', '" & _
            esc(Trim(.Cells(row_pl, 2).Value)) & "', '" & _
            esc(Trim(.Cells(row_pl, 3).Value)) & "', '" & _
            esc(Trim(.Cells(row_pl, 4).Value)) & "', '" & _
            esc(Trim(.Cells(row_pl, 5).Value)) & "', '" & _
            esc(Trim(.Cells(row_pl, 6).Value)) & "', '" & _
            esc(Trim(.Cells(row_pl, 7).Value)) & "', '" & _
            esc(Trim(.Cells(row_pl, 8).Value)) & "', '" & _
            esc(Trim(.Cells(row_pl, 9).Value)) & "', '" & _
            esc(Trim(.Cells(row_pl, 10).Value)) & "')"

            rs.Open strsql_pl, oConn, adOpenDynamic, adLockOptimistic
            row_pl = row_pl + 1
        Wend
    End With

Ik heb het zonder succes geprobeerd met:

    Set wsCtcts = Worksheets("controleblad")
    Set rs = New ADODB.Recordset
    
    With shtControleblad
        Dim strsql_basis As String
            strsql_basis = "INSERT INTO is_calculatie (offerte_id) VALUES ('" & Sheets("controleblad").Range("D1").Value & "')"

            rs.Open strsql_basis, oConn, adOpenDynamic, adLockOptimistic
            
            Dim last_id As String
            last_id = "select last_insert_id()"
    End With
    
    Set wsCtcts = Worksheets("materialen")
    Set rs = New ADODB.Recordset
    
    With shtMaterialen
        Dim row_pl As Integer
        Dim strsql_pl As String
        row_pl = 2
        While Trim(.Cells(row_pl, 2)) <> ""
            strsql_pl = "INSERT INTO is_platen (id_calculatie, cnc, lengte_genest, lengte_nodig, breedte_genest, breedte_nodig, dikte, materiaal, aantal, snijtijd, machine) " & _
            "VALUES ('" & last_id & "','" & _
            esc(Trim(.Cells(row_pl, 1).Value)) & "', '" & _
            esc(Trim(.Cells(row_pl, 2).Value)) & "', '" & _
            esc(Trim(.Cells(row_pl, 3).Value)) & "', '" & _
            esc(Trim(.Cells(row_pl, 4).Value)) & "', '" & _
            esc(Trim(.Cells(row_pl, 5).Value)) & "', '" & _
            esc(Trim(.Cells(row_pl, 6).Value)) & "', '" & _
            esc(Trim(.Cells(row_pl, 7).Value)) & "', '" & _
            esc(Trim(.Cells(row_pl, 8).Value)) & "', '" & _
            esc(Trim(.Cells(row_pl, 9).Value)) & "', '" & _
            esc(Trim(.Cells(row_pl, 10).Value)) & "')"

            rs.Open strsql_pl, oConn, adOpenDynamic, adLockOptimistic
            row_pl = row_pl + 1
        Wend
    End With

EDIT, dit topic hoort denk ik beter thuis in Visual basic for Applications (VBA)?

 

[OctaFish]

Heb je al gecontroleerd of het eerste deel van de code werkt? Oftewel: doet de Insert query wat-ie moet doen?

 

[Muiter]

Heb je al gecontroleerd of het eerste deel van de code werkt? Oftewel: doet de Insert query wat-ie moet doen?

Alles op de last_insert_id na werkt naar behoren.

 

[snb]

Wat ik niet begrijp:

Set wsCtcts = Worksheets("controleblad")
Set rs = New ADODB.Recordset

With shtControleblad
Dim strsql_basis As String
strsql_basis = "INSERT INTO is_calculatie (offerte_id) VALUES ('" & Sheets("controleblad").Range("D1").Value & "')"

rs.Open strsql_basis, oConn, adOpenDynamic, adLockOptimistic
End With

of je declareert en gebruikt (veelal overbodige) objectvariabelen
Maar nu declareer je ze en gebruikt ze niet.

En waaruit moet het laatste record gelezen worden: uit welke tabel (Recordset).