SQL 7.0 Query in Access 2000 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

SQL 7.0 Query in Access 2000

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-10-10 : 09:27:16

Michael writes "I have a SQL 7.0 query:
SELECT StudentVolInfo.StuSchoolName, Volunteers.FirstName,
Volunteers.LastName, Students.StuFirstName,
Students.StuLastName, VolSchool.VolClassification,
VolSchool.ActiveSchool
FROM VolSchool INNER JOIN
Volunteers ON
VolSchool.VolID = Volunteers.VolID RIGHT OUTER JOIN
StudentVolInfo ON
VolSchool.SchoolName = StudentVolInfo.StuSchoolName AND
Volunteers.VolID = StudentVolInfo.VolID LEFT OUTER JOIN
Students ON
StudentVolInfo.StuID = Students.StuID
WHERE (StudentVolInfo.StuSchoolName LIKE N'eastgate%')

However, this query refuses to run on Access, and generates the following error:

Syntax error (missing operator) in query expression 'VolSchool.VolID = Volunteers.VolID RIGHT OUTER JOIN StudentVolInfo ON
VolSchool.SchoolName = StudentVolInfo.StuSchoolName'

I need to solve this as quickly as possible!
I'm running SQL Server 7.0 with a SQLserver 2000 Enterprise manager to generate the query. The access DB is version 2000 SP-1"

ewade
Starting Member

36 Posts

Posted - 2002-10-10 : 15:17:42

Are you copying the SQL and pasting it into Access or are you running this as a stored procedure? Would I be correct in assuming that you want to use the output from the query for a report?

Access has, in the past, not done a very good job of interacting with data sources other than Access or Excel (and even that was iffy). Access XP solves some of this by allowing you to directly interact with a SQL server, but it sounds like you don't have this luxury.

I think the problem is your syntax... Access 97 (and presumably 2000) use RIGHT JOIN and LEFT JOIN rather than RIGHT OUTER JOIN and LEFT OUTER JOIN.

Try:

SELECT StudentVolInfo.StuSchoolName, Volunteers.FirstName, 
Volunteers.LastName, Students.StuFirstName, 
Students.StuLastName, VolSchool.VolClassification, 
VolSchool.ActiveSchool 
FROM VolSchool JOIN 
Volunteers ON 
VolSchool.VolID = Volunteers.VolID RIGHT JOIN 
StudentVolInfo ON 
VolSchool.SchoolName = StudentVolInfo.StuSchoolName AND 
Volunteers.VolID = StudentVolInfo.VolID LEFT JOIN 
Students ON 
StudentVolInfo.StuID = Students.StuID 
WHERE (StudentVolInfo.StuSchoolName LIKE N'eastgate%')

^

You could also simply use the IS NULL function in the Access QBE view, but you have to place it appropriately on either the right or left side of the equation. The orientation of the relationship line dictates which side is considered right and left in this view.

My best suggestion is to trigger a stored procedure from within Access (you can do this from the QBE view also)- it will run faster and you won't have to struggle with syntax incompatibilities.

Edited by - ewade on 10/10/2002 15:57:13

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-10-10 : 19:14:07

If you have the option look into Access Data Projects.
They offer the benefit of a SQL Backend with an Access Front End.

ewade
Starting Member

36 Posts

Posted - 2002-10-16 : 14:25:09

I wanted to explain why I altered the syntax for Access in the manner that I did.

Inner joins are the default join type for Access (hence simply using JOIN ... ON rather than INNER JOIN).

Never stop learning!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources