I have created a beautiful database, atleast I think, to track all the information for my organization. It is really not that large, only about 4 mb, without data in it. The problem I have is that my organization has many different sites across a large geographical area. We do not have the funds to set up t1 lines or any other type of leased lines. So I have set up a server at the main office and every site eventually will be able to connect via VPN. The problem I have that accessing this access database across the VPN is extremly slow. I have already tried using front and back ends, but it is still fairly slow. Is there anything else I can try to make this database available across the VPN? I thought maybe using a MySQL database as the back end. Any thoughts or suggestions would be greatly appreciated.
Access over VPN
- Thread starter rwilson32
- Start date
KenHigg
Registered User
- Local time
- Today, 02:49
- Joined
- Jun 9, 2004
- Messages
- 13,327
The most important pc. is to make sure the sever side is doing the processing. With Access, even if the tables are split out and reside on the server, if you need, say 100 records out of a poss. 10K records, Access must still bring all 10k records over to the client. The solution you mention is fine as long as the front end sends all the processing over to the server. If you don't require a really robust front end, a web solution with something like ASP or Cold Fusion might work.
I am looking at the possibility of my fe/be database being used over a VPN in the near future so I am interested in this discussion.
When you say 'make sure all the processing is done at the server' what exactly do you mean? Are you suggesting that queries should be on the server and only forms on the client?
From reading around I have got the impression that MySQL can provide substantial performance improvement, mainly due to cached queries. As our database gets larger (its currently at approx 35k records in the main table) we will probably upgrade the back-end to something like MySQL, SQLserver, Oracle etc. We have only had a preliminary look at the options but MySQL is looking very attractive, not only because of the price...
The above options seems to have a big advantage over Access in terms of server side processing.
I'd be interested in others comments whether they agree/disagree
When you say 'make sure all the processing is done at the server' what exactly do you mean? Are you suggesting that queries should be on the server and only forms on the client?
From reading around I have got the impression that MySQL can provide substantial performance improvement, mainly due to cached queries. As our database gets larger (its currently at approx 35k records in the main table) we will probably upgrade the back-end to something like MySQL, SQLserver, Oracle etc. We have only had a preliminary look at the options but MySQL is looking very attractive, not only because of the price...
The above options seems to have a big advantage over Access in terms of server side processing.
I'd be interested in others comments whether they agree/disagree
KenHigg
Registered User
- Local time
- Today, 02:49
- Joined
- Jun 9, 2004
- Messages
- 13,327
- When you say 'make sure all the processing is done at the server' what exactly do you mean? Are you suggesting that queries should be on the server and only forms on the client? -
There are several ways to get to data in a SQLSever db. A common way is to just attach to them through ODBC. Using this method, the front end is still going through Jet and odbc layers. This means you will still bring the entire record set over to the local machine if you hit the tables with a query on the local machine. Unless, of course, you a pass-through query, which sends a sql string to the db sever which will in turn, send back the requested sub set of records or... you set your query up as a view in SQL sever (Basically a query that resides on the db server.
This brings up MS Access Projects. which allows your database to communicate directly with the db server.
One other important consideration is the hardware / software on the clients on the vpn. If you have a bunch of identical workstations (Exp: XP, XP office, etc.), then a custom solution using MS Access/SQLSever will be great. But if you have a lot of machines that have different software running, the a browser (ASP, etc) solution may be a better idea...
There are several ways to get to data in a SQLSever db. A common way is to just attach to them through ODBC. Using this method, the front end is still going through Jet and odbc layers. This means you will still bring the entire record set over to the local machine if you hit the tables with a query on the local machine. Unless, of course, you a pass-through query, which sends a sql string to the db sever which will in turn, send back the requested sub set of records or... you set your query up as a view in SQL sever (Basically a query that resides on the db server.
This brings up MS Access Projects. which allows your database to communicate directly with the db server.
One other important consideration is the hardware / software on the clients on the vpn. If you have a bunch of identical workstations (Exp: XP, XP office, etc.), then a custom solution using MS Access/SQLSever will be great. But if you have a lot of machines that have different software running, the a browser (ASP, etc) solution may be a better idea...
