| Author |
Message |
|
cpolic
#1 / 12
|
 Left joins from code?
Does anybody know how to create a left outer join in code?
|
| Fri, 01 Feb 2002 03:00:00 GMT |
|
 |
|
Crying Wol
#2 / 12
|
Left joins from code? What do you mean by a left outer join? Joining a variable to the left of text?
Quote: > Does anybody know how to create a left outer join in code?
-- He who learns but does not think is lost, he who thinks but does not lea Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.
|
| Fri, 01 Feb 2002 03:00:00 GMT |
|
|
|
cpolic
#3 / 12
|
Left joins from code? Sorry about that, I should have been more specific. I'm looking for a way to join two tables in code - but the join has to be a left outer join. I can't seem to find anything that will do it - and I haven't found any way to enforce cascading updates or deletes even when I create a normal join. I've used the documented steps - but then when I look up the join in the realtionships window, cascading updates and deletes aren't enforced. Thanks for any possible help
-Patrick Stephens
Quote: > What do you mean by a left outer join? Joining a variable to the left > of text?
> > Does anybody know how to create a left outer join in code?
> --
> He who learns but does not think is lost, he who thinks but does not lea
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
|
| Fri, 01 Feb 2002 03:00:00 GMT |
|
|
|
Simon Lewi
#4 / 12
|
Left joins from code? Patrick, You're still being a bit vague, but I suspect you may be talking about
creating relationships programmatically. This is done by creating a Relation
object, setting its properties, and appending it to the relationships
collection (similar to other DAO objects).
The relation's Attributes property allows you to specify referential
integrity and cascade update/delete options.
Does that help? There is more about Relation objects in online help, of
course.
Simon Lewis
Quote:
>Sorry about that, I should have been more specific. I'm looking for a way to >join two tables in code - but the join has to be a left outer join. I can't >seem to find anything that will do it - and I haven't found any way to >enforce cascading updates or deletes even when I create a normal join. I've >used the documented steps - but then when I look up the join in the >realtionships window, cascading updates and deletes aren't enforced. >Thanks for any possible help
>-Patrick Stephens
>> What do you mean by a left outer join? Joining a variable to the left
>> of text?
>> > Does anybody know how to create a left outer join in code?
>> --
>> He who learns but does not think is lost, he who thinks but does not lea
>> Sent via Deja.com http://www.deja.com/
>> Share what you know. Learn what you don't.
|
| Fri, 01 Feb 2002 03:00:00 GMT |
|
|
|
cpolic
#5 / 12
|
Left joins from code? that's exactly ehat I want to do - but I can't find any information about how to create a left outer join relationship. I've tried using the attributes to set the cascade update/delete options but it doesn't seem to actually set the properties when I open the relationship window. Although what i'm mainly interested in is creating a left outer join. -Patrick
Quote: > Patrick, > You're still being a bit vague, but I suspect you may be talking about
> creating relationships programmatically. This is done by creating a
Relation
> object, setting its properties, and appending it to the relationships
> collection (similar to other DAO objects).
> The relation's Attributes property allows you to specify referential
> integrity and cascade update/delete options.
> Does that help? There is more about Relation objects in online help, of
> course.
> Simon Lewis
> >Sorry about that, I should have been more specific. I'm looking for a way
> to
> >join two tables in code - but the join has to be a left outer join. I
can't
> >seem to find anything that will do it - and I haven't found any way to
> >enforce cascading updates or deletes even when I create a normal join.
I've
> >used the documented steps - but then when I look up the join in the
> >realtionships window, cascading updates and deletes aren't enforced.
> >Thanks for any possible help
> >-Patrick Stephens
> >> What do you mean by a left outer join? Joining a variable to the left
> >> of text?
> >> > Does anybody know how to create a left outer join in code?
> >> --
> >> He who learns but does not think is lost, he who thinks but does not
lea
> >> Sent via Deja.com http://www.deja.com/
> >> Share what you know. Learn what you don't.
|
| Fri, 01 Feb 2002 03:00:00 GMT |
|
|
|
Albert Marshal
#6 / 12
|
Left joins from code?
Quote: >Does anybody know how to create a left outer join in code?
When you build your SQL string you need to include LEFT JOIN in the From part. Try building the query you need in the QBE grid and then switch to SQL view.
That's how I got this sample:
FROM tblDates LEFT JOIN tblDateTable ON tblDates.MonthNo = tblDateTable.MonthNo;
--
Albert Marshall
|
| Fri, 01 Feb 2002 03:00:00 GMT |
|
|
|
Simon Lewi
#7 / 12
|
Left joins from code? Patrick, The 'Join Properties' options are specific to Access and are not documented
in help. From experiment, it seems that setting bits 24 or 25 of the
Attributes specifies an outer join. However, I got an 'invalid operation'
error when I tried to set bit 24 in code, using:
rel.Attributes = rel.Attributes + 16777216
It may not be possible to set this via DAO. But the join type is merely used
to specify the default join in queries - it has no effect on data integrity
at all.
Simon Lewis
Quote:
>that's exactly ehat I want to do - but I can't find any information about >how to create a left outer join relationship. I've tried using the >attributes to set the cascade update/delete options but it doesn't seem to >actually set the properties when I open the relationship window. Although >what i'm mainly interested in is creating a left outer join. >-Patrick
|
| Fri, 01 Feb 2002 03:00:00 GMT |
|
|
|
Andy Bar
#8 / 12
|
Left joins from code? Your question is pretty ambiguous. If you mean a query that includes a left outer join, then you would use createquerydef and the sql property of the querydef as required. If you mean a relationship that defaults to a left outer join, then you would use createrelation and set the attributes property to include the value, dbRelationLeft. -- Andy
Quote:
>Does anybody know how to create a left outer join in code?
|
| Fri, 01 Feb 2002 03:00:00 GMT |
|
|
|
Rebecca Riorda
#9 / 12
|
Left joins from code? Simon, Funny, they're _not_ in help, but they are in the Jet DB Engine Programmer's
Guide. The attributes are dbRelationLeft and dbRelationRight. They don't
have any effect on referential integrity, though...
- Rebecca.
Quote: > Patrick, > The 'Join Properties' options are specific to Access and are not
documented
> in help. From experiment, it seems that setting bits 24 or 25 of the
> Attributes specifies an outer join. However, I got an 'invalid operation'
> error when I tried to set bit 24 in code, using:
> rel.Attributes = rel.Attributes + 16777216
> It may not be possible to set this via DAO. But the join type is merely
used
> to specify the default join in queries - it has no effect on data
integrity
> at all.
> Simon Lewis
> >that's exactly ehat I want to do - but I can't find any information about
> >how to create a left outer join relationship. I've tried using the
> >attributes to set the cascade update/delete options but it doesn't seem
to
> >actually set the properties when I open the relationship window. Although
> >what i'm mainly interested in is creating a left outer join.
> >-Patrick
|
| Sat, 02 Feb 2002 03:00:00 GMT |
|
|
|
Simon Lewi
#10 / 12
|
Left joins from code? Rebecca, They're also defined in DAO.RelationAttribu{*filter*}um, so Access certainly
recognizes them - but I'm still getting an 'invalid op' error when I try to
set the Attributes bits. Have you tried doing this?
Simon Lewis
|
| Sat, 02 Feb 2002 03:00:00 GMT |
|
|
|
Radu Lasca
#11 / 12
|
Left joins from code? Hi Simon, You need to 'or' them. This works:
.Attributes = dbRelationLeft Or dbRelationDeleteCascade
Hth,
Radu Lascae
Quote:
> Rebecca, > They're also defined in DAO.RelationAttribu{*filter*}um, so Access certainly
> recognizes them - but I'm still getting an 'invalid op' error when I try to
> set the Attributes bits. Have you tried doing this?
> Simon Lewis
|
| Sat, 02 Feb 2002 03:00:00 GMT |
|
|
|
cpolic
#12 / 12
|
Left joins from code? Wow. Thank you! Everything works!
I never cease to be amazed at the benevolence on this list. It's refreshing
and inspiring.
: )
-Patrick Stephens
|
| Sat, 02 Feb 2002 03:00:00 GMT |
|
| |
