Re: Cascade Update using only queries
- From: "Jeff Boyce" <nonsense@xxxxxxxxxxxx>
- Date: Wed, 21 Jan 2009 08:06:01 -0800
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" <JimMurray@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:D9A825CF-58A6-4875-9EAE-4F1E2F3D32F1@xxxxxxxxxxxxxxxx
Yes, I'm working directly in the tables and all of my foreign keys are
long
integers.
Jim
"Jeff Boyce" wrote:
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" <JimMurray@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E4DB4A39-D47C-4A50-8AD0-4FE74B52FA2B@xxxxxxxxxxxxxxxx
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];
.
- Follow-Ups:
- Re: Cascade Update using only queries
- From: Jim Murray
- Re: Cascade Update using only queries
- References:
- Cascade Update using only queries
- From: Jim Murray
- Re: Cascade Update using only queries
- From: Jeff Boyce
- Re: Cascade Update using only queries
- From: Jim Murray
- Cascade Update using only queries
- Prev by Date: Re: Paramater Query with drop down menu
- Next by Date: Find Uppercase letters
- Previous by thread: Re: Cascade Update using only queries
- Next by thread: Re: Cascade Update using only queries
- Index(es):
Relevant Pages
|
