?
Solved

ms access query and subquery gives an odbc call failed

Posted on 2013-12-17
2
Medium Priority
?
406 Views
Last Modified: 2013-12-17
I am trying to use a subquery as a criteria to my query and I get a odbc call failed.  I do not get this error when I use the subquery to make a temp table and then use the subquery criteria calling on the temp table.  The queries are talking to back end sql tables.  I do not have an odbc connection problem.

The first query is (Query Name is:  "InitialSetupProblemQuery_Sanjay"

SELECT dbo_CallRpt.CCRNum
FROM (((dbo_CallRpt LEFT JOIN dbo_ProblemDetails ON dbo_CallRpt.[CCRNum] = dbo_ProblemDetails.[RefNumber]) LEFT JOIN dbo_CrfRpt ON dbo_CallRpt.CCRNum = dbo_CrfRpt.CCRNum) LEFT JOIN dbo_CodeSystemLevel ON dbo_CallRpt.SystemLevel = dbo_CodeSystemLevel.SystemCode) LEFT JOIN dbo_CrfPartDetails ON dbo_CallRpt.CCRNum = dbo_CrfPartDetails.RefNumber
WHERE (((dbo_CrfPartDetails.LastBuildServiceStatus) Is Null) AND ((dbo_CallRpt.CallStatus)=3) AND ((dbo_CrfRpt.CRFNum) Is Not Null) AND ((dbo_CallRpt.CallDate)>=[Forms]![z filter].[BeginDate] And (dbo_CallRpt.CallDate)<DateAdd("d",1,[Forms]![z filter].[EndDate])) AND ((dbo_ProblemDetails.ProblemCode)="0001"))
GROUP BY dbo_CallRpt.CCRNum
ORDER BY dbo_CallRpt.CCRNum
WITH OWNERACCESS OPTION;


And then the second query is:

SELECT dbo_CallRpt.CCRNum, dbo_CallRpt.CallDate, dbo_CallRpt.CallStatus, dbo_CrfPartDetails.RecordID AS IDPartDetails, dbo_CrfPartDetails.PartNo, dbo_CrfPartDetails.LSN, dbo_CrfPartDetails.Rev, [LSN] & "_" & [Rev] AS LSNRev, dbo_CrfPartDetails.LastBuildServiceStatus
FROM dbo_CallRpt LEFT JOIN dbo_CrfPartDetails ON dbo_CallRpt.CCRNum = dbo_CrfPartDetails.RefNumber
WHERE (((dbo_CallRpt.CCRNum) In (select [CCRNum] from [InitialSetupProblemQuery_Sanjay])) AND ((dbo_CrfPartDetails.RecordID) Is Not Null) AND ((dbo_CrfPartDetails.PartNo) Is Not Null) AND ((dbo_CrfPartDetails.LastBuildServiceStatus) Is Null))
ORDER BY dbo_CallRpt.CCRNum;
0
Comment
Question by:sxxgupta
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
2 Comments
 
LVL 48

Accepted Solution

by:
Dale Fye earned 2000 total points
ID: 39724169
If this is being run from Access, as it appears, have you tried changing the query's timeout property to zero?

Are the fields in your where clause and Join indexed in the SQL Server tables?

Have you defined the references to the form controls as parameters in Access?  Or write the query as a stored procedure and pass it those values as parameters.

You might want to consider is creating this query via VBA and updating the SQL  property of the query at run time.  By doing so, you could convert your references to your form in the to explicit dates.
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 39724569
So, what worked?
0

Featured Post

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question