ms access query and subquery gives an odbc call failed

Posted on 2013-12-17
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

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;
Question by:sxxgupta
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
LVL 48

Accepted Solution

Dale Fye (Access MVP) earned 500 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.
LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 39724569
So, what worked?

Featured Post

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

705 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