Access-SQL query error

[Pietro]

Hi,

I used to use the following query in my Access database, when i upsized
the tables to SQL it's no longer working and gives me the error "ODBC--call
failed"
Please advise.

the query:
SELECT WTQ.Datew, WTQ.[Issue Type], WTQ.Language, WTQ.[DemandingSPV reason],
Count(WTQ.ID) AS Cases
FROM WTQ
GROUP BY WTQ.Datew, WTQ.[Issue Type], WTQ.Language, WTQ.[DemandingSPV reason]
HAVING (((WTQ.Datew) Between [forms]![WarmT Report]![from] And
[forms]![WarmT Report]![to]) AND ((WTQ.[Issue Type])="Reached end of tier 1
support boundaries") AND ((WTQ.Language)=[forms]![WarmT Report]![language]))
OR (((WTQ.Datew) Between [forms]![WarmT Report]![from] And [forms]![WarmT
Report]![to]) AND ((WTQ.[Issue Type])="Reached end of tier 1 support
boundaries") AND (([forms]![WarmT Report]![language]) Is Null));

 

[Stefan Hoffmann]

hi Pietro,

I used to use the following query in my Access database, when i upsized
the tables to SQL it's no longer working and gives me the error "ODBC--call
failed"

Have you upsized your .mdb to an .adp?


mfG
--> stefan <--

 

[Sylvain Lafontaine]

If he were using an ADP project, he should have seen an OLEDB error msg, not
an ODBC one.

To the original poster: some complex queries who can work against JET tables
won't work anymore when you are using ODBC Linked tables against a
SQL-Server (a combination of Outer Join and Subqueries is a common exemple);
maybe it's what happenings here. In your case, you can simplify your query
by moving all the expressions inside the Having clause to the Where clause.
In fact, even with JET, these expressions have no place there. The Having
statement should only by used on columns computed by a Group By; such as
your Count(WTQ.ID) expression column:

SELECT WTQ.Datew, WTQ.[Issue Type], WTQ.Language, WTQ.[DemandingSPV reason],
Count(WTQ.ID) AS Cases
FROM WTQ
WHERE (((WTQ.Datew) Between [forms]![WarmT Report]![from] And
[forms]![WarmT Report]![to]) AND ((WTQ.[Issue Type])="Reached end of tier 1
support boundaries") AND ((WTQ.Language)=[forms]![WarmT Report]![language]))
OR (((WTQ.Datew) Between [forms]![WarmT Report]![from] And [forms]![WarmT
Report]![to]) AND ((WTQ.[Issue Type])="Reached end of tier 1 support
boundaries") AND (([forms]![WarmT Report]![language]) Is Null));
GROUP BY WTQ.Datew, WTQ.[Issue Type], WTQ.Language, WTQ.[DemandingSPV
reason]

However, I have no idea if this will solve your problem or not.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Stefan Hoffmann]

hi Sylvain,

If he were using an ADP project, he should have seen an OLEDB error msg, not
an ODBC one.

While this is true, his query doesn't contain any problematic SQL clause
or anything other. It's still executed by Jet, hence the ODBC message
type. But I've never seen this short "ODBC call failed" without further
notice or message.


mfG
--> stefan <--

 

[Sylvain Lafontaine]

Well, there are other possibilities here but without any further details,
it's hard to say. For example, it's quite possible that his problem is not
limited to this particular query but to any other queries as well or that he
have a permission problem on one of these tables or maybe even a collation
issue.

To the OP, if possible, give us some more details. Also, if you know how,
taking a look with the SQL-Server Profiler might give us some hints on this
problem.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)