Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Sep 2004
    Location
    Sacramento, California, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    data sent from backend file (Access 2002)

    There is a lot of discussion by the network group where I work stating that when you have a split database Access creates a lot of network traffic in terms of the amount of data sent across the network as opposed to other database products, there is a project to move the backend tables to SQL Server which will improve performance on the user side, but does it change the amount of data sent across the network? I understand that you can use functions with SQL Server to improve the amount of data sent with existing Access front ends, but is the amount of data sent significant enough to justify moving all of the front ends to .NET? The databases are small in terms of user numbers and in terms of data, no huge amounts of anyting. My personal experience is that Access front ends coupled with SQL Server back ends is a nice solution for smaller databases, but I don't know how Access sends data to client front ends from .mdb backends or SQL or how other products might work to justify my position with the network people. I have not considered using .adp front ends I have not had any experience with them and Microsoft is now recommending not to use them. Any thoughts or experience would be appreciated.

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: data sent from backend file (Access 2002)

    From my limited understanding when you have a Access backend there is a high amount of network traffic as all processing is done in the front end.

    With SQL server as the backend the amount of data going across the network is minimal since the back end is doing the processing before it sends just the data it needs to back to the front end.

    Again microsoft seems to recommend an Acces front end for SQL server backends. I have heard it takes a lot longer to develop .net front end than it does in Access.

    As I said, this is mainly what i have heard.

  3. #3
    4 Star Lounger
    Join Date
    Sep 2004
    Location
    Sacramento, California, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: data sent from backend file (Access 2002)

    I have heard the same, and am looking for some type of confirmation. It does takes a lot longer to develop a .NET front end, and to redevelop functioning systems seems to me to be an unnecesary effort. My recommendation has been just to move the back ends to SQL Server.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: data sent from backend file (Access 2002)

    Simply migrating the backend to SQL Server will not necessarily diminish network traffic, since the Access frontend will still be doing most of the data processing. To let SQL Server do the hard work, you need to convert your queries to pass-through queries, or define views in the SQL Server database.

    For example, let's say that you have a table with 100,000 records, and you have a query that uses selection criteria to return 100 of those records (to be displayed on a form or report).

    With a normal query, Access will sequentially transfer all 100,000 records across the network into the memory of the PC running the query, and discard the 99,900 records that don't meet the criteria.

    With a pass-through query, SQL Server will apply the criteria, and send only the 100 records that meet the criteria across the network to the PC running the frontend - an enormous reduction of networtk traffic.

    See:
    How to create an SQL pass-through query in Access
    Microsoft Access Pass-Through Queries to SQL Server
    ACC: How to Simulate Parameters in an SQL Pass-Through Query

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: data sent from backend file (Access 2002)

    I've had alot of experience working with Access using an SQL backend, and the answer is "it depends".

    As has been stated, under normal circumstances Access does all its processing in the frontend. However, this does not mean that if you have a 100,000 record table that every time you query this table all 100,000 records come across the network to the frontend. Access can use the indexes to reduce the # of records based on the selection criteria. This happens regardless of the backend.

    However, using pass-through queries with an SQL Server backend will DRAMATICALLY improve performance. In these situations, all the processing is done by the SQL Server (which is much faster) and only the results come back across the network (reducing network traffic).

    However, you can't use pass-through queries for everything; one primary reason being that they are not updatable. They are great for filling listboxes and in reports Therefore, you have to adopt preventive measures to minimize the need for Access to work with large recordsets that you need to update. For example, let's say you have the usual Customer form. Many such forms are bound to a table so Access always has to pull-down all the records in the table. Instead, you can create a recordset with only a single record! (See Create Powerful Bound Forms ) Another thing to do is only populate listboxes and subforms when they are needed. For example, you may have that Customer form with several tab pages, one of which contains a Transaction History subform. Don't load that subform immediately, only do it when the tab page is displayed. And don't use the normal Master/Child properties. This will requery the subform every time another customer is displayed (even if the tab page isn't being displayed). Put the selection criteria in the subform's recordset query, then manually requery the subform whenever the tabpage is selected.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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