Re: White Paper on How MSAccess interacts with SQL Server Back End

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

the answer is that Jet scans the whole table across the network and
then does the math locally.

you _SHOULD_ be using ADP for anything that you do, because it sounds
to me like you know how to write TSQL

thanks

-Aaron Kempf
MCITP: DBA SQL 2005


On Nov 3, 6:39 am, Vihrea <Vih...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
I'll try another group, Sylvain.

Thank you for your response
--
======================
JG
Systems/DBA Supervisor
Vermont Agency of Transportation
GIAC Security Certified
MCSE, CCNA



"Sylvain Lafontaine" wrote:
First, this newsgroup is about ADP and has nothing to do with ODBC linked
tables.  While some of the people hanging around here will have a more or
less depth knowledge of ODBC linked tables, you can decrease the luck factor
by posting to a newsgroup more dedicated to that specific subject such as
m.p.a.odbcclientsvr or mpa.externaldata; where m.p.a. and mpa. are
abreviations for microsoft.public.access.

As to your question, personnally, I never heard of any white paper or of a
technical article or book specifically dedicated to that subject; however,
you can find here and there some anecdotal pieces of information that will
say:

For simple queries, Access will create a TSQL queries that will run on the
server and retrieve the primary keys of the desired result and will use
these keys to retrieve the other fields from the tables by group of 10 rows.
For more complexe queries, for example queries with complexe LIKE statements
or with VBA functions that Access cannot translate directly into T-SQL
functions; Access will retrieve all the rows and then make its own
filtering.  Of course, in this situation, the performance will rapidly sunk.

You can easily take a look at what Access is doing by using the SQL-Server
Profiler; which will show you exactly what Access is doing in every
situation.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

"Vihrea" <Vih...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:DBE003D3-BCD1-4672-900E-0679BE037166@xxxxxxxxxxxxxxxx
Hi All,

I'm having some performance issues with someoe trying to retrieve data
from
my SQL 2000 db using ACC2003 as the front end via linked tables.

I'd like to know more at a technical level about how ACC compiles an data
access path, hands it off os SQL2K , how SQL interprets it and then
returns
the dataset.

Finally, exactly what is ACC2003 then processing? Is it the entire dataset
(>6million rows).  Perhaps this user needs to redesign the query. I don;t
know enough about ACC's behavior yet.

Any references out there?

======================
JG
Systems/DBA Supervisor
Vermont Agency of Transportation
GIAC Security Certified
MCSE, CCNA, .NET- Hide quoted text -

- Show quoted text -

.

Relevant Pages

  • Re: Which identity?
    ... > Some postings I read suggested using MAXto retrieve the inserted record ... > each of these 3 SQL features? ... Another way to do it if using the .AddNew method on a Jet based ADO ... Dim rsTest As ADODB.Recordset ...
    (microsoft.public.access.queries)
  • Re: ADP and dynamic sql
    ... INNER JOIN UnitTbl AS u ON r.UnitKey = u.UnitKey INNER JOIN ... Could you give us a exemple of the builded sql string? ... Sylvain Lafontaine, ing. ... store the value of @dynsql in a table somewhere. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: MS Access 2007 closes and errors when clicking on last record
    ... Was BigInt a feature introduced with SQL 2005, or it existed in SQL 2000? ... Sylvain Lafontaine, ing. ... Independent consultant and remote programming for Access and SQL-Server ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Need help with ADP code
    ... You should the sql string into a variable and display it in a message box to ... Sylvain Lafontaine, ing. ... Dim strSQL As String, strOrder As String, strWhere As String ... 'Constant Select statement for the RowSource ...
    (microsoft.public.access.formscoding)
  • Re: Access 2003 max rows
    ... U mean with the SQL Studio EXPRESS? ... Sylvain Lafontaine, ing. ... Independent consultant and remote programming for Access and SQL-Server ...
    (microsoft.public.access.adp.sqlserver)