Re: White Paper on How MSAccess interacts with SQL Server Back End?
- From: "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
- Date: Fri, 31 Oct 2008 20:16:27 -0400
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" <Vihrea@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
.
- Follow-Ups:
- Next by Date: Re: ADV Conversion to .Net
- Next by thread: Re: White Paper on How MSAccess interacts with SQL Server Back End
- Index(es):
Relevant Pages
|
