[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

ms access query and subquery gives an odbc call failed

Posted on 2013-12-17
2
Medium Priority
?
420 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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
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…

649 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