Return value in auto number field when inserting

[svjensen]

This question is a mirror of
http://www.mrexcel.com/forum/showthread.php?t=491616 as I am uncertain if it should be posted as en excel or an access question.

I am using VBA in excel to insert values into an Access DB.

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"

[FONT=Verdana][COLOR=black]Function SkrivTilDBViaSQL(Tekst As String) As Boolean[/COLOR][/FONT]
[FONT=Verdana][COLOR=black] Dim SQLstr As String[/COLOR][/FONT]
[FONT=Verdana][COLOR=black] Dim MyCon As New ADODB.Connection[/COLOR][/FONT]
 
[FONT=Verdana][COLOR=black] MyCon.ConnectionString = ConStr[/COLOR][/FONT]
[FONT=Verdana][COLOR=black] MyCon.Open[/COLOR][/FONT]
 
[FONT=Verdana][COLOR=black] SQLstr = "INSERT INTO tblTest (Tekst) VALUES ('" & Tekst & "')"[/COLOR][/FONT]
 
[FONT=Verdana][COLOR=black] MyCon.Execute SQLstr[/COLOR][/FONT]
 
[FONT=Verdana][COLOR=black] SkrivTilDBViaSQL = True[/COLOR][/FONT]
 
[FONT=Verdana][COLOR=black] 'Oprydning i fht. DB[/COLOR][/FONT]
[FONT=Verdana][COLOR=black] MyCon.Close[/COLOR][/FONT]
[FONT=Verdana][COLOR=black] Set MyCon = Nothing[/COLOR][/FONT]
[COLOR=black][FONT=Verdana]End Function[/FONT][/COLOR]

It seems that there might be some help here: http://databases.aspfaq.com/general/...-inserted.html
But I am uncertain how I could include that in my VBA script.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>