Autonumber Field ( ACCESS)

[prfs]

Hi friends!

How get autonumber (Access) field after update table, via ADO?

Thanks.

Paulo.

 

[MichaelRed]

Paulo,

You may get more response from the Ms. Access Forum. It may also be helpful to provide mode details of the situation.

I assume that you are attempting to get the number of an added record at the time of the addition. I believe there is a 'faq' on the subject in the Ms. Acceess forum. If you cannot find it, or have addittional needs, please post in the Ms. Access Forum(s)


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[sunaj]

If you use VB to access your database:

Use
SELECT @@IDENTITY to retrieve the last value of an COUNTER data type column (COUNTER is the JET SQL data type for AUTONUMBER).

Note that if you try to use this through the Access SQL view user interface it will return 0.

 

[prfs]

Thanks Michael and Sunaj for your help.

In my tests the function @@IDENTITY is only to SQL-Server. Otherwise, in ACCESS, the code in VB is above:

'========================================================='
' Database: Autonumber.mdb
' Fields : ChvTipo ( autonumber )
' Tipo ( text )
'========================================================='

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim lngProductID As Long

Set conn = CreateObject("ADODB.Connection&quot
Set rs = CreateObject("ADODB.Recordset&quot

conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " _
& "Data source=C:\Produtos\Etiquetas\AutoNumber.mdb; " _
& "Persist Security Info=False"

strSQL = "Select * from TipoBeneficio where 0=1"

Set rs.ActiveConnection = conn

rs.CursorLocation = adUseServer
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
rs.Source = strSQL
rs.Open

rs.AddNew
rs("Tipo&quot = "Inclusão 1"
rs.Update

lngProductID = rs("ChvTipo&quot

rs.Close
conn.Close

Set rs = Nothing
Set conn = Nothing

Debug.Print "Valor: " & lngProductID

'========================================================='

The trick is in cursor.location = adUseServer.

Paulo.

 

[anhmytran]

adUseServer allows AddNew and Edit record.
adUseClient allows Read-Only.