Re: Access Autonumber problem

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

If you insist on using Access, you'll find that JET engine's inability to
execute more than one operation at a time forces you to either fetch the
@@Identity value post INSERT or use another scheme. I've been writing a lot
about identity issues lately so check the archives of this list. I expect
that a better alternative would be to use a GUID as the row identifier. This
is also supported in Oracle (and SQL Server). In this case your application
simply generates a GUID in code and uses that as the PK in place of an
engine-generated identity value.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
Between now and Nov. 6th 2006 you can sign up for a substantial discount.
Look for the "Early Bird" discount checkbox on the registration form...
-----------------------------------------------------------------------------------------------------------------------


Microsoft MVP, Author, Mentor
Microsoft MVP
"dcali001" <dcali001@xxxxxxxxxxxxxxxx> wrote in message
news:2C933C31-415E-4F0B-BE34-E66091C4B42C@xxxxxxxxxxxxxxxx
I would use SQLServer Express if I could, but unfortunatly this is not the
primary application that the database uses. I am actually writing web
services to expose functionality for a VB6 application that already has a
fairly large customer base. We use Oracle for our larger customers and
Access for the smaller ones.

In traditional ADO, we would use something similar to the following:

---
rs!ID = CLng(Rnd() * -10000000)
rs.Update

rs.Resync adAffectCurrent, adResyncAllValues

rs!ID = rs!CounterID & Format(StoreInfo.StoreNum, "00")
---

I hate to believe that this is impossible in ADO.NET.

Dave

"Cor Ligthert [MVP]" wrote:

Dcali,

In fact everything, you get only back the latest ID. In SQLServer the ID
is
giving back automaticly in Access not. Therefore in my idea is eithter to
change to SQLServer (Express) or to refresh everytime your dataset
completely.

Although you have first to do an update of the dataset of course and can
than try to get the id, will the ident that you only be the latest, not
all
the ones that are new in the same dataset.

While the event that you are using is typical in a binding situation,
where
the datarow is updated from a binded control, it is than of course still
not
in the database.

Does not help much, but I hope a little bit.

Cor



.

Relevant Pages

  • Re: Moving full-text index to a different system - problems
    ... What I did was to restore the database into a database which had the db_id I ... > The Content Index for project <SQLServer SQL0001100005> cannot be loaded. ... > The previous crawl was reset, ...
    (microsoft.public.sqlserver.fulltext)
  • Moving full-text index to a different system - problems
    ... different database server wuthout losing their data or full-text index. ... The Search service has loaded project. ... The Content Index for project <SQLServer SQL0001100005> cannot be loaded. ... The previous crawl was reset, ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Importing .dat file
    ... You've obviously never written software for a bunch of clients ... It is not the primary responsibility of my users to do SQLServer. ... would stop using Excel for everything and start using a database; ... mean SQL Server Developers' edition costs $49; ...
    (microsoft.public.dotnet.languages.vb)
  • Re: sqlserver backup & recovery procedures
    ... have rights to the new database. ... >> filename (backup done). ... >> i copied this file to another system running sqlserver ... >> no service packs had been installed for sqlserver 2000. ...
    (microsoft.public.sqlserver.security)
  • Re: Two questions about moving data between a SQL DB and XML
    ... If you're simply transporting data, use DTS/SSIS or the SqlBulkCopy class. ... Edition) database. ... Hitchhiker's Guide to Visual Studio and SQL Server ... Between now and Nov. 6th 2006 you can sign up for a substantial discount. ...
    (microsoft.public.dotnet.framework.adonet)