max autonum multi-user environment

[NJS]

Hi All,
although max ('ID' where param ..) is almost 100% I need to know the max
autonum in a multi-user environment (e.g. max ID for particular client
session)similar to MySQL ADO 'LAST_INSERT_ID()'.
Does Access ADO/OLEDB have a similar function/option?
thank you.

 

[Michel Walsh]

If you use a recordset, it depends on if you are using DAO or ADO. In DAO,
you get the autonumber value as soon as you AddNew, but you have to read and
keep it in a variable before you update the record since DAO move back to
where it was before the append. ADO, on the other hand, stays on the new
record after you update it, and you can read the value at that moment. With
ADO, if you append the data through a given connection, you can also use the
same connection to read the @IDENTITY value:

saidConnection.Open( "SELECT @IDENTITY").Fields(0).Value



The variable @Identity is maintained by user, so other users can add
records, that won't modify YOUR session value, as long as your session don't
add records, through THE mentioned connection. Triggers, if any, though, can
modify this value.



Hoping it may help,
Vanderghast, Access MVP

 

[NJS]

Thanks Michael,
unfortunately this app is using a DAO/runsql statement, and (very
occasionally) two records are entered by different users at the same time. I
need a machine specific solution so I will probably add the machine name to
the table so Ii can get the latest record for that machine.
cheers.

 

[Michel Walsh]

If you append MULTIPLE records in one 'run', then, indeed, it may be
preferable to do as you mentioned (there are alternatives, such as to use a
GUIID, which implies the machine Ethernet card, an hardware number
different, in principle, for each and every Ethernet adapter,... else TCP/IP
will get problem, on the network with two identical Ethernet numbers). On
the other hand, if your user add only one record at a time, through a FORM,
then, you can capture the id in the form before update event, with DAO,
which correspond to 'after the AddNew, but before the Update, for a
recordset.


Vanderghast, Access MVP

 

[NJS]

Thanks Michael, that is what I was going to do. It is unfortunate
(considering Access knows 'who' did the transaction) that it does not provide
a 'LAST_ID_BY_USER()' function.

 

[Michel Walsh]

You could then know if you are the last user, OR NOT... What would you do if
you discover you are not the last user having update/append something? That
'branch' of possibilities would have to be 'coded' anyhow, isn't it, and you
will be back at square zero. I may have not understood what you meant,
though... (but which sounds interesting, anyhow).


Vanderghast, Access MVP

 

[Jamie Collins]

saidConnection.Open( "SELECT @IDENTITY").Fields(0).Value

It is in fact

SELECT @@IDENTITY

i.e. *two* commercial at symbols.

Note this only works with Jet Autonumbers where the auto-generation
algorithm is *incremental* (not random, not GUID).

The variable @Identity...

"Variable"? All the Microsoft articles describes the Jet syntax SELECT
@@IDENTITY as a "query" e.g.

INFO: Jet OLE DB Provider Version 4.0 Supports SELECT @@Identity
http://support.microsoft.com/kb/232144

...is maintained by user, so other users can add
records, that won't modify YOUR session value, as long as your session don't
add records, through THE mentioned connection.

In one sentence you implied it was maintained at the user level and

Triggers, if any, though, can
modify this value.

Not it Jet ;-) SQL Server BOL describes @@IDENTITY as a *function*. A
user cannot explicitly change the value returned by @@IDENTITY, even
via a trigger. I guess you mean that a T-SQL statement may cause a
trigger to fire that may cause an further IDENTITY value to be
generated, which is why I find I more frequently use SCOPE_IDENTITY.

Jamie.

--

 

[Michel Walsh]

Yes, it is @@ not @, my mistake, but it works with Jet:


Public Sub ShowID()
Dim db As Database: Set db = CurrentDb
Dim xnn As ADODB.Connection: Set xnn = CurrentProject.Connection

' Drop the table, if it exists (else, no error occurs anyhow, unless
already open).
db.Execute "DROP TABLE showID"
' create the table
db.Execute "CREATE TABLE showID(id AUTOINCREMENT, f1 INT)"

xnn.Execute "INSERT INTO showID(f1) VALUES(222)"
xnn.Execute "INSERT INTO showID(f1) VALUES(333)"
xnn.Execute "INSERT INTO showID(f1) VALUES(444)"

' get @IDENTITY
Debug.Print "After 3 inserts, ADO: ", xnn.Execute("SELECT
@@Identity").Fields(0).Value

End Sub


And it is maintained by ADO.Connection.


Vanderghast, Access MVP

 

[Michel Walsh]

I should have supplied the whole code, with illustration of AddNew different
behavior between ADO and DAO:

----------------------- cut and paste in a standard module and
----------------------- in the immediate window, run: ShowID
-----------------------
Option Compare Database
Option Explicit

Public Sub ShowID()
Dim db As Database: Set db = CurrentDb
Dim xnn As ADODB.Connection: Set xnn = CurrentProject.Connection

' Drop the table, if it exists (else, no error occur anyhow, unless
already open).
db.Execute "DROP TABLE showID"
' create the table
db.Execute "CREATE TABLE showID(id AUTOINCREMENT, f1 INT)"

