Results 1 to 14 of 14
  1. #1
    Join Date
    Oct 2003
    Posts
    78

    Question Unanswered: Loading Access DB over slow WAN

    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

  2. #2
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    107
    Hi there,

    1) Pass-through queries will be much quicker as the processing will be done on the mySQL server and NOT on the access FE. The only downside is that pass-through queries return read-only datasets meaning that your users will not be able to edit the data that is returned.

    2) Seen as you have a VPN connection would it be possible to use some sort of thin client software such as Cytrix or Terminal Services? This would mean that the only data being sent across the WAN would be screenshots so would be much faster...

    3) The only other option is to look at re-designing/re-writing the access database so that it does all of the work on the server rather than the client - there are ways of programming this. Also, you could upgrade to an MS Access Project file which is specifically designed to work with server based DB engines such as MSSQL and MySql.... (I've not actually used this myself tho' have thought about it..)

    Matt

  3. #3
    Join Date
    Oct 2003
    Posts
    78
    Hi MMcDonald,
    many thanks for the reply. I have not used pass-through queries at all, just read about it, so am not sure about how exactly it works. You pointed out that pass-through queries return recordsets with data that cant be edited. So, in a way, I would be able to use those queries for reports only, and not for forms where data would be entered. So then what do I do if need to use bound forms? I presume, its going to be a problem then since all records pertaining to all the tables, linked to the form, will be passed. Any other suggestions?

    I am also looking into the thin client concept that you mentioned. Sounds gr8 bcos there are only 1-2 people at both remote offices using the thing. So number of users is also not so great as to warrant setting up a whole db over there.

    Regarding ADP, how good isn mysql at processing queires? Lets say i have a proper normalized structure with ADP front end and mysql BE, will it work well? I mean is mysql fast enoguh and powerful enough to support simultaneous processing of several queries being generated from several client on the LAN?

    The main reason for splitting Access into FE/BE was that if users tried to use the same form simultaneously, they would run into lot of troubles like one user opens one record, the other one opens another and the record on the first form changes to something else. There was a whole lot of other problems too, hence I did the splitting so that multiple users would do the work simultaneously. Would ADP work in the same way? Would there be an ADP file for each client or one common ADP and all users access that ADP only, lets say via a shortcut on the deskktop?

    Thanks in advance for reply.

    Regards.

  4. #4
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    107
    Hi,

    What you say about pass-through queries is correct. They are ideal for reports but they can also be used for populating list boxes, combo boxes etc. - basically anywhere where the data doesn't need to be changed.

    With regard to bound forms, it really comes down to how you design the forms. Basically, if you want the forms to be as fast a possible you only bind data to them when the user needs it. For example, you might have a form that allows the user to view all customers. Instead of binding all the customer data to a form every time it opens you could have a dropdown list (populated using a pass-through query for example) listing all the customer names. The user then selects the Customer name form the list and THEN the data is bound to the form - but only one customer is bound at a time so it's quicker if you know what I am mean. I'm sure if you search for "building unbound Access applications" on the net u will find stuff about this.

    I'm told mySQL is excellent but apparently an ADP file can only be used with MSSQL - sorry if I misled you. If you want to use MySQL as the backend the only option is connect to MySQL using ODBC and a standard .mdb access file.

    Basically, if you can afford it go for thin-client. If you have a W2K server it comes with Terminal Services anyway so you could play about and test it first before commiting to this route.

    Hope this helps...

  5. #5
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Prathmesh: Although a little late, I just wanted to clear up one misconception you had. MSDE is NOT limited to 5 concurrent users. Unlimited numbers (Well, actually, you have the same limits as SQL Server) of concurrent users can be connected. However, as each user (over 5 on MSDE 1, 8 on MSDE2) connects, the total throughput begins to degrade. MS wanted to ensure that end users would still have a reason to upgrade to full-blown SQL Server, LOL. Essentially, MSDE uses bandwidth throttling so that the network performance of MSDE approximates that of Access.

    So, in your case, MSDE would perform at LEAST as well as Access would under the same conditions. However, since you CAN specify server-side cursors, the effective performance of MSDE in a network environment can be much greater. And, by designing your application so that a single client connects, does what it needs to, then disconnects, you can have near SQL-Server performance at no cost to you. And, finally, by installing MSDE, you could enjoy a truly stable database platform. I can't tell you how many times I've had to repair a corrupted Access database in a multi-user environment.
    Last edited by loquin; 08-24-04 at 20:14.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  6. #6
    Join Date
    Oct 2003
    Posts
    78
    Hi Loquin,
    Thanks for the input, much appreciated. It was really a great help regarding the clarifications that u put forth for MSDE. I cant agree with u more on the Access Corruption. It really is a pain. We also used to have that sort of problems here, but since I put in the new structure, thank GOD, nothing has gone wrong so far. But I know it is going to happen one day. I'll try to go more over the MSDE thing and work out its feasibility in my working environment. Thanks and have a great time.
    Cheers:
    Prathmesh

  7. #7
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    107
    You CAN ALSO specify server-side cursors in Access.

  8. #8
    Join Date
    Aug 2004
    Posts
    5
    Why not dump the Access FE and write your app in VB6 or VB.NET ?

  9. #9
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Quote Originally Posted by mmcdonald
    You CAN ALSO specify server-side cursors in Access.
    Well, you can specify it, but with an access DB file as the back end, it doesn't matter; the entire data set is transfered to the client before any querying takes place. i.e. high network bandwidth.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  10. #10
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    107
    I disagree, it comes down to how you design your database. Let's say we have a SQL Server back-end database and an Access FE. Within SQL Server we have a table 'tbl_Customers'. Using the traditional method for linking this within Access we create an ODBC linked table (tbl_customers) and then create a form and set the recordsource to 'tbl_cUstomers'. In this case whenever a user wanted to look at a particular customers details then the entire data set WOULD be sent across the network before it is queried - using a server -side cursor would not have any effect in thsi case.

    However, if we were to create the same form but this time leave the record source blank (in effect an unbound form). Then, when the user selected the customer that they want to view we use a server side cursor to execute a query that returns to the Access FE ONLY the customers details that the user is interested in - the recordset is then disconnected again. Using this method Access is working in exactly the same manner as any VB 6 or VB.net application.

    Also, Pass-Through Queries, ALL of the processing is done on the server and results returned to Access.

    There is no reason why Access cannot be used as a front-end to a SQL Server database - I known, I've done it several times quite successfully.

    Here's some code that I've used in one of my Apps:

    Dim cn As ADODB.Connection
    Dim rst As ADODB.Recordset

    Set cn = New ADODB.Connection

    ' With Integrated Security
    cn.ConnectionString = strConnectionString

    cn.Open 'Now you have an open connection to SQL

    Set rst = New ADODB.Recordset

    rst.ActiveConnection = cn
    rst.CursorLocation = adUseServer
    rst.CursorType = adOpenKeyset
    rst.LockType = adLockOptimistic

    rst.Open "SELECT tblMaterialCategory1List.tblMaterialCategories1_ID FROM tblMaterialCategory1List " & _
    "WHERE (tblMaterialCategory1List.tblMaterialCategories1_I D = " & Me.cmbCategory.Column(0) & ")"

    rst.Delete
    rst.Close

    cn.Close

    Set rst = Nothing
    Set cn = Nothing

  11. #11
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Yes, but Prathmesh is using Access as the BACKEND also. My discussion above was pertaining to the differences between Access and MSDE/SQL as the back end.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  12. #12
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    107
    Appologies...

  13. #13
    Join Date
    Oct 2003
    Posts
    78
    Hi All.
    Loquin and MMcdonald, your suggestions were quite valuable. I have tried using unbound forms and populating them only when user selects a particular criteria. Can notice the difference. Although, I need to mention here that I have done this using "On-Load" Property of form, and not pass-throuhg queries, since the data needs to be updated. One thing Mmcdonald, the scenario you described along with the example, would that work with MS Access or only with MSDE or MSSQl? As Loquin suggested for server side cursors, that since it is Access DB backend, all the data would be returned anyway, so should server-side cursors be used or not in my scenario? Will I get improvement over my current condition by using those?

    Thanks in advance, guys, for all your time and efforts. Cheers.

  14. #14
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Server side cursors are ignored for Access back end, since the Access data file is not a server...
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •