Re: VB6 app may need to generate its own ACCESS AUTONUMBER VALUES???

Alan Mailer <clarityassoc@xxxxxxxxxxxxx> wrote in
news:to5gg2libi8nkhk6ftc4m896vfkqjo711u@xxxxxxx:

On Tue, 12 Sep 2006 20:17:59 -0500, "David W. Fenton"
<XXXusenet@xxxxxxxxxxxxxxxxxxx> wrote:

Alan Mailer <clarityassoc@xxxxxxxxxxxxx> wrote in
news:e3jdg2lh4iuilp8a0gkk1d1g70c95pgivt@xxxxxxx:

- I'm curious: Again, since I am using a VB6 app to perform
Access record alteration, why would having a separate table for
AutoNumbers work better in a multi-user environment? What if
two different Users happen to trigger a process that reads the
same AutoNumber table at the same moment... wouldn't both those
users still get the same (and now potentially dangerously
redundant) number?

The generation of the Autonumber is done by *Jet*, which is smart
enough to deal with multiple users at the engine level. If you
generate the number yourself, you then have to duplicate the
multi-user functionality that's already built into Jet, just
waiting for you to use it.

The easiest way to solve your problem is to change the original
table to use a new Autonumber field as its PK, and forget about
creating a separate table -- it's much easier to do this
structural change once in the actual table involved than to do it
in a separate table.

Well, I will certainly consider this, but I guess I should make it
clear that this Access database contains more than 50 tables....
This is NOT a critique of your suggestion, but it seems like
taking the suggestion would involve me re-inventing Primary Key
values for every table in the Access database... **AND**... then
making sure that every table in which every Primary Key appears as
a foreign key is changed in value to the NEW Primary Key value.

That's a one-time operation that should take about an hour, no? You
could also write code to do it, using the relationships collection
to figure out what the child tables are.

I'm a little afraid of doing this, because I'm afraid I might miss
something and leave a couple tables with now-obsolete foreign key
values.

???

How could that happen?

If you're worried about it, do it in code wrapped in a transaction,
and before committing, using the transaction workspace, check for
unmatched records in the child table.

If you have any suggestions as to how I could make sure that was
less likely to happen, I'd sure listen to it! For example, is
there a way to get Access to list every table in the current
database that contains a field called "ContactID"? (I'd even be
willing to write VB code that would seek this out, if possible).

All you *really* want is all the tables that are *related* to the
Contacts table on the ContactID field, and that should be in the
relationships collection.

Again, please don't take the above as a criticism of your advice.
I'm just voicing some insecurity about it. Thanks again for your
help.

Well, I don't see doing what I suggested as a big deal at all. I've
done it many times and it's just not that hard. And it would solve
your problem permanently and require no working around multi-user
issues in generating your own sequence numbers.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
.

Relevant Pages

  • Re: VB6 app may need to generate its own ACCESS AUTONUMBER VALUES???
    ... AutoNumbers work better in a multi-user environment? ... AutoNumber table at the same moment... ... creating a separate table -- it's much easier to do this structural ... suggestion would involve me re-inventing Primary Key values for every ...
    (comp.databases.ms-access)
  • Re: how to set autonumber primary key combining from several tables
    ... If you are going to use separate tables for each product (do your products ... tables would use, as a primary key, a LongInt ... field that holds the value of the Autonumber. ... I have set up a Client table, an Insurance Company table, ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Multi-Field Primary Key
    ... Surrogate numeric keys do solve a lot of technical ... problems for database and database application developers and many seasoned ... I recognise *three* uses for autonumber. ... "Although a primary key isn't required, ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Concatenate and Null Values -- Features
    ... Tony Toews dislikes cascade deletes as well as cascade updates, ... fence regarding the use of natural versus surrogate (autonumber) keys. ... Database Normalization Tips ... For optimal database design and performance, the primary key of a table ...
    (microsoft.public.access.reports)
  • Re: Autonumber Fields
    ... this special meaning is clustered index. ... believe that a clustered index is a requirement for a primary key. ... questions don't understand the purpose of an autonumber primary key. ...
    (microsoft.public.access.tablesdbdesign)