ODBC error with SQL Server

[Dale Fye]

I'm getting the following error when I run a query from an Access 2003
application using links to a SQL Server database.

The column prefix 'db.tbl_Gaps' does not match with a table name or alias
used in the query. (#107)

My query starts out with a query (qry_rpt_Selected_Gaps_and_Users) that
joins two tables using a Cartesian join to identify all of the users and Gaps
that are selected, that looks like:

SELECT User_ID, GapID
FROM tbl_Users, tbl_Gaps
WHERE tbl_Users.IsSelected <> 0
AND tbl_Gaps.IsSelected <> 0

As you can see, I have renamed the linked tables, eliminating the dbo_ prefix.

I have then created a second query that joins the first query to another
linked table. On the User_ID and GapID fields.

SELECT Q.User_ID, Q.GapID, GV.Gap_Likelihood, GV.Gap_Impact
FROM qry_rpt_Selected_Gaps_and_Users Q
INNER JOIN tbl_Gap_Voting GV
ON Q.User_ID = GV.User_ID AND Q.GapID = GV.GapID

When I use an INNER JOIN for this link, the query runs properly but
obviously does not give me all of the combinations of GapID/User_ID from the
Cartesian join. However, when I change the join to a Left Join, I get this
error message.

Any ideas why or how to work around this. I know I could create a view in
SQL Server and link to the view, but would prefer to stay within Access as
the gentleman who will be maintaining this application has little or no SQL
Server experience.

 

[Gary Walter]

Hi Dale,

I don't know why you get the error,
maybe setting up ShowPlan might help...

beyond that I don't think you are doing
this gentleman any favors by not creating
a view...

a cartesian join should be made on the server

one workaround might be a form/subform
(or report/subreport) where you would get the
equivalent of a "LEFT JOIN"...

no one else had responded so I thought my
meager advice might be worth something...

good luck,

gary

 

[Dale Fye]

Gary,

Thanks for your input. The problem is that this application can actually be
connected to either an Access database or to SQL Server, and I would have to
create that view/query in each instance of the SQL Server database that it
might connect to.

Thanks anyway. I'm having a similar problem with another query, which runs,
but returns '#DELETED' in the fields that should be NULL in the left join.
Have never had this problem before.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.