Re: Slow Query

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Bdavis wrote:
Ok, aliases are not a problem. I'm not familiar with Pass-through queries. how do they work? 

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Pass-thru queries are queries written in the syntax of the DB server.
It looks like you're using SQL Server so, you'd use T-SQL syntax &
language to create the query.

How to create a pass-thru query:

1.  Open a new query in design mode.
2.  On the menu bar click Query > SQL Specific > Pass-Through.
3.  In the SQL view type in the T-SQL command that will read data ONLY
from the DB server.  IOW, you can't use the names of tables in the
Access file, only the tables on the SQL Server.
4.  On the menu bar click View > Properties - the Query Properties
dialog box will appear.
5.  In the ODBC Connect Str property put the correct ODBC connection
string.  E.g. (a DSN-less connection string [all one line]):

  ODBC;Driver=SQL Server;Server=myServerName;Database=MyDatabaseName;
Trusted_Connection=Yes;

Trusted_connection means use Windows authentication.

Each connection string depends on the environment.  Find out the name of
your server, or it's IP address, the database name and if you can use
Windows authentication to log on to the SQL Server.  If not in place of
the Trusted_Connection parameter, use:

  UID=LoginName;PWD=SQLServerPassword

Substitute your login for "LoginName" and that user's  SQL Server
password for "SQLServerPassword."

6.  Test the query.
7.  Save the query.

For more info read the Access article on Pass-thru queries and the SQL
Server Books On Line (BOL) documentation on T-SQL.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQtMZNIechKqOuFEgEQJglQCg7bN1qDBqLDc6TbL4jXkQDQVb/tsAnipK
R1rxkwaVo/o75GdzI0VOq7r/
=INGA
-----END PGP SIGNATURE-----
.

Relevant Pages

  • Re: Update or Delete querys with Joins
    ... This is were I was missing the boat Steve. ... update multiple columns in the ANSI with the same right-side of query. ... that time as Microsoft deems it suitable for inclusion into T-SQL. ... > but SQL Server doesn't implement it. ...
    (microsoft.public.sqlserver.programming)
  • Re: CONTAINS performance
    ... That said, and with the query plan, I can start to give you more ... relational join in the context of the free-text optimization, ... SQL Server tables. ...
    (microsoft.public.sqlserver.fulltext)
  • Re: What are these queries used for?
    ... A query exactly like the one above is rather pointless. ... T-SQL are also defined in ANSI, but there are some features that T-SQL ... has added in addition to the ANSI standard, ... My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis ...
    (comp.databases.ms-sqlserver)
  • Re: Access to SQLServer GCE
    ... Actually, as Larry points out, the Access client does a good job of filtering only information that you need. ... If your query is bound to a report, and you open that report supplying a typical "where" for that report, then Access will NOT pull down all the records, but in most cases Access will only pull down the required records and respect your filter. ... So SQL server will respect the conditions and filtering placed into those queries, and therefore only pull down those records you require. ... The suggestion in these cases is to consider using a pass-through query since all of that summing is done before the row comes down the network pipe. ...
    (comp.databases.ms-access)
  • Re: Subquery Help?
    ... Hve not used T-SQL in a while. ... may be in SQL Server 2005. ... DateSerial function to create a date based on the YearField, MonthField, ... The second query would have to be written in the SQL view, ...
    (microsoft.public.access.queries)