Query Design View

  • Thread starter Thread starter Kirk Phillips
  • Start date Start date
K

Kirk Phillips

I've got a select query based on a SQL pass through query
which accesses data in a large SQL Server db. The query
takes about 5-6 minutes to run when I return records in
Datasheet view. I don't have a problem with this, since I
know the recordset is very large.

The issue I'm having is opening the query in DESIGN view.
Access still takes 5-6 minutes to open the query in DESIGN
view, which is a pain everytime time a want to make a
simple change. Is there a way to bypass this behavior?
 
Dear Kirk:

There is a superior tool available for writing queries that are to be
run in SQL Server, Query Analyzer. If you are licensed for access to
SQL Server, then you would probably have the license to use this tool.
Once a query is written you could copy the SQL into an Access
application. If you have permission from the database administrator,
you could even save queries (Views, Stored Procedures, User Defined
Functions) within the SQL database as well.

If you are only licensed for an MSDE version of SQL Server, you could
purchase SQL Server Developer Edition for only $50 to gain a license
to this tool. This license is restricted to development uses, but
that would be just what you are doing when you're writing queries.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Even opening a Query in DesignView, I think the SQL String is parsed again
and datasource is checked. However, 5-6 mins seems to be excessive. I do
have delays but usually a few seconds for opening the DesignView, not 5-6
mins. I think the large recordset affects the execution of the Query but
not the DesignView of the Query.

Try open a linked Table from the SQL Server in DesignView and see what delay
you get?

Which communication are you using to access the SQL Server? Named Pipe?
TCP/IP?

How many Tables are used in the Query?
 
Kirk,

Did you find a solution to this problem? I think I have
the same problem except that my pass-through query takes
about 90 minutes to run. When opening it in Design view,
it never opens, but then again I have never waited 90
minutes. As you said, this is unacceptable when I just
want to tweek the query.

Gene
 
Gene

As advised by Tom & myself, the best way is to test your SQL directly in the
Database Server Management Tools, e.g. Enterprise Manger for MS-SQL Server
and see what result you get. Certainly 90 mins is an exceedingly long time
to execute a server query.
 
Hi Van,

Thanks for your response. Here is additional background on
the problem:

I am using the pass-through query to "send" an SQL command
to an Oracle 9i database. The query runs fine when I
submit it via TOAD, and it does normally take 90 minutes
to run. (Lotsa records and a somewhat complex query.)

Since our dev group does not use SQL Server, I do not have
access to the tool you mentioned. Also, I don't think I
need to "test my SQL" since it runs fine when submitted
either via TOAD or Access. The only problem is that the
query appears to run (not just be validated for syntax and
data source availability) every time I try to open it in
Design View. (It will eventually open in Design View if I
wait 90 minutes.)

Any other ideas?

Thanks,
Gene
 
Sorry, no ideas as I don't know Oracle.

Enterprise Manager is for MS_SQL Server only.
 
Back
Top