xnn.Execute "INSERT INTO showID(f1) VALUES(222)"
xnn.Execute "INSERT INTO showID(f1) VALUES(333)"
xnn.Execute "INSERT INTO showID(f1) VALUES(444)"

' get @IDENTITY
Debug.Print "After 3 inserts, ADO returns ID=: ", xnn.Execute("SELECT
@@Identity").Fields(0).Value

' insert new record through DAO
db.Execute "INSERT INTO showID(f1) VALUES(555)"

' observe ADO is still ok, as long as it is concerned with itself
Debug.Print "After 3 (ADO) +1 (DAO) inserts, ADO returns ID=: ",
xnn.Execute("SELECT @@Identity").Fields(0).Value


Dim rst As DAO.Recordset: Set rst = db.OpenRecordset("SELECT * FROM showID")
Dim uvw As New ADODB.Recordset

uvw.Open "showID", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic, adCmdTableDirect


rst.MoveNext ' move to the second record
Debug.Print "DAO rst, before AddNew, is at id=", rst.Fields("id")
rst.AddNew
Debug.Print "DAO rst, after AddNew, is at id=", rst.Fields("id")
rst.Fields("f1") = 666
rst.Update
Debug.Print "DAO rst, after the update, is back at id=",
rst.Fields("id")

uvw.MoveNext
Debug.Print "ADO uvw, before the AddNew, is at id=", uvw.Fields("id")


uvw.AddNew
uvw.Fields("f1") = 777
uvw.Update
Debug.Print "ADO uvw, after the update, is at id=", uvw.Fields("id")
Debug.Print "ADO, and then, reports @@identity=", xnn.Execute("SELECT
@@identity").Fields(0).Value
uvw.Update

End Sub

 

[Jamie Collins]

Not it Jet ;-) SQL Server BOL describes @@IDENTITY as a *function*. A
user cannot explicitly change the value returned by @@IDENTITY, even
via a trigger. I guess you mean that a T-SQL statement may cause a
trigger to fire that may cause an further IDENTITY value to be
generated

Note that in Jet a single SQL statement can cause multiple IDENTITY
values to be generated across multiple tables and (I guess) @@IDENTITY
will return the most recent (transaction time) generated value e.g.

Sub mainer()
Kill Environ$("temp") & "\DropMe.mdb"
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
..Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
Environ$("temp") & "\DropMe.mdb"
With .ActiveConnection
Dim sql As String
sql = _
"CREATE TABLE Test1 (" & vbCr & "ID1 INTEGER" & _
" IDENTITY(1, 1) NOT NULL UNIQUE," & _
" " & vbCr & "f1 INTEGER)"
..Execute sql
sql = _
"CREATE TABLE Test2 (" & vbCr & "test1_ID1" & _
" INTEGER NOT NULL REFERENCES Test1" & _
" (ID1), " & vbCr & "ID2 IDENTITY(300, 3)" & _
" NOT NULL UNIQUE, " & vbCr & "f2 INTEGER)"
..Execute sql
sql = _
"CREATE VIEW Test1Test2" & vbCr & "(ID1, f1," & _
" Test1_ID1, ID2, f2)" & vbCr & "AS " & vbCr & "SELECT" & _
" Test1.ID1, Test1.f1, Test2.Test1_ID1," & _
" Test2.ID2, Test2.f2" & vbCr & "FROM Test1" & vbCr & "INNER" & _
" JOIN Test2" & vbCr & "ON Test1.ID1 = Test2.Test1_ID1"
..Execute sql
sql = _
"INSERT INTO Test1Test2 (f1, f2)" & _
" VALUES (1, 1)"
..Execute sql
Dim rs
Set rs = .Execute( _
"SELECT @@IDENTITY;")
MsgBox rs.GetString
End With
Set .ActiveConnection = Nothing
End With
End Sub

PS Out of curiosity I changed the query to

SELECT @@IDENTITY, * FROM Test1Test2;

and got the very impressive error message, "Catastrophic failure" <g>.

Jamie.

--

 

[NJS]

Thanks Jaime & Michael.
So, in closing, the only way (in DAO to Access BE) to get the last ID (in a
table) for a speciific session is to include a machine/user key in the prior
insert statement?

cheers.

 

[Jamie Collins]

So, in closing, the only way (in DAO to Access BE) to get the last ID (in a
table) for a speciific session is to include a machine/user key in the prior
insert statement?

Sounds like a kludge to me. If ADO can give you what you want,
consider using ADO; perhaps even consider whether you need a different
value auto-generation algorithm to Autonumber e.g.

How To Implement Multiuser Custom Counters in Jet 4.0 and ADO 2.1
http://support.microsoft.com/kb/240317/en-us

"Because the Microsoft Jet database engine has a read cache and lazy
writes, you can get duplicate values in your custom counter field if
two applications add records in less time than it takes for the cache
to refresh and the lazy-write mechanism to flush to disk. This article
presents a method that takes these factors into account..."

Jamie.

--

 

[NJS]

Thanks Jamie, I wasn't aware of the 'lazy write' issue. Does this also apply
when using the docmd.runsql with transaction property set true?

It still sounds as if the easiest way is another field storing the
machine/userID. This way the 'max (autonum) where' function will return valid
data (assuming same user doesn't insert multiple records within the 'lazy'
limitation which is not possible in my case.

cheers, and have a good one.