Network performance issue

qwertyjjj

Registered User.
Local time
Today, 06:44
Joined
Aug 8, 2006
Messages
262
I have designed an Access DB with various forms to display data populated by queries. This runs fairly efficiently when on the same system.
As soon as I try to split the DB into a front and back end and place the data part in a network folder location (across a WAN) the performance is incredibly slow.

The strange thing is that when testing the queries out on SQL server (just as a test), they run quickly and the data can't be more than a few kb in size.

As it was explained to me the other day:
"Access is an file oriented database. There is no client-server code, so all data manipulations are done on client side anyway. Access has to load data across network."

This sounds to me like Access would be loading all 20Mb of data across the WAN and processing it on the client end rather than running the query at the back end first and only sending through 10kb of data.
Is this true?
 
Thanks for that.
A few questions though...
When you migrate everything to SQL Server (under MSDE) there are 2 options:
link tables
make ADP file (client server)

If you just link the files, where are the queries processed? On the client or at the SQL server end.
If you convert to ADP, I assume it is all done on the SQL server end?
As Access still has all the queries, does it pass the whole query through the network?

...or do I then have to convert all the queries to stored procedures and change the code ?
 
When you have queries to an SQL server, you want to choose the options that make your queries all of the PASSTHROUGH variety. Don't leave it to chance. Actively pursue this situation. You'll be VERY glad you did.
 
So to do this I'd need to split the DB and link all the tables through to the SQL back end.
How do you then make sure they're of the passthrough variety - are there some properties or something?

Maybe it's better just to make an ADP file and change everything to MSDE ?
 

Users who are viewing this thread

Back
Top Bottom