Re: ODBC call failed

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: blinton25 (blinton25_at_hotmail.com)
Date: 03/20/04 

Date: Sat, 20 Mar 2004 10:24:00 -0800

Hi,

Thanks for the pointers, guess I have my work cut out for
me. I may also try upsizing to an ADP to try to improve
performance.

>-----Original Message-----
>Your particular query is way too much for Access to
handle. The UNION
>clauses are causing all rows to be fetched locally into
Access for
>processing. Then Access tries to run the IIf and other
functions on
>each row, one row at a time. So the answer is, yes
indeedy, you need
>to convert this monster query to a stored procedure,
which you can
>then call from a pass-through query that you can base the
report on.
>One other point -- Keep all format functions and the like
in the
>report itself, don't put those in the stored procedure.
You'll just
>slow down the execution of the stored procedure, and the
report has to
>process each line in the result set one at a time anyway.
What you
>want to do is let the stored procedure do all the data
processing,
>hand the result set back to Access, and let Access do the
>presentation.
>
>-- Mary
>Microsoft Access Developer's Guide to SQL Server
>http://www.amazon.com/exec/obidos/ASIN/0672319446
>
>On Fri, 19 Mar 2004 09:20:34 -0800, "blinton25"
><blinton25@hotmail.com> wrote:
>
>>Hello,
>>
>>I use Linked Tables to connect to my SQL Server
database.
>>I have a number of reports which worked fine when I was
>>using test data (few thousand records).
>>
>>I just loaded in a million records and ran a report, and
>>received the following error:
>>
>>ODBC call failed
>>
>>when running this query:
>>
>>SELECT regionorder, Regions3, 1 As Quarter, January as
>>A, February as B, March as C FROM
>>RegionofResidenceCrosstab
>>UNION ALL
>>SELECT regionorder, Regions3, 2 As Quarter, April, May,
>>June FROM RegionofResidenceCrosstab
>>UNION ALL
>>SELECT regionorder, Regions3, 3 As Quarter, July,
August,
>>September FROM RegionofResidenceCrosstab
>>UNION ALL SELECT regionorder, Regions3, 4 As Quarter,
>>October, November, December FROM
>>RegionofResidenceCrosstab;
>>
>>RegionofResidenceCrosstab looks like this:
>>
>>TRANSFORM "~" & Count(IIf(DatePart('yyyy',
>>[F_ARRV_DAT],1,0)=forms!DateRangePreviousCurrentYear!
>>PrevYear,[Final_Clean Tourists Query].[TRAV-KEY])) & "~"
>>& Count(IIf(DatePart('yyyy',[F_ARRV_DAT],1,0)=forms!
>>DateRangePreviousCurrentYear!CurrYear,[Final_Clean
>>Tourists Query].[TRAV-KEY])) AS Expr1
>>SELECT [Final_Clean Tourists Query].REGIONS3,
>>[Final_Clean Tourists Query].regionorder
>>FROM [Final_Clean Tourists Query]
>>WHERE [Final_Clean Tourists Query].REGIONS3<>''
>>GROUP BY [Final_Clean Tourists Query].regionorder,
>>[Final_Clean Tourists Query].REGIONS3
>>PIVOT Format([F_ARRV_DAT],"mmmm") in
>>
("January", "February", "March", "April","May","June","Jul
>>y","August","September","October","November","December");
>>
>>
>>1. I am wondering if the query is not simply timing out.
>>2. Given that these queries were written for MS Access
>>maybe I need to convert them into stored procedures to
be
>>called by the report.
>>
>>Any other suggestions?
>>
>
>.
>

Relevant Pages

  • RE: union query for report
    ... I have a report with 14 subreports. ... but the enter parameter value for A1aC display 12 times. ... The other 6 are based on 6 union ... I wondered if the problem would be using the query, qYr2ReviewSample, in the ...
    (microsoft.public.access.reports)
  • Re: Sum of numbers
    ... Administrator to come in to insure we have this critical report by April. ... You say the union query 'only shows fields from the first table'. ... Do you mean that you want a multicolumn report with all customers' names ...
    (microsoft.public.access.reports)
  • Re: SQL Select Query Help
    ... I should be approaching this as a union and not trying to ... If I type all the rows (pay or deduction) ... > in the case of your report, there IS no logical relationship - so I had to ... > query in chronological order. ...
    (microsoft.public.sqlserver.programming)
  • Re: This Month vs Last Month
    ... UNION ALL ... Then create a crosstab query based on the union query with (should be ... Heading, Area as another Row Heading, "Mth" & MthNum as the Column Heading, ... The report is currently being pulled for the ...
    (microsoft.public.access.reports)
  • RE: Still have hope but need more help. First Question, more to fo
    ... Check Help or google on UNION Queries. ... Perhaps if I knew exactly what a union query ... "Duane Hookom" wrote: ... tell us how you want to show this in a report ...
    (microsoft.public.access.reports)