Cascade Update using only queries

  • Thread starter Thread starter Jim Murray
  • Start date Start date
J

Jim Murray

I did not setup my Access / SQL to allow cascade updates. I'm trying to
accomplish this by query. My main table, tblPersons, has an autonumber
primary key. The target tables (11 of them) use this as a foreign key. I can
update through the query if each table has an entry with the PersonsID. The
query does not work if one table does not have an entry matching that
PersonsID. I could write a complex VB script checking each table for an entry
and dropping those that don't from the SQL script but this seems extreme. Is
there a simple way of doing this?
Sample of Query (only 3 tables)
UPDATE ((tblPersons LEFT JOIN tblActions ON tblPersons.PersonsID =
tblActions.PersonsID) LEFT JOIN tblAddresses ON tblPersons.PersonsID =
tblAddresses.PersonsID) LEFT JOIN tblAlias ON tblPersons.PersonsID =
tblAlias.PersonsID SET tblActions.PersonsID =
[Forms]![adminForm]![cboCombineToPersons], tblAddresses.PersonsID =
[Forms]![adminForm]![cboCombineToPersons], tblAlias.PersonsID =
[Forms]![adminForm]![cboCombineToPersons];
 
Are you working directly in the tables?

It is quite common to have a parent-child relationship between tables. As
it is quite common to use a main form/subform construction to help with data
entry.

It sounds like your "child" tables already have a foreign key to point back
to the parent table's primary key.

By the way, you didn't explicitly state this, so I need to ask ... you are
NOT using an Autonumber data type as your "foreign key" field, right? To
make the parent-child relationship work, you'd need to have a primary key in
the child table and a foreign key field of type LongInt to point back to the
parent record that "owns" it.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Yes, I'm working directly in the tables and all of my foreign keys are long
integers.
Jim

Jeff Boyce said:
Are you working directly in the tables?
It is quite common to have a parent-child relationship between tables. As
it is quite common to use a main form/subform construction to help with data
entry.

It sounds like your "child" tables already have a foreign key to point back
to the parent table's primary key.

By the way, you didn't explicitly state this, so I need to ask ... you are
NOT using an Autonumber data type as your "foreign key" field, right? To
make the parent-child relationship work, you'd need to have a primary key in
the child table and a foreign key field of type LongInt to point back to the
parent record that "owns" it.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jim Murray said:
I did not setup my Access / SQL to allow cascade updates. I'm trying to
accomplish this by query. My main table, tblPersons, has an autonumber
primary key. The target tables (11 of them) use this as a foreign key. I
can
update through the query if each table has an entry with the PersonsID.
The
query does not work if one table does not have an entry matching that
PersonsID. I could write a complex VB script checking each table for an
entry
and dropping those that don't from the SQL script but this seems extreme.
Is
there a simple way of doing this?
Sample of Query (only 3 tables)
UPDATE ((tblPersons LEFT JOIN tblActions ON tblPersons.PersonsID =
tblActions.PersonsID) LEFT JOIN tblAddresses ON tblPersons.PersonsID =
tblAddresses.PersonsID) LEFT JOIN tblAlias ON tblPersons.PersonsID =
tblAlias.PersonsID SET tblActions.PersonsID =
[Forms]![adminForm]![cboCombineToPersons], tblAddresses.PersonsID =
[Forms]![adminForm]![cboCombineToPersons], tblAlias.PersonsID =
[Forms]![adminForm]![cboCombineToPersons];
Click to expand...
Click to expand...
 
Access tables store data, Access forms (and reports) display data (and offer
a rich event environment).

Consider stepping away from the tables and using forms instead.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jim Murray said:
Yes, I'm working directly in the tables and all of my foreign keys are
long
integers.
Jim

Jeff Boyce said:
Are you working directly in the tables?
It is quite common to have a parent-child relationship between tables.
As
it is quite common to use a main form/subform construction to help with
data
entry.

It sounds like your "child" tables already have a foreign key to point
back
to the parent table's primary key.

By the way, you didn't explicitly state this, so I need to ask ... you
are
NOT using an Autonumber data type as your "foreign key" field, right? To
make the parent-child relationship work, you'd need to have a primary key
in
the child table and a foreign key field of type LongInt to point back to
the
parent record that "owns" it.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jim Murray said:
I did not setup my Access / SQL to allow cascade updates. I'm trying to
accomplish this by query. My main table, tblPersons, has an autonumber
primary key. The target tables (11 of them) use this as a foreign key.
I
can
update through the query if each table has an entry with the PersonsID.
The
query does not work if one table does not have an entry matching that
PersonsID. I could write a complex VB script checking each table for an
entry
and dropping those that don't from the SQL script but this seems
extreme.
Is
there a simple way of doing this?
Sample of Query (only 3 tables)
UPDATE ((tblPersons LEFT JOIN tblActions ON tblPersons.PersonsID =
tblActions.PersonsID) LEFT JOIN tblAddresses ON tblPersons.PersonsID =
tblAddresses.PersonsID) LEFT JOIN tblAlias ON tblPersons.PersonsID =
tblAlias.PersonsID SET tblActions.PersonsID =
[Forms]![adminForm]![cboCombineToPersons], tblAddresses.PersonsID =
[Forms]![adminForm]![cboCombineToPersons], tblAlias.PersonsID =
[Forms]![adminForm]![cboCombineToPersons];
Click to expand...
Click to expand...
Click to expand...
 
