Re: Problem using Access or Query Designer to run queries in SQL Serve

Bottom line: don't DO that. As you have seen, it won't work and will
never work the way you are going about it.

Access was originally designed (a dozen years ago) to be a desktop
database connected to the low-end Jet engine, not a general purpose
querying tool for fetching server data consisting of millions of rows.
Its basic architecture is essentially unchanged since then. If you
want to use it as a FE to SQLS, then you need to design a
query-by-form interface that builds queries with WHERE clauses that
restrict the number of rows fetched from the server. There are a ton
of Access books that have been written over the years that tell you
exactly how to construct such a UI.

Part of developing a successful and robust application is picking the
right tool for the job, or understanding the limitations of the tool
you are stuck with, and working around those limitations. You can use
Access as long as you honor its limitations and obey the golden rule
of fetching less data.

--Mary

On Fri, 13 May 2005 19:26:04 -0700, "BI_Specialist"
<BISpecialist@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

>Hi,
>I'm running a SQL server 2000 (recently upgraded to SP4, MDAC 2.81) on
>Win2k box with 1GB memory. I've two huge denormalized tables: one Master
>table with 70 columns and 5million rows and another Summary table with fewer
>columns and 2 million rows.
>
>Problem:
>When I try to run a simple <select * from table> kind of query from Access
>or Query Designer within Enterprise Manager, it works and I get data back.
>The minute I add a simple where condition or run a query with 3-4 columns
>with a group by, it fails with a:
>ODBC Call Failed [ODBC SQL Server Driver] Timeout Expired(#0) error in Access
>and in Query Designer (on the server box!) something similar happens:
>[ODBC SQL Server Driver] Timeout Expired
>Same query against the summary table may work most of the time using both
>clients.
>
>So, I've spent the last few days searching the forums, KB's etc and realize
>that this is a client issue and specifically a query timeout issue since I do
>NOT have problems running any kind of query using Query Analyzer or surprise:
>Microsoft Query (Excel) against either table. Not being able to use Query
>Designer doesn't matter much, but if running queries via Access doesn't
>work..then I'm in serious trouble. Being able to use Access to access the SQL
>server data is key, because of the large datasets and the ease with which
>financial analysts can customize their queries for their specific needs.
>
>While I work on normalizing my database, things I've tried so far with no
>success:
>1. Added indexes to the tables, shrunk database, checked available space,
>updated statistics etc..
>2. Set Query Governor on server to unlimited, remote server connection
>timeout to unlimited too
>3. Logged long running queries in Client configuration of SQL server DSN and
>set query time to max of 99999 milliseconds
>4. Set OLE/DD timeout within Access's Tools-->Options to max of 300
>5. Run a trace in SQL Profiler and see if I could get any clue ...
>6. Restarted SQL server a few times to get rid of ghost sessions, locks etc..
>7. Used Access and Query Designer on the same box as the SQL server to
>eliminate network issues
>8. Changed my ODBC DSN to use SQL pipes instead of TCP/IP
>
>This is driving me absolutely crazy. Especially the fact that Excel using
>the same client ODBC SQL server DSN used within Access, can run any query
>without timing out, but not Access!
>I've used Access to run queries against same 5million row table in MySQL
>(using MySQL ODBC driver) and it works fine..Only downside is that I've to
>install MySQL ODBC driver on all desktops and it is unsupported :(
>
>Can someone please help me make Access work with SQL server 2000 or is this
>just an ODBC bug that affects big tables?
>
>Thanks for listening
>John H.

.

Relevant Pages

  • Problem using Access or Query Designer to run queries in SQL Serve
    ... When I try to run a simple kind of query from Access ... ODBC Call Failed [ODBC SQL Server Driver] Timeout Expirederror in Access ... but if running queries via Access doesn't ...
    (microsoft.public.sqlserver.odbc)
  • Re: parameter in sql query
    ... Most likely designer omits '@' in parameter name. ... Desktop provider would silently add it, ... >> Does the parameter substitution occur after or before the query is sent ... >> I tried writing some queries to the SQL server but couldn't reproduce ...
    (microsoft.public.dotnet.framework.compactframework)
  • Query Designer and outer joins
    ... Are there any issues with Access 2000 and outer joins in the query ... I'm using Access to report against a SQL Server db, ... and parentheses so I decided to just use the Query Designer, ...
    (microsoft.public.access.queries)
  • Re: Enterprise Manager confusion
    ... I orginally started out in MS Access, so when I began using SQL Server, I ... it does like the Access query builder. ... By the way, you can drag columns individually in QA, you don't have to drag ... SOME others out there who feel, as I do, that the View designer is far ...
    (microsoft.public.sqlserver.tools)
  • Query Timeout error
    ... MS ODBC SQL Server Driver ... The ODBC Query Timeout Reg key is set to 0 and the SQL Server remote query ... I never get this when I execute the stored procedure in SSMS Express. ...
    (microsoft.public.sqlserver.odbc)