|
 |
asp_databases thread: Limit on joins in access 2000 ??
Message #1 by "Kim Iwan Hansen" <kimiwan@k...> on Sat, 17 Nov 2001 00:42:03 +0100
|
|
Hi,
I have a problem using joins on access 2000. Does anyone know if there is a
limit on how many joins you can make in a single sql sentence in access?
I'm trying to make the following work, so I've taken out all the fields that
I'll need to call eventually.
I have three tables: Users, UserInfo and Charts.
strSQL = "SELECT Users.UserAlias FROM Charts INNER JOIN Users ON
(Charts.UserID = Users.UserID) INNER JOIN UserInfo ON (Charts.UserID
UserInfo.UserID)"
The error message I get is this:
**********************************************
Microsoft JET Database Engine error '80040e14'
Syntax error (missing operator) in query expression '(Charts.UserID
Users.UserID) INNER JOIN UserInfo ON (Charts.UserID = UserInfo.UserID)'.
/adm/user_details.asp, line 40
**********************************************
If I take out one of the joins - no matter which one - it'll work, but there
seems to be a problem when I use three at once.
Does anyone know what the problem might be??
Best regards,
Kim
Message #2 by "Paul Cracknell" <paul.cracknell@b...> on Sun, 18 Nov 2001 15:43:06 -0000
|
|
Kim
I cannot find any reference to such a limit in any of the books I have on
Access 2000. I have tested it with links between several tables without any
problem.
The only times I have ever had query problems similar to that you have
described is when the data has either been absent or of the wrong data type.
Paul C
----- Original Message -----
From: "Kim Iwan Hansen" <kimiwan@k...>
To: "ASP Databases" <asp_databases@p...>
Sent: Friday, November 16, 2001 11:42 PM
Subject: [asp_databases] Limit on joins in access 2000 ??
> Hi,
>
> I have a problem using joins on access 2000. Does anyone know if there is
a
> limit on how many joins you can make in a single sql sentence in access?
>
> I'm trying to make the following work, so I've taken out all the fields
that
> I'll need to call eventually.
>
> I have three tables: Users, UserInfo and Charts.
>
> strSQL = "SELECT Users.UserAlias FROM Charts INNER JOIN Users ON
> (Charts.UserID = Users.UserID) INNER JOIN UserInfo ON (Charts.UserID
> UserInfo.UserID)"
>
> The error message I get is this:
>
> **********************************************
> Microsoft JET Database Engine error '80040e14'
>
> Syntax error (missing operator) in query expression '(Charts.UserID
> Users.UserID) INNER JOIN UserInfo ON (Charts.UserID = UserInfo.UserID)'.
>
> /adm/user_details.asp, line 40
> **********************************************
>
> If I take out one of the joins - no matter which one - it'll work, but
there
> seems to be a problem when I use three at once.
>
> Does anyone know what the problem might be??
>
> Best regards,
>
> Kim
>
>
>
paul.cracknell@b...
$subst('Email.Unsub')
>
Message #3 by David Cameron <dcameron@i...> on Mon, 19 Nov 2001 09:11:15 +1100
|
|
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01C1707D.F36E4E80
Content-Type: text/plain;
charset="iso-8859-1"
Access can definitely handle more that 3 joins. I'm not sure of the limits
but I have created a query in Access with ~10 joins.
Copy the query into somewhere where you can test it in the Access database.
Edit it until you get it working. Your syntax looks fine, but you might want
to enclose the table name Users in []. You need to watch using words that
might be reserved keywords. I'm not sure what the reserved keywords are for
Access or if there are any, but by enclosing fields and table names in []
should be able to get around the problem.
regards
David Cameron
nOw.b2b
dcameron@i...
-----Original Message-----
From: Paul Cracknell [mailto:paul.cracknell@b...]
Sent: Monday, 19 November 2001 1:43 AM
To: ASP Databases
Subject: [asp_databases] Re: Limit on joins in access 2000 ??
Kim
I cannot find any reference to such a limit in any of the books I have on
Access 2000. I have tested it with links between several tables without any
problem.
The only times I have ever had query problems similar to that you have
described is when the data has either been absent or of the wrong data type.
Paul C
----- Original Message -----
From: "Kim Iwan Hansen" <kimiwan@k...>
To: "ASP Databases" <asp_databases@p...>
Sent: Friday, November 16, 2001 11:42 PM
Subject: [asp_databases] Limit on joins in access 2000 ??
> Hi,
>
> I have a problem using joins on access 2000. Does anyone know if there is
a
> limit on how many joins you can make in a single sql sentence in access?
>
> I'm trying to make the following work, so I've taken out all the fields
that
> I'll need to call eventually.
>
> I have three tables: Users, UserInfo and Charts.
>
> strSQL = "SELECT Users.UserAlias FROM Charts INNER JOIN Users ON
> (Charts.UserID = Users.UserID) INNER JOIN UserInfo ON (Charts.UserID
> UserInfo.UserID)"
>
> The error message I get is this:
>
> **********************************************
> Microsoft JET Database Engine error '80040e14'
>
> Syntax error (missing operator) in query expression '(Charts.UserID
> Users.UserID) INNER JOIN UserInfo ON (Charts.UserID = UserInfo.UserID)'.
>
> /adm/user_details.asp, line 40
> **********************************************
>
> If I take out one of the joins - no matter which one - it'll work, but
there
> seems to be a problem when I use three at once.
>
> Does anyone know what the problem might be??
>
> Best regards,
>
> Kim
>
>
>
paul.cracknell@b...
$subst('Email.Unsub')
>
$subst('Email.Unsub')
Message #4 by "Ken Schaefer" <ken@a...> on Mon, 19 Nov 2001 11:53:39 +1100
|
|
Jet has a funny need for ( ) - you can see this if you create the query in
the QBE. If I take your query, and rewrite it as follows, it'll work:
SELECT
Users.UserAlias
FROM
(
Charts
INNER JOIN
Users
ON
(Charts.UserID = Users.UserID)
)
INNER JOIN
UserInfo
ON
(Charts.UserID = UserInfo.UserID)
For some reason, the JOINs need to be "nested" (probably not the right word,
but I can't think of anything more appropriate)
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Kim Iwan Hansen" <kimiwan@k...>
Subject: [asp_databases] Limit on joins in access 2000 ??
: Hi,
:
: I have a problem using joins on access 2000. Does anyone know if there is
a
: limit on how many joins you can make in a single sql sentence in access?
:
: I'm trying to make the following work, so I've taken out all the fields
that
: I'll need to call eventually.
:
: I have three tables: Users, UserInfo and Charts.
:
: strSQL = "SELECT Users.UserAlias FROM Charts INNER JOIN Users ON
: (Charts.UserID = Users.UserID) INNER JOIN UserInfo ON (Charts.UserID
: UserInfo.UserID)"
:
: The error message I get is this:
:
: **********************************************
: Microsoft JET Database Engine error '80040e14'
:
: Syntax error (missing operator) in query expression '(Charts.UserID
: Users.UserID) INNER JOIN UserInfo ON (Charts.UserID = UserInfo.UserID)'.
:
: /adm/user_details.asp, line 40
: **********************************************
:
: If I take out one of the joins - no matter which one - it'll work, but
there
: seems to be a problem when I use three at once.
:
: Does anyone know what the problem might be??
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Message #5 by "Kim Iwan Hansen" <kimiwan@k...> on Tue, 20 Nov 2001 06:24:24 +0100
|
|
Thanks a lot Ken, that solved my problm :)
-Kim
-----Original Message-----
From: Ken Schaefer [mailto:ken@a...]
Sent: 19. november 2001 01:54
To: ASP Databases
Subject: [asp_databases] Re: Limit on joins in access 2000 ??
Jet has a funny need for ( ) - you can see this if you create the query in
the QBE. If I take your query, and rewrite it as follows, it'll work:
SELECT
Users.UserAlias
FROM
(
Charts
INNER JOIN
Users
ON
(Charts.UserID = Users.UserID)
)
INNER JOIN
UserInfo
ON
(Charts.UserID = UserInfo.UserID)
For some reason, the JOINs need to be "nested" (probably not the right word,
but I can't think of anything more appropriate)
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Kim Iwan Hansen" <kimiwan@k...>
Subject: [asp_databases] Limit on joins in access 2000 ??
: Hi,
:
: I have a problem using joins on access 2000. Does anyone know if there is
a
: limit on how many joins you can make in a single sql sentence in access?
:
: I'm trying to make the following work, so I've taken out all the fields
that
: I'll need to call eventually.
:
: I have three tables: Users, UserInfo and Charts.
:
: strSQL = "SELECT Users.UserAlias FROM Charts INNER JOIN Users ON
: (Charts.UserID = Users.UserID) INNER JOIN UserInfo ON (Charts.UserID
: UserInfo.UserID)"
:
: The error message I get is this:
:
: **********************************************
: Microsoft JET Database Engine error '80040e14'
:
: Syntax error (missing operator) in query expression '(Charts.UserID
: Users.UserID) INNER JOIN UserInfo ON (Charts.UserID = UserInfo.UserID)'.
:
: /adm/user_details.asp, line 40
: **********************************************
:
: If I take out one of the joins - no matter which one - it'll work, but
there
: seems to be a problem when I use three at once.
:
: Does anyone know what the problem might be??
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
$subst('Email.Unsub')
|
|
|
View Cart
