Access And SQL Server with ODBC

[Savvoulidis Iordanis]

After trying to use Access MDE in connection with Access MDB backend, I
finally made my opinion : Don't use it outside a LAN. Even an ADSL 1Mbit
line for a VPN can't offer speed. This is because of Access's nature, using
file sharing to access the MDB file. All data must be downloaded to the
client and then, the filtering is applied. TOO DAMN SLOW.

So, before I make my next move, I'd like to ask a few questions:

If I move the data only, to a SQL Server, which Access links to it, through
its ODBC driver, how is data retrieved? Does Access use the same method as
in the MDE/MDB scenario ? (all data comes to the client and then used?) Is
TCPIP and TDS involved, as in a Access project? Also What happens to the
linked tables?

I really want to minimize the data retrieved, to only what data my queries
expect. Also I'd like to stay with the application as it is so far, and not
to go to stored procedures yet, or other types of changes to the application

TIA
Savvoulidis Iordanis
Greece

 

[Rick Brandt]

After trying to use Access MDE in connection with Access MDB backend,
I finally made my opinion : Don't use it outside a LAN. Even an ADSL
1Mbit line for a VPN can't offer speed. This is because of Access's
nature, using file sharing to access the MDB file. All data must be
downloaded to the client and then, the filtering is applied. TOO DAMN
SLOW.
So, before I make my next move, I'd like to ask a few questions:

If I move the data only, to a SQL Server, which Access links to it,
through its ODBC driver, how is data retrieved? Does Access use the
same method as in the MDE/MDB scenario ? (all data comes to the
client and then used?) Is TCPIP and TDS involved, as in a Access
project? Also What happens to the linked tables?

I really want to minimize the data retrieved, to only what data my
queries expect. Also I'd like to stay with the application as it is
so far, and not to go to stored procedures yet, or other types of
changes to the application
TIA
Savvoulidis Iordanis
Greece

The answer is "it depends". Generally, plain old local queries against ODBC
linked tables will still have most of the processing performed on the server.
There are queries though that would force an inordinate amount of data over the
wire and do a lot of the processing locally. You would need to move the data to
SQL Server and then TEST.

Any queries that do not perform well would likely do better if changed to a
passthrough query or stored procedure, but you will probably be surprised just
how few of them fall into that category. You certainly won't want to just
blindly convert them all. Sometimes the passthrough or stored procedure will
actually be slower (or at least provide a slower response to the user).

Using views on the server to perform joins between larger tables and then
linking and further querying against the view is often a big benefit.

 

[Albert D.Kallal]

After trying to use Access MDE in connection with Access MDB backend, I
finally made my opinion : Don't use it outside a LAN. Even an ADSL 1Mbit
line for a VPN can't offer speed.

Well, that is because a 1 mbit is 100 times slower then your cheap-o office
100bast T network.

Why would you not think that something that is 100 times slower is not going
to be slower????

Think about the above for a minute. We are not talking about a connection
that is 10%, or even 50% slower..we are talking bout 100 TIMES....

All data must be downloaded to the client and then, the filtering is
applied.

The above is complete 100% wrong. ms-access does not download all of the
data. The reason why what you have is slow is because it is 100 times slower
then your office LAN.

So, a JET file share WILL download all of the table data if the search
criteria is not indexed ( but how often does that happen??) (answer...not
very).

You can read about a solution here, and why you are experience slowness.
READ carefully about the part that CLEARLY states that ms-access does NOT
download all of the data and then apply a filter.

http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html

The problem (and solution) is to make sure you ALWAYS restrict the number of
records you load into a form. This advice applies to when you use a jet file
share, or sql server for the back end...

Anyway..read the above article....