Return value in auto number field when inserting

[svjensen]

I am using VBA in excel to insert values into an Access DB (if I should post in excel questions please let me know).

In order to link my tables together I use the id from one table as the reference in others. Pretty standard I guess.
Now the thing is, that the id is inserted using auto numbering, and I would very much like to have the issued value returned to me, so that I can use it immediately for inserting data in related tables.
How can I do that?

I have inserted a very simple example of my code below.

Const ConStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Q:\PL\PLA\ALLE\03 Periodeplan\Sandkasse\Access programmering\TestDB.mdb;Persist Security Info=False"
 
Function SkrivTilDBViaSQL(Tekst As String) As Boolean
    Dim SQLstr As String
    Dim MyCon As New ADODB.Connection
 
    MyCon.ConnectionString = ConStr
    MyCon.Open
 
    SQLstr = "INSERT INTO tblTest (Tekst) VALUES ('" & Tekst & "')"
 
    MyCon.Execute SQLstr
 
    SkrivTilDBViaSQL = True
 
    'Oprydning i fht. DB
    MyCon.Close
    Set MyCon = Nothing
End Function

It seems that there might be some help here: http://databases.aspfaq.com/general.../autonumber-value-for-the-row-i-inserted.html
But I am uncertain how I could include that in my VBA script.

 

[CT Witter]

See my post at http://www.mrexcel.com/forum/showthread.php?t=480830

HTH,
CT