By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,414 Members | 2,903 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,414 IT Pros & Developers. It's quick & easy.

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

P: n/a
A project I'm working on is going to use VB6 as a front end. The back
end is going to be pre-existing MS Access 2002 database tables which
already have records in them *but do not have any AutoNumber* fields
in them.

Correct me if I'm wrong, but I'm assuming this means that I cannot now
alter these existing Access tables and change their primary key to an
"AutoNumber" type.

If I'm right about this, I need some suggestions as to the most
efficient and reliable way of creating a VB6 function that will return
a number that represents the next available primary number in a
particular table.

In other words, I *think* (again, correct me if I'm wrong) I need to
create an AutoNumber generating function in VB6.

One of the issues I'm grappling with is that my VB app may be used by
more than 10 people at a time. So, in addition to whatever
suggestions you have as to how I go about what I've described above,
it would help me to know how I would go about making sure that no two
simultaneous users of my VB app will ever generate identical
'autonumber' values; because I obviously need the primary field in
question to always have a unique value for each record.

By the way, for what it's worth I'm probably going to use ADO in the
VB app.

Thanks in advance for any help you can provice.
Sep 11 '06 #1
Share this Question
Share on Google+
11 Replies


P: n/a

Alan Mailer wrote:
A project I'm working on is going to use VB6 as a front end. The back
end is going to be pre-existing MS Access 2002 database tables which
already have records in them *but do not have any AutoNumber* fields
in them.

Correct me if I'm wrong, but I'm assuming this means that I cannot now
alter these existing Access tables and change their primary key to an
"AutoNumber" type.

If I'm right about this, I need some suggestions as to the most
efficient and reliable way of creating a VB6 function that will return
a number that represents the next available primary number in a
particular table.

In other words, I *think* (again, correct me if I'm wrong) I need to
create an AutoNumber generating function in VB6.

One of the issues I'm grappling with is that my VB app may be used by
more than 10 people at a time. So, in addition to whatever
suggestions you have as to how I go about what I've described above,
it would help me to know how I would go about making sure that no two
simultaneous users of my VB app will ever generate identical
'autonumber' values; because I obviously need the primary field in
question to always have a unique value for each record.

By the way, for what it's worth I'm probably going to use ADO in the
VB app.

Thanks in advance for any help you can provice.
You can always add a new autonumber field to the table. You can even
go so far as to add a new autonumber field, delete the original
non-autonumber key field, and rename your new field to the old key
field's name. In this case you will have to make sure that you update
any foreign keys appropriately however, i.e., you will need to make
sure that all records in related tables still match the appropriate
records in your now autonumbered table. One has to wonder how primary
keys were generated in that database prior to your application however.

I wouldn't recommend ADO unless you envision upsizing your back end
database in the near future or absolutely need functionality that DAO
does not provide. DAO works more efficiently with a Jet database than
ADO does.

Hope this helps,
Bruce

Sep 11 '06 #2

P: n/a
Nope, you can still add AutoNumbers. Access will automatically
genereate values for all records in the database. You can do this by
issuing an ALTER TABLE DDL statement, or just opening the database and
making the change yourself.

Chris Nebinger
Alan Mailer wrote:
A project I'm working on is going to use VB6 as a front end. The back
end is going to be pre-existing MS Access 2002 database tables which
already have records in them *but do not have any AutoNumber* fields
in them.

Correct me if I'm wrong, but I'm assuming this means that I cannot now
alter these existing Access tables and change their primary key to an
"AutoNumber" type.

If I'm right about this, I need some suggestions as to the most
efficient and reliable way of creating a VB6 function that will return
a number that represents the next available primary number in a
particular table.

In other words, I *think* (again, correct me if I'm wrong) I need to
create an AutoNumber generating function in VB6.

One of the issues I'm grappling with is that my VB app may be used by
more than 10 people at a time. So, in addition to whatever
suggestions you have as to how I go about what I've described above,
it would help me to know how I would go about making sure that no two
simultaneous users of my VB app will ever generate identical
'autonumber' values; because I obviously need the primary field in
question to always have a unique value for each record.

By the way, for what it's worth I'm probably going to use ADO in the
VB app.

Thanks in advance for any help you can provice.
Sep 11 '06 #3

P: n/a
Thank you to all who have attempted to lend a hand to my issue so far.
This group continues to be a wonderful resource!

After reading the responses, I'm thinking I need to be a more specific
about what I need and don't need:

I am not just looking to add a new column to an existing table that
happens to use AutoNumber format. I believe I need to start
programmatically providing an *existing* primary ('Number' data type)
field with unique numbers that the field itself cannot (because of the
way the table was originally designed) automatically generate.

Let me put it this way: Imagine that there are existing records in
tblContact. That table's primary ID field is ContactID.
Unfortunately, the previous programmer did not establish ContactID as
an AutoNumber field. Nevertheless, there are now hundreds of records
which already exist in tblContact; all with their own unique
ContactIDs, all of which have been used as foreign keys in other
tables. In other words, these existing Contact records cannot now
have their ContactIDs changed.

My mandate is that whenever the user uses my VB6 app to create a new
Contact record, I need to append a record to tblContact which will
have a UNIQUE ContactID. My app needs to take on that responsibility,
because the Access table cannot.

Ideally, I wish I could just change the existing ContactID's field
property to AutoNumber and know that new records would forever
afterwards increment starting from the largest existing ContactID in
tblContacts... but I'm under the impression Access won't allow me to
do this.

This is why I'm thinking I have to now write a routine so that VB6 can
(for example) look at the MAX ContactID in tblContacts before VB6
appends a new record to that table... so that VB6 can then provide
that new record with a ContactID value that is the equivalent of that
'MAX' plus 1.

Unless I'm missing something (and God knows, I might be), a new
AutoNumbering column will *not* give me what I need... for one thing
because that new column will automatically contain its own 'default'
values for records whose 'ContactID' values are already established
and should not be changed.

I hope I'm not over-explaining here, or sound like I'm complaining
about the answers I've received so far. I'm just trying to be a bit
more specfic about what I need.

Again, I would need to write the VB6 routine 'auto increment' routine
in a way that insures that no two simultaneous users of the app will
ever generate identical ContactIDs.

Thanks again for all your input. I look forward to your further
responses.

On 11 Sep 2006 11:36:46 -0700, "ch************@gmail.com"
<ch************@gmail.comwrote:
>Nope, you can still add AutoNumbers. Access will automatically
genereate values for all records in the database. You can do this by
issuing an ALTER TABLE DDL statement, or just opening the database and
making the change yourself.

Chris Nebinger
Alan Mailer wrote:
>A project I'm working on is going to use VB6 as a front end. The back
end is going to be pre-existing MS Access 2002 database tables which
already have records in them *but do not have any AutoNumber* fields
in them.

Correct me if I'm wrong, but I'm assuming this means that I cannot now
alter these existing Access tables and change their primary key to an
"AutoNumber" type.

If I'm right about this, I need some suggestions as to the most
efficient and reliable way of creating a VB6 function that will return
a number that represents the next available primary number in a
particular table.

In other words, I *think* (again, correct me if I'm wrong) I need to
create an AutoNumber generating function in VB6.

One of the issues I'm grappling with is that my VB app may be used by
more than 10 people at a time. So, in addition to whatever
suggestions you have as to how I go about what I've described above,
it would help me to know how I would go about making sure that no two
simultaneous users of my VB app will ever generate identical
'autonumber' values; because I obviously need the primary field in
question to always have a unique value for each record.

By the way, for what it's worth I'm probably going to use ADO in the
VB app.

Thanks in advance for any help you can provice.
Sep 11 '06 #4

P: n/a
On Mon, 11 Sep 2006 21:47:58 GMT, Alan Mailer <cl**********@earthlink.net>
wrote:
Nz(DMax("FieldName","TableName"))+1

>Thank you to all who have attempted to lend a hand to my issue so far.
This group continues to be a wonderful resource!

After reading the responses, I'm thinking I need to be a more specific
about what I need and don't need:

I am not just looking to add a new column to an existing table that
happens to use AutoNumber format. I believe I need to start
programmatically providing an *existing* primary ('Number' data type)
field with unique numbers that the field itself cannot (because of the
way the table was originally designed) automatically generate.

Let me put it this way: Imagine that there are existing records in
tblContact. That table's primary ID field is ContactID.
Unfortunately, the previous programmer did not establish ContactID as
an AutoNumber field. Nevertheless, there are now hundreds of records
which already exist in tblContact; all with their own unique
ContactIDs, all of which have been used as foreign keys in other
tables. In other words, these existing Contact records cannot now
have their ContactIDs changed.

My mandate is that whenever the user uses my VB6 app to create a new
Contact record, I need to append a record to tblContact which will
have a UNIQUE ContactID. My app needs to take on that responsibility,
because the Access table cannot.

Ideally, I wish I could just change the existing ContactID's field
property to AutoNumber and know that new records would forever
afterwards increment starting from the largest existing ContactID in
tblContacts... but I'm under the impression Access won't allow me to
do this.

This is why I'm thinking I have to now write a routine so that VB6 can
(for example) look at the MAX ContactID in tblContacts before VB6
appends a new record to that table... so that VB6 can then provide
that new record with a ContactID value that is the equivalent of that
'MAX' plus 1.

Unless I'm missing something (and God knows, I might be), a new
AutoNumbering column will *not* give me what I need... for one thing
because that new column will automatically contain its own 'default'
values for records whose 'ContactID' values are already established
and should not be changed.

I hope I'm not over-explaining here, or sound like I'm complaining
about the answers I've received so far. I'm just trying to be a bit
more specfic about what I need.

Again, I would need to write the VB6 routine 'auto increment' routine
in a way that insures that no two simultaneous users of the app will
ever generate identical ContactIDs.

Thanks again for all your input. I look forward to your further
responses.

On 11 Sep 2006 11:36:46 -0700, "ch************@gmail.com"
<ch************@gmail.comwrote:
>>Nope, you can still add AutoNumbers. Access will automatically
genereate values for all records in the database. You can do this by
issuing an ALTER TABLE DDL statement, or just opening the database and
making the change yourself.

Chris Nebinger
Alan Mailer wrote:
>>A project I'm working on is going to use VB6 as a front end. The back
end is going to be pre-existing MS Access 2002 database tables which
already have records in them *but do not have any AutoNumber* fields
in them.

Correct me if I'm wrong, but I'm assuming this means that I cannot now
alter these existing Access tables and change their primary key to an
"AutoNumber" type.

If I'm right about this, I need some suggestions as to the most
efficient and reliable way of creating a VB6 function that will return
a number that represents the next available primary number in a
particular table.

In other words, I *think* (again, correct me if I'm wrong) I need to
create an AutoNumber generating function in VB6.

One of the issues I'm grappling with is that my VB app may be used by
more than 10 people at a time. So, in addition to whatever
suggestions you have as to how I go about what I've described above,
it would help me to know how I would go about making sure that no two
simultaneous users of my VB app will ever generate identical
'autonumber' values; because I obviously need the primary field in
question to always have a unique value for each record.

By the way, for what it's worth I'm probably going to use ADO in the
VB app.

Thanks in advance for any help you can provice.
Wayne Gillespie
Gosford NSW Australia
Sep 11 '06 #5

P: n/a
Alan Mailer <cl**********@earthlink.netwrote in
news:v4********************************@4ax.com:
Let me put it this way: Imagine that there are existing records
in tblContact. That table's primary ID field is ContactID.
Unfortunately, the previous programmer did not establish ContactID
as an AutoNumber field. Nevertheless, there are now hundreds of
records which already exist in tblContact; all with their own
unique ContactIDs, all of which have been used as foreign keys in
other tables. In other words, these existing Contact records
cannot now have their ContactIDs changed.
If I were taking over an application in this state, I'd do this (a
one-time process):

1. add an AutoNumber column.

2. remove the relationship between this table and its child tables.

3. run update queries to change the foreign keys to match the new
AutoNumber column's values.

4. delete the original ContactID field (or rename it).

5. name the AutoNumber field to ContactID.

6. restore the relationship between the foreign keys in the child
tables and the replacement ContactID field.

Since AutoNumbers should never have meaning, none of this should
matter to the end users. And you won't have to do this but once, and
won't need to write any code to create the numbers yourself, which
is always subject to multi-user issues.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Sep 12 '06 #6

P: n/a
The problem you're going to face is multi-users. Either us the DMAX
function posted below on the Before Insert (be sure to error trap for
existing items), or create a new table just to save autonumbers. When
a record is created, add the new autonumber to that table so it is
reserved. If you're using OO techniques, it shouldn't be that
difficult.

Chris Nebinger
Wayne Gillespie wrote:
On Mon, 11 Sep 2006 21:47:58 GMT, Alan Mailer <cl**********@earthlink.net>
wrote:
Nz(DMax("FieldName","TableName"))+1

Thank you to all who have attempted to lend a hand to my issue so far.
This group continues to be a wonderful resource!

After reading the responses, I'm thinking I need to be a more specific
about what I need and don't need:

I am not just looking to add a new column to an existing table that
happens to use AutoNumber format. I believe I need to start
programmatically providing an *existing* primary ('Number' data type)
field with unique numbers that the field itself cannot (because of the
way the table was originally designed) automatically generate.

Let me put it this way: Imagine that there are existing records in
tblContact. That table's primary ID field is ContactID.
Unfortunately, the previous programmer did not establish ContactID as
an AutoNumber field. Nevertheless, there are now hundreds of records
which already exist in tblContact; all with their own unique
ContactIDs, all of which have been used as foreign keys in other
tables. In other words, these existing Contact records cannot now
have their ContactIDs changed.

My mandate is that whenever the user uses my VB6 app to create a new
Contact record, I need to append a record to tblContact which will
have a UNIQUE ContactID. My app needs to take on that responsibility,
because the Access table cannot.

Ideally, I wish I could just change the existing ContactID's field
property to AutoNumber and know that new records would forever
afterwards increment starting from the largest existing ContactID in
tblContacts... but I'm under the impression Access won't allow me to
do this.

This is why I'm thinking I have to now write a routine so that VB6 can
(for example) look at the MAX ContactID in tblContacts before VB6
appends a new record to that table... so that VB6 can then provide
that new record with a ContactID value that is the equivalent of that
'MAX' plus 1.

Unless I'm missing something (and God knows, I might be), a new
AutoNumbering column will *not* give me what I need... for one thing
because that new column will automatically contain its own 'default'
values for records whose 'ContactID' values are already established
and should not be changed.

I hope I'm not over-explaining here, or sound like I'm complaining
about the answers I've received so far. I'm just trying to be a bit
more specfic about what I need.

Again, I would need to write the VB6 routine 'auto increment' routine
in a way that insures that no two simultaneous users of the app will
ever generate identical ContactIDs.

Thanks again for all your input. I look forward to your further
responses.

On 11 Sep 2006 11:36:46 -0700, "ch************@gmail.com"
<ch************@gmail.comwrote:
>Nope, you can still add AutoNumbers. Access will automatically
genereate values for all records in the database. You can do this by
issuing an ALTER TABLE DDL statement, or just opening the database and
making the change yourself.

Chris Nebinger
Alan Mailer wrote:
A project I'm working on is going to use VB6 as a front end. The back
end is going to be pre-existing MS Access 2002 database tables which
already have records in them *but do not have any AutoNumber* fields
in them.

Correct me if I'm wrong, but I'm assuming this means that I cannot now
alter these existing Access tables and change their primary key to an
"AutoNumber" type.

If I'm right about this, I need some suggestions as to the most
efficient and reliable way of creating a VB6 function that will return
a number that represents the next available primary number in a
particular table.

In other words, I *think* (again, correct me if I'm wrong) I need to
create an AutoNumber generating function in VB6.

One of the issues I'm grappling with is that my VB app may be used by
more than 10 people at a time. So, in addition to whatever
suggestions you have as to how I go about what I've described above,
it would help me to know how I would go about making sure that no two
simultaneous users of my VB app will ever generate identical
'autonumber' values; because I obviously need the primary field in
question to always have a unique value for each record.

By the way, for what it's worth I'm probably going to use ADO in the
VB app.

Thanks in advance for any help you can provice.

Wayne Gillespie
Gosford NSW Australia
Sep 12 '06 #7

P: n/a
On 12 Sep 2006 07:47:19 -0700, "ch************@gmail.com"
<ch************@gmail.comwrote:

Thanks for responding Chris.
>The problem you're going to face is multi-users. Either us the DMAX
function posted below on the Before Insert (be sure to error trap for
existing items),
As to your DMAX suggestion, I don't think this applies to my
particular scenario because I'll be using a VB6 app to append, update,
delete (etc) records in Access, so (unless I'm wrong about this) any
internal Access 'Before Insert' rules will not apply to what a
separate app is doing to the records. As I say, if I'm wrong about
this, please let me know.
>or create a new table just to save autonumbers. When
a record is created, add the new autonumber to that table so it is
reserved. If you're using OO techniques, it shouldn't be that
difficult.
This is intriguing, but I want to make sure I understand your
suggestion about creating a new table just to save autonumbers.

- In this scenario, are you suggesting I make a separate AutoNumber
table for *each* table in the current Access database? (I'm not
complaining, I'm just trying to clarify).

- 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?

I really appreciate your help... and your patience.
Sep 12 '06 #8

P: n/a
You're right, the BeforeInsert event fires on an Access form. You
needn't worry about this.

Let me fully explain the problems with creating your own AutoNumber.
On a regular autonumber field, as soon as the record is created (but
not yet saved), it is assigned an autonumber. This is to prevent two
new records grabbing the same autonumber. The problem that alot of
people run into is if you create a new record, but do not commit the
record, that Autonumber is lost forever, leading to gaps in the
sequence. As autonumbers are not to be meaningful, this isn't a
problem as long as you are not trying to audit why a record seemingly
disappeared.

So, from your application's standpoint, when do you want to create the
autonumber, on new record creation, or on new record commit? If it's
on the Commit, then you probably will not have an issue with using the
DMAX function:

Insert Into Table (MyCounter, Field1, Field2) Values (Dmax
("MyCounter","Table")+1,"Value1","Value2")

The only problem is if you change data providers from Access to another
which does not support the DMAX function.

If you need to get an autonumber as soon as the record is created (The
ID is needed by the user for something), then you have the problem with
multiple users all creating records with the same number. In that
case, as soon as the record is created, get the last value from the
autonumber table, add one, save back (trapping for errors), and use
that number.

You don't necessarily need a seperate autonumber table for each table
you want to implement this on. You could have one with:

Table AutoNumber
Employee 1
Employee 2
Company 300
etc.
Then,

Select Max (AutoNumber), Table from AutoNumbers Group By Table

HTH,
Chris Nebinger


Alan Mailer wrote:
On 12 Sep 2006 07:47:19 -0700, "ch************@gmail.com"
<ch************@gmail.comwrote:

Thanks for responding Chris.
The problem you're going to face is multi-users. Either us the DMAX
function posted below on the Before Insert (be sure to error trap for
existing items),

As to your DMAX suggestion, I don't think this applies to my
particular scenario because I'll be using a VB6 app to append, update,
delete (etc) records in Access, so (unless I'm wrong about this) any
internal Access 'Before Insert' rules will not apply to what a
separate app is doing to the records. As I say, if I'm wrong about
this, please let me know.
or create a new table just to save autonumbers. When
a record is created, add the new autonumber to that table so it is
reserved. If you're using OO techniques, it shouldn't be that
difficult.

This is intriguing, but I want to make sure I understand your
suggestion about creating a new table just to save autonumbers.

- In this scenario, are you suggesting I make a separate AutoNumber
table for *each* table in the current Access database? (I'm not
complaining, I'm just trying to clarify).

- 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?

I really appreciate your help... and your patience.
Sep 12 '06 #9

P: n/a
Alan Mailer <cl**********@earthlink.netwrote in
news:e3********************************@4ax.com:
- 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.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Sep 13 '06 #10

P: n/a
On Tue, 12 Sep 2006 20:17:59 -0500, "David W. Fenton"
<XX*******@dfenton.com.invalidwrote:
>Alan Mailer <cl**********@earthlink.netwrote in
news:e3********************************@4ax.com :
>- 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.

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.

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).

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.
Sep 13 '06 #11

P: n/a
Alan Mailer <cl**********@earthlink.netwrote in
news:to********************************@4ax.com:
On Tue, 12 Sep 2006 20:17:59 -0500, "David W. Fenton"
<XX*******@dfenton.com.invalidwrote:
>>Alan Mailer <cl**********@earthlink.netwrote in
news:e3********************************@4ax.co m:
>>- 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/
Sep 13 '06 #12

This discussion thread is closed

Replies have been disabled for this discussion.