query the last autonumber in the table during insert 3

[JeffSabat]

Help, pls...
i have a program that uses mssql for its database.In my program, i use a stored procedure to insert a datarow in the database wherein the stored procedure also returns the Last Identity number of the row being inserted.
Now, I am converting the database to Access however, there are no stored procedure in Access, so I have to do some programming in my program so that it would still be flexible. My problem now is, How can i get the last identity number (the autonumber field) being assigned to the datarow being inserted?
please help..

 

[cheerio]

I don't think you can.

For example, in a new table if you add 7 records and then delete the last one the last number apparently used is 6. However, Access knows the truth and will assign 8 next time.

In any event, it's very dangerous in a multiuser environment. Even if Access would tell you the next number available there is no guarantee it will be assigned to your record because another user could jump in first.

Autonumber has a number of problems. Consider carefully whether you really want to use it.

 

[MichaelRed]

see faq700/184




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[JeffSabat]

thanks for the reply...
however, michaelred, where can i find faq700/184?

cheerio,
what i really want is to know what number was assigned to the new record inserted?
because in MSSql,
it is possible to know the identity number assigned to the newly inserted data by the function @@Identity, ex.
--
use databasename
insert into tablename(fieldname) values('fieldvalue')
select @@identity
GO
--
so in this query,it will insert a value into tablename and return the identity number assigned to the newly inserted value...
my question is is this possible in MSAccess or is there an equivalent function of @@identity in msaccess..
thanks...

 

[cheerio]

Access is not SQL Server! There is no @@identity. The autonumber is assigned at the moment of update and even if you knew the next number available there is no guarantee that you will be the next person to add a record in a multiuser environment.

The faq link is faq700-184.

 

[ptpaton]

Jeff,

You can set up a select query using the Max funciton to do this.

Let's call your table tblData and your number field AutoNumber.

SELECT tblTable.AutoNumber, Max([AutoNumber]+1) AS NewAutoNumber
FROM tblTable
GROUP BY tblTable.FieldWhatever

Call it qrysMaxNumber

Using Max() + 1 will return the largest number in the AutoNumber Field and add one to it. Now, build a second append query that appends data to tblTable:
1. Bring qrysMaxNumber and pull down the NextAutoNumber field.
2. Append NextAutoNumber to your AutoNumber field in tblTables, and bring in any other data that you want.

In this manner, the database will always grab the largest number and add one. This guarentees that you're numbers will always increment by one, even if you've deleted records, etc.

Since implementing this, AutoNumber fields don't even exist to me.

Good luck!

-Patrick

Nine times out of ten, the simplest solution is the best one.

 

[HockeyGuy]

If you needed to know the highest autonumber assigned (the last record added) you could probably use the DMax function on the autonumber field. I think that the function is setup as follows:

MaxValue = DMax("FieldName", "TableName",["Criteria"])

If you don't provide criteria, the largest created number (last used number) will be returned.

HTH

Rick

 

[cmmrfrds]

In Access 2000 and above Microsoft provided an @@identity against the Access database. It has been covered in this Forum, so try a search. If no luck, check the Microsoft site. It is similar to how you would retrieve in SQL Server. If you are using ADO, then the new identity number is available when you insert the record.

 

[cmmrfrds]

Here is a link.

http://www.aspnet101.com/aspnet101/aspnet/codesample.aspx?code=accessidentity2

 

[JeffSabat]

thank you for all your replies.
Patrick, thanks for the solution...i haven't thought about it. probably because i'm looking for a solution that requires less code... and effort.
thanks most to "cmmrfrds" ....
it's just the answer that i wanted...
i'm not sure though what errors it would coz when it is use in a multi-user environment however, my idea is in my codes...i would place the command begin trans before the insert statement, then select the autonumber assign using the @@identity command, then place the command commit trans...

gconn.begintrans
gconn.execute "insert..."
grs.open "select @@identity...",gconn,....
gconn.committrans

i still have to try this code though today, i am just hoping it won't create any errors.. anyway, any comment and suggestion would greatly be appreciated...
again thank you very much to everyone..

 

[cmmrfrds]

I have assumed but not tested, that Microsoft implemented the @@identity in Access with the same rules as in SQL Server and it will gaurantee uniqueness in a multi-user environment. The Max(field) is not gauranteed to be unique in a multi-user environment depending on how it is used. I would not use max(field) to set foreign keys because of this.