Hello,
What is the difference/advantages either way between defining join properties "on the fly" in the Design Query View vs. permanent definition under the Tool-Relationships menu procedure?

It seems that I can define them on the fly using the following code without any setting of primary keys (equivalent to doing so in the Design query view), while setting of primary keys is necessary for permanent definition under the Tool-Relationships menu procedure. Is there any danger in not setting the primary keys but just doing the join and make table (later export table) on the fly? If there is no danger, why ever bother with the Tools-Relationships menu procedure?

Dim strSQL As String
strSQL = "SELECT zmax2.*, rooting2.* " & _
"INTO temp " & _
"FROM zmax2 LEFT OUTER JOIN rooting2 " & _
"ON zmax2.musym = rooting2.musym"

'Turn off warning message.
DoCmd.SetWarnings False

DoCmd.RunSQL strSQL

'Re-enable warning message
DoCmd.SetWarnings True


RichardA

Hi Richard,

These are actually two fundamentally different things.
The relationships window allows you to set relationships
for the purpose of preserving data integrity. By doing
so, you can instruct Access to do certain things such as
not accepting a foreign key value that does not exist in
the primary table, deleting records from detail tables
when the corresponding record in the master table is
deleted, etc.

In queries, you also define relationships between tables,
but only for the purposes of defining the output that the
query will provide. True, that by default the query
builder imports the relationships that you have set in
the relationships window (actually this may be an option,
I can't remember offhand), but these can be changed
according to the needs for each individual query. The
relationships defined in queries have no impact on how
Access treats the relationships between tables regarding
data integrity, as those continue to follow the
relationships saved in the relationships window.

Hope that helps.

-Ted Allen
>-----Original Message-----
>Hello,
>What is the difference/advantages either way between
defining join properties "on the fly" in the Design Query
View vs. permanent definition under the Tool-
Relationships menu procedure?
>
>It seems that I can define them on the fly using the
following code without any setting of primary keys
(equivalent to doing so in the Design query view), while
setting of primary keys is necessary for permanent
definition under the Tool-Relationships menu procedure.
Is there any danger in not setting the primary keys but
just doing the join and make table (later export table)
on the fly? If there is no danger, why ever bother with
the Tools-Relationships menu procedure?
>
> Dim strSQL As String
> strSQL = "SELECT zmax2.*, rooting2.* " & _
> "INTO temp " & _
> "FROM zmax2 LEFT OUTER JOIN rooting2 " & _
> "ON zmax2.musym = rooting2.musym"
>
> 'Turn off warning message.
> DoCmd.SetWarnings False
>
> DoCmd.RunSQL strSQL
>
> 'Re-enable warning message
> DoCmd.SetWarnings True
>
>
>RichardA
>.
>

Ted,

I think this was very helpful. thanks.

RichardA

"Ted Allen" wrote:

> Hi Richard,
>
> These are actually two fundamentally different things.
> The relationships window allows you to set relationships
> for the purpose of preserving data integrity. By doing
> so, you can instruct Access to do certain things such as
> not accepting a foreign key value that does not exist in
> the primary table, deleting records from detail tables
> when the corresponding record in the master table is
> deleted, etc.
>
> In queries, you also define relationships between tables,
> but only for the purposes of defining the output that the
> query will provide. True, that by default the query
> builder imports the relationships that you have set in
> the relationships window (actually this may be an option,
> I can't remember offhand), but these can be changed
> according to the needs for each individual query. The
> relationships defined in queries have no impact on how
> Access treats the relationships between tables regarding
> data integrity, as those continue to follow the
> relationships saved in the relationships window.
>
> Hope that helps.
>
> -Ted Allen
> >-----Original Message-----
> >Hello,
> >What is the difference/advantages either way between
> defining join properties "on the fly" in the Design Query
> View vs. permanent definition under the Tool-
> Relationships menu procedure?
> >
> >It seems that I can define them on the fly using the
> following code without any setting of primary keys
> (equivalent to doing so in the Design query view), while
> setting of primary keys is necessary for permanent
> definition under the Tool-Relationships menu procedure.
> Is there any danger in not setting the primary keys but
> just doing the join and make table (later export table)
> on the fly? If there is no danger, why ever bother with
> the Tools-Relationships menu procedure?
> >
> > Dim strSQL As String
> > strSQL = "SELECT zmax2.*, rooting2.* " & _
> > "INTO temp " & _
> > "FROM zmax2 LEFT OUTER JOIN rooting2 " & _
> > "ON zmax2.musym = rooting2.musym"
> >
> > 'Turn off warning message.
> > DoCmd.SetWarnings False
> >
> > DoCmd.RunSQL strSQL
> >
> > 'Re-enable warning message
> > DoCmd.SetWarnings True
> >
> >
> >RichardA
> >.
> >
>