Solved

ms access query and subquery gives an odbc call failed

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

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

Suggested Solutions

Title # Comments Views Activity
SQL Server 2012 r2 - Sum totals 2 28
SQL Server group with two values 4 34
Comparison query - 4 columns 9 22
SQL - Curser to do an insert based on a select 2 10
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
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.​
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

839 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