VBA How to find last insert id?

Question

I have this code:

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

The string last_id is not filled. What is wrong? I need to find te last_insert_id so I can use it in an other query.

Answer 1

you have to add rs.movelast after you open the recordset, that should help

Answer 2

last_id = "select last_insert_id()"

You have set the sql statement to be executed, but have not executed it.
Call rs.Open with the above statement to get the 'last_insert_id` instead.

If mysql supports multiple sql statements on single line, you could do

strsql_basis = "INSERT INTO is_calculatie (offerte_id)  
VALUES ('" & Sheets("controleblad").Range("D1").Value & "')
; select last_insert_id()"

rs.Open strsql_basis, oConn, adOpenDynamic, adLockOptimistic