Hi All.
I recently deployed a BE-FE version of a new Access Database in my firm, after restructuring the old one which was just a standalone mdb file. We have a normal 100Mbps LAN. Its recently deployed and so far it seems to work fine. However, now my firm wants me to try out and deploy the same thing over the WAN (its in form of VPN) that they have got over their 1 other remote office. The data transfer rate is 1.5Mbps/512kbps (download, upload on the remote office side). I tried to deploy the FE at the remote office keeping the backend at the main office, but that's VERY SLOW. It takes minutes to open up forms and reports. Also, I read somewhere that in order to process queries, Access sends all the data accross to the client machine, instead of just the required data, and all the data is processed on the client side. (Well, that's the whole point of having a FE-BE structure to decrease the load on server, and make use of client machine resources, isnt it?) Now I am in a bit of a fix under two conflicting scenarios. For Local office I need a FE on client machine that does all the processing and for the remote office I need something that will do the processing on server side and just send the required data across. What do I do now? I have thoguht of some ideas which I am highlighting and would appreciate some advice on those.
1. Create a similar FE and BE on the remote office and then link the two BEs. But here also I guess the same problem will arise of ALL the data transferring back and forth during each query run or loading forms and reports.
2. I dont want to use MSSQL since it is very expensive and MSDE because it is limited to 5 users connecting at a time simultaneously. (An off-the-track question for this section, but supposedly in line with the solution) Would mySQL suffice?
3. If I use a combo of mySql and Access and use pass through queries, will it solve the problem? I am asking this because Access mgiht not be able to handle the running of multiple queries simultaneously as it will seriously mar the performance of database.
3. Is there anything else I should be looking at or I have missed? I would really welcome if there are any more suggestions, which I am sure, will be.
P.S. All the tables are linked and normalized. However, the company requires a lot of forms and rpeorts and hence the Front end is almost 250 MB in size. (This is just for reference).
Sorry to post such a long question, but did not want to miss out on any facts. Thanks to all in advance for taking time to answer my queries.
Regards,
Prathmesh
I recently deployed a BE-FE version of a new Access Database in my firm, after restructuring the old one which was just a standalone mdb file. We have a normal 100Mbps LAN. Its recently deployed and so far it seems to work fine. However, now my firm wants me to try out and deploy the same thing over the WAN (its in form of VPN) that they have got over their 1 other remote office. The data transfer rate is 1.5Mbps/512kbps (download, upload on the remote office side). I tried to deploy the FE at the remote office keeping the backend at the main office, but that's VERY SLOW. It takes minutes to open up forms and reports. Also, I read somewhere that in order to process queries, Access sends all the data accross to the client machine, instead of just the required data, and all the data is processed on the client side. (Well, that's the whole point of having a FE-BE structure to decrease the load on server, and make use of client machine resources, isnt it?) Now I am in a bit of a fix under two conflicting scenarios. For Local office I need a FE on client machine that does all the processing and for the remote office I need something that will do the processing on server side and just send the required data across. What do I do now? I have thoguht of some ideas which I am highlighting and would appreciate some advice on those.
1. Create a similar FE and BE on the remote office and then link the two BEs. But here also I guess the same problem will arise of ALL the data transferring back and forth during each query run or loading forms and reports.
2. I dont want to use MSSQL since it is very expensive and MSDE because it is limited to 5 users connecting at a time simultaneously. (An off-the-track question for this section, but supposedly in line with the solution) Would mySQL suffice?
3. If I use a combo of mySql and Access and use pass through queries, will it solve the problem? I am asking this because Access mgiht not be able to handle the running of multiple queries simultaneously as it will seriously mar the performance of database.
3. Is there anything else I should be looking at or I have missed? I would really welcome if there are any more suggestions, which I am sure, will be.
P.S. All the tables are linked and normalized. However, the company requires a lot of forms and rpeorts and hence the Front end is almost 250 MB in size. (This is just for reference).
Sorry to post such a long question, but did not want to miss out on any facts. Thanks to all in advance for taking time to answer my queries.
Regards,
Prathmesh