Very Slow performance over a network (different cities) 4

[gtaborda]

Hi

I have a back end-front end MS Access database which runs perfectly and very fast on a local server (several PC's linked to a server, all in the same office)

However if I try to access same db in another office (we have 10 offices around the country) to do something like a simple query, it takes 20+seconds to do so.
The query is something line SELECT *.* WHERE CLIENT="PEPE" so nothing fancy.
The number of records is only like 500!

The number of people using the db is very small, possible 2-5 are connected at the same time, if that.

is there a known performance when a back end is being used across two places (in my case 600 kms away) ?

Just for testing purposes, I have tried to do the same process with MySQL and the performance is <1 second, while same query in MS Access takes 20-30 seconds or more.

I understand MySQL is much more powerful than Access however I cannot use MySQL for a number of reasons (I just did for testing purposes)

Is there a way MS Access performance can be increased in these cases ?
Note I have already ready and did follow all the guidelines to improve Access performance on linked tables, etc etc, but no changes in speed at all.

thanks in advance

 

[bubba100]

In the case you describe Access will never run as fast as SQL. Several years ago I was given the same task of splitting a DB and ran into the same time delays. We ended with the back-end in SQL and the front-end as Access. It isn't as fast as if the entire db was local to a machine, nor is it as fast at the remote offices as at headquarters but it's workable.

 

[MasterRacker]

Access is a shared file database. Even if you have a front/back end setup, the client is still scanning the back end file, not the server where the back end resides. This means your speed is limited to the speed of your WAN links.

You could have a copy of the back end at each site and do some sort synchronization, but that would probably introduce more problems than it solves.

Bubba100 used the best solution, which is to use Access for the front end but a true client-server database for the backend.

Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[gtaborda]

Ok, I thank you for your comments

MasterRacker, you've confirmed what I was afraid of...that Access runs as good as your WAN links (in our case, our WAN links are not very fast...)

bubba100, I wish I could use MySQL (or SQL Server) to do the job, unfortunately because of IT limitations, I cannot.
I will have to come up with some other solutions...

thank you both again and have a great weekend.

 

[MajP]

I was suspect of doing this at first, but it has worked fine for us.
I have a split db with about 40 users. Local workers link their front end directly to the backend. Remote users connect through a vpn. Most work from home, and the connection is slow. So each remote user has a replication copy. When they open their database they automatically synch to the master, when they are done they synch back up. I have automated most of the synching so they just confirm or deny the synchronization. The synching is very fast (couple of seconds).

Replication is a little finicky, but with a good design we have been successful. The other solutions would be better, but do not sound like an option for you.

 

[gtaborda]

MajP, thanks for your suggestions.
The thing is that I am using Excel as frontend, and Access as backend (access only contains certain data)
Only part of the data on the Excel sheet is saved/retrieved/updated etc in Access BE.

So (I assume) synching access db cannot be done from Excel?

 

[MajP]

Yes it can be done. You replicate the tables, not the front end.

So remote users could open their backends and do the synchronization. But instead you would want to code this from the front end.

1) Remote user opens excel
2) Using the access object model from excel, prompt the user if they want to synch to get the current data, and then synch if confirmed
3) user works in excel front end
4) Have a button in excel so they can do a synch at any time they desire
5) On close prompt them again if they want to synch

If you want to go this route I have a lot of code, I could send.

 

[MajP]

Only part of the data on the Excel sheet is saved/retrieved/updated etc in Access BE

This actually makes it sound like replication would be a good option. There are a few strategies to employ to make replication work well. The first is to really limit the amount of tables replicated. It sounds like basically you are pushing spreadsheet information on a limited set of data.

 

[MazeWorX]

I have to agree with MajP replication done well can resolve the issue you are experiencing I have successfully setup a replicated solution for approx 250 users from coast to coast over a wan and replication has performed quite well

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work