? Are you suggesting that I set up a form, populate it from a select query
that links all of the tables, list all of the foreign keys in text boxes and
set up a vb function to replace the values of those foreign keys with a new
foreign key?
? You've done this and it works?
Jim

Jeff Boyce said:
Access tables store data, Access forms (and reports) display data (and offer
a rich event environment).

Consider stepping away from the tables and using forms instead.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jim Murray said:
Yes, I'm working directly in the tables and all of my foreign keys are
long
integers.
Jim

Jeff Boyce said:
Are you working directly in the tables?
It is quite common to have a parent-child relationship between tables.
As
it is quite common to use a main form/subform construction to help with
data
entry.

It sounds like your "child" tables already have a foreign key to point
back
to the parent table's primary key.

By the way, you didn't explicitly state this, so I need to ask ... you
are
NOT using an Autonumber data type as your "foreign key" field, right? To
make the parent-child relationship work, you'd need to have a primary key
in
the child table and a foreign key field of type LongInt to point back to
the
parent record that "owns" it.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I did not setup my Access / SQL to allow cascade updates. I'm trying to
accomplish this by query. My main table, tblPersons, has an autonumber
primary key. The target tables (11 of them) use this as a foreign key.
I
can
update through the query if each table has an entry with the PersonsID.
The
query does not work if one table does not have an entry matching that
PersonsID. I could write a complex VB script checking each table for an
entry
and dropping those that don't from the SQL script but this seems
extreme.
Is
there a simple way of doing this?
Sample of Query (only 3 tables)
UPDATE ((tblPersons LEFT JOIN tblActions ON tblPersons.PersonsID =
tblActions.PersonsID) LEFT JOIN tblAddresses ON tblPersons.PersonsID =
tblAddresses.PersonsID) LEFT JOIN tblAlias ON tblPersons.PersonsID =
tblAlias.PersonsID SET tblActions.PersonsID =
[Forms]![adminForm]![cboCombineToPersons], tblAddresses.PersonsID =
[Forms]![adminForm]![cboCombineToPersons], tblAlias.PersonsID =
[Forms]![adminForm]![cboCombineToPersons];
Click to expand...
Click to expand...
Click to expand...
 
Jim

That doesn't sound like any forms I've built.

If you have "parent" and "child" tables, the way Access offers to (easily)
handle the relationships is a Main form/Subform.

The main form gets the parent table record, the subform gets the (related)
child table records.

Since, in Access, it all starts with the data, it would probably help folks
here offer more specific suggestions if you'd provide a more specific
description of your table structure. Examples help.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jim Murray said:
? Are you suggesting that I set up a form, populate it from a select query
that links all of the tables, list all of the foreign keys in text boxes
and
set up a vb function to replace the values of those foreign keys with a
new
foreign key?
? You've done this and it works?
Jim

Jeff Boyce said:
Access tables store data, Access forms (and reports) display data (and
offer
a rich event environment).

Consider stepping away from the tables and using forms instead.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jim Murray said:
Yes, I'm working directly in the tables and all of my foreign keys are
long
integers.
Jim

:
Are you working directly in the tables?
It is quite common to have a parent-child relationship between tables.
As
it is quite common to use a main form/subform construction to help
with
data
entry.

It sounds like your "child" tables already have a foreign key to point
back
to the parent table's primary key.

By the way, you didn't explicitly state this, so I need to ask ... you
are
NOT using an Autonumber data type as your "foreign key" field, right?
To
make the parent-child relationship work, you'd need to have a primary
key
in
the child table and a foreign key field of type LongInt to point back
to
the
parent record that "owns" it.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I did not setup my Access / SQL to allow cascade updates. I'm trying
to
accomplish this by query. My main table, tblPersons, has an
autonumber
primary key. The target tables (11 of them) use this as a foreign
key.
I
can
update through the query if each table has an entry with the
PersonsID.
The
query does not work if one table does not have an entry matching
that
PersonsID. I could write a complex VB script checking each table for
an
entry
and dropping those that don't from the SQL script but this seems
extreme.
Is
there a simple way of doing this?
Sample of Query (only 3 tables)
UPDATE ((tblPersons LEFT JOIN tblActions ON tblPersons.PersonsID =
tblActions.PersonsID) LEFT JOIN tblAddresses ON tblPersons.PersonsID
=
tblAddresses.PersonsID) LEFT JOIN tblAlias ON tblPersons.PersonsID =
tblAlias.PersonsID SET tblActions.PersonsID =
[Forms]![adminForm]![cboCombineToPersons], tblAddresses.PersonsID =
[Forms]![adminForm]![cboCombineToPersons], tblAlias.PersonsID =
[Forms]![adminForm]![cboCombineToPersons];
Click to expand...
Click to expand...
Click to expand...
 
Thanks for your time, I'm sorry I wasn't clear.

I have a tblPersons centered table structure. Most of my other tables use
the Primary Key from tblPersons as a foreign key. New events entered in the
tblEvents link back to an individual in the tblPersons. Sometimes the clerk
does not notice an existing person in the tblPersons and creates a new
tblPersons entry. They then use that new Primary Key as a foreign key in
tblEvents to enter the new event. My query replaces the new foreign key in
tblEvents with the earlier Primary Key from tblPersons. I have 11 tables to
update.
I've solved the problem by creating a query for each table's entry.

Jim
 
Back
Top