Solved

ms access query and subquery gives an odbc call failed

Posted on 2013-12-17
2
375 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
  • 2
2 Comments
 
LVL 47

Accepted Solution

by:
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.
0
 
LVL 47

Expert Comment

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

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

757 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now