How to get AutoNumber off Access 2003

[Ed_P]

Hello

I currently have a Access 2003 database, I am able to add a record to it, and the CustomerID field (which is auto generated by the Database) is correctly filled in by the Database. I am now trying to figure out a way to retreive this number to use it in another Table of the Same Database. Anyone have any examples or resources that I can use to find the best way that I can accomplish this task? Any help/resources that you can provide will be greatly appreciated

Thanks

[William Ryan eMVP]

Ed_P:
"Ed_P" <an*******@discussions.microsoft.com> wrote in message
news:7D**********************************@microsof t.com...

Hello,

I currently have a Access 2003 database, I am able to add a record to it, and the CustomerID field (which is auto generated by the Database) is
correctly filled in by the Database.

Set the Autoincrement value of the DataColumn to -1, set hte
AutoIncrementSeed to 0 and set the AutoIncrement property to True. This way
you'll never have two seperate instances of the app trying to send back the
same value. Then, on update, add a Select query at the end. To See this in
action, configure a DataAdapter using the DataAdapter Configuration wizard
and on the advanced options, check the Refresh DataSet option if it's not
checked. Assuming the wizard can generate the commands, you'll see that it
appends a Select statement at the end of the Update statements and that's
how it gets the value(s) back. You may think it's not efficient firing
another query after update and I won't disagree with you, but that's the
nature of the beast. Also, if you try to manage the Autoincrement values,
and you have a multi user app, you are probably asking for Primary Key
violations b/c they will almost assuredly write over each other (or attempt
to anyway before throwing an exception) unless you want to come up with some
pretty sophisiticated ways to work around it. Using the -1 scneario is
great b/c no other app will every add -1 so the db can always assign a legit
value.

If you have datarelations defined, when you find out the new value from the
db, it will change the negative number value that was added by the
datacolumn's autoincrmement property and update the child records.

Check out http://www.betav.com "Managing an @@Identity crisis" by Bill
vaughn under Articles, MSDN. He's talking about SQL Server but the same
principles apply.

HTH,

Bill

I am now trying to figure out a way to retreive this number to use it in
another Table of the Same Database. Anyone have any examples or resources
that I can use to find the best way that I can accomplish this task? Any
help/resources that you can provide will be greatly appreciated!
Thanks

[Chris Botha]

Here is another method - after the insert, execute the following select
statement, using the same connection:
SELECT @@IDENTITY
or
SELECT @@IDENTITY As TheNewId
It returns the last AutoNumber inserted (works for any table with a
AutoNumber, there are no parameters, etc, it returns the last AutoNumber
generated).

"Ed_P" <an*******@discussions.microsoft.com> wrote in message
news:7D**********************************@microsof t.com...

Hello,

I currently have a Access 2003 database, I am able to add a record to it, and the CustomerID field (which is auto generated by the Database) is
correctly filled in by the Database. I am now trying to figure out a way
to retreive this number to use it in another Table of the Same Database.
Anyone have any examples or resources that I can use to find the best way
that I can accomplish this task? Any help/resources that you can provide
will be greatly appreciated!
Thanks

[Ed_P]

Hello Chris

Thanks for the quick reply. I'll try what you mentioned, do you happen to have any sample source code to go along with your suggestion?

[Chris Botha]

Basically it is a simple select, instead of "Select * From MyTable", you do
a
"Select @@IDENTITY As TheNewId", the only difference there is no "From"
clause.
The select above will return 1 row, and the column name will be "TheNewId".

"Ed_P" <an*******@discussions.microsoft.com> wrote in message
news:7E**********************************@microsof t.com...

Hello Chris,

Thanks for the quick reply. I'll try what you mentioned, do you happen to

have any sample source code to go along with your suggestion?