Datasets and autonumber

[hog]

If I have an object that has been updated with user data and then the objects .Add method is called to create the record in the Access database then is there any easy way of getting the AutoNumber generated by Access other than finding a way to read back in the new record?

[Mehyar]

No hog, you can do that if only user is using your application at a time and thus you would fetch the maximum id from the Access database upon filling the dataset. But ofcourse if more than one user is using your application then this number would be meaningless since new rows are added all the time

[hog]

oh bums! a rethink me thinks:) Thnx

[Nerseus]

I heard differently awhile back. If you issue two separate calls to Access, one to do the INSERT and one to do the "SELECT @@IDENTITY" to get the last inserted AutoNumber value, it should work. This assumes each user has their own always-on connection to the DataBase (usually how you program against Access).

 

It's a bit easier in SQL Server since you can do the INSERT and SELECT @@IDENTITY in one calls, but it doesn't work in Access for dynamic SQL. If you're calling a stored proc in Access (which you must create using dynamic sql using something like "CREATE PROC...", you *might* be able to have the proc do an INSERT and SELECT, but I haven't tried it (nor have I tried creating procs in Access, though I hear-tell you can).

 

-Nerseus

[hog]

Thx Nerseus:)

 

I have decided to take a different approach to solve this issue. However you points will be noted as this is the second time I've required this, so you never know:) :)