Can I split a backend into two

[Shallo9]

Hello All,

I've got a Split Database (Front End & Back End). So far it was all good until recent when a department placed in a different city (200 Miles apart) wants to use my application. The Application has gone very slow for them when they access the back end.

Could some one help me in finding a way to speed up their access.

Things which I've already done.
Analyzed Tables
Analyzed Performance

The only best way I could think of is to have two separate Back Ends for both cities and synchronize them may be every hour or so. Is this possible and how much feasible is this? Or is there an even better way to accomplish this.

Seniors, your comments would be highly appreciated.

 

[CJ_London]

The problem is to do with the connection you have between the two cities. If you were to synchronise, this would be very slow and prone to failure if there is a blip in the connection which synchronising.

I would look at the option of moving the back end to sql server or, if you have it available, make use of Citrix or equivalent.

Depending on how critical the database is to your business you may be able to pursuade the company to improve the bandwidth.

Also, just to check, many corporate wireless networks are not really up to the job of linking tables so make sure your users are wired into their network and not using wireless.

 

[missinglinq]

As you've discovered, the Access Gnomes don't play nice when used over a wide area network. Albert Kallal has an often quoted article on the subject of WANs that might be of help to you. You can find it at

http://www.kallal.ca/Wan/Wans.html

Linq ;0)>

 

[The_Doc_Man]

The only way I would approach something like this if I did not have a reasonable common server (SQL server or ORACLE or something similar) is to do two different things.

1. Make your Access database entirely transactional. Don't EVER directly update a table. ONLY add records that will have the effect you want as a command or transaction.

2. Each database should have a method of exporting the transactions as a text file. Then spawn a command shell to transmit the file containing the transactions, and use batch-oriented SFTP or something else that does encryption and checksumming from point A to point B. Devise a naming scheme and a transmit/receive scheme so that each site drops its files into a specific place and picks up its partner's files from a different place. Then have your session of Access that imports the text files to do your updates. This probably means that you would have one machine at each site dedicated to exchanging transactions. There is a way for you to compute checksums of various records or files to verify correct transmission. Which you should use if you want to keep your sanity.

Believe it or not, it is #1 that will be harder to do.

That's if and only if you cannot get something like SQL server or ORACLE server.