Access Database get Autoincrement Value

[cpopham]

I am inserting rows into an Access database. How can I get the primary key which is an autoincrement field for the new row(s)? I have came up with and interesting way of doing it, but it will not work on already built databases. I use a random number generator between 1 and 100,000. i then insert this number in a field along with the rest of my row data. I then use a Select statement to find this value and return the primary key value. I then set this random number to 0 in the database just in case the number ever shows up again. But there has to be an easier way, but I have not been able to find it.

 

Chester

[pelikan]

jeez louise! talk about ugly hacks!

 

if you are using Access 2000 (Jet 4.0) and you are inserting via SQL INSERT INTO statement - you can use the SELECT @@IDENTITY query to retrieve new aoutoincrement values - it returns the last auto value generated on your connection.

[cpopham]

Isn't @@Indentity a SQL thing? I did not think that it would work with Access?

 

Chester

[pelikan]

Access is a wrapper for Jet 4.0 - @@IDENTITY syntax is used by Jet

[cpopham]

Will this work if multiple users are connecting to the database and also inserting rows into the table? Or would it just return the highest value?

 

Chester

[bri189a]

Access is a wrapper for Jet 4.0 - @@IDENTITY syntax is used by Jet

I know many people who have tried to get this to work unsuccessfully' date=' inlcuding myself ([i']I'm not saying that it can't be done, just haven't gotton it to work myself[/i]). The only solution we've had for this problem is to use the OnRowUpdated Event to get the new ID (via a seperate query (which is ugly, thus we don't use Access and use SQL Server)). I would like to see a functional example of @@IDENTITY working using Access... in other words for those of you who don't know what that means, it's one query (an INSERT) that has an output parameter that gives you back the new AutoGenerated value from the table. I know many people who I could send this to who would love you for it, including myself.

[cpopham]

Well, we are kind of stuck with Access, so I am having to try some things. I did get it to work, but I do not know how well it will work in real time. As soon as my dataset updates the database, and while the connection is still open I run the @@IDENTITY agianst it. That seems to work. Now my only other option is using my Random number bit, that is bad and if there is already an active database you would have to add another column to it.

 

Chester