Return the newly created ID

[Abdel K. Belkasri]

Hi there!

This must be easy for those of you who are familiar with VB and DB.

Suppose I have the following Table (In MS Access):

tbCustomers
CustomerID: Auto-number
CustomerName: String

I use a piece of code of this nature to insert a new customer:

strSQL = "INSERT INTO tbCustomers Values (CustomerName,'IBM')"
db.Execute (strSQL)

How do we get back the value of the newly created CustomerID (Auto-number)?

Do we read the database and look for the last inserted element? (another
person may have inserted another row just after I inserted mine!).

Thanks in advance.

--Abdel.

[Patrick Escarcega]

Adbel,

This is not the best method, and it can introduce race conditions
with multiple users. It also assumes that the Auto-Number field
increments.

<just p-code>
NewID= SELECT MAX(CustomerID) FROM tbCustomers WHERE
CustomerName=<customername>

You could also use recordsets and refresh the data after the update.

--
Patrick Escarcega patrick AT vbguru DOT net
Consultant:Maxim Group www.vbguru.net
---------Do not write below this line---------------

"Abdel K. Belkasri" <abdelb@riseintl.com> wrote in message
news:38cd07ae$1@news.devx.com...
> Suppose I have the following Table (In MS Access):
>
> tbCustomers
> CustomerID: Auto-number
> CustomerName: String
>
> I use a piece of code of this nature to insert a new customer:
>
> strSQL = "INSERT INTO tbCustomers Values (CustomerName,'IBM')"
> db.Execute (strSQL)
>
> How do we get back the value of the newly created CustomerID
(Auto-number)?