Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Network speeds (access 97)

    I have to supply a database to a member of staff who works in a sub office on a remote part of our WAN. The data part of the database (back end) needs to reside at our head office to allow the intranet server/asp pages access to the data for the intranet.

    Are there any ways of setting up the front end so that the response times do not make the database unusable ? Currently, in test mode, it takes 15 seconds for a form to load, and that is with only 7 records in the table. Once loaded however the records change almost instantly.

    thanks in advance for any suggestions you may have

  2. #2
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Network speeds (access 97)

    Is the backend part of the database Access?
    Not sure if this is a possible solution for you due to costs etc, but SQL server is very good for backends as it is far more stable than Access. We have an SQL database that is accessed locally, over a Wan and over the internet and the only problem we've ever had with it was when somebody pulled the wrong plug out of the wall! It won't speed up access for the user over the Wan but it should stop the database becoming unstable.

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Network speeds (access 97)

    Let's back up one step - is the database split into a fe and a be? If it isn't, split it. If it is, have you moved the fe to the remote WAN workstation? If you haven't, do so as you are loading every form and every report across the WAN, which takes lots of time. If you have and it's still sluggish then there are two possible approaches.

    The first is to use SQL Server as Phil suggests - the primary reason for it being quicker is that you don't pass an entire recordset across the network with SQL, while Access generally does. The other approach is to go to something like Norton (Symantec??) PCAnyWhere, Citrix or W2000 Remote Services. In that case you are simply sending screen paint messages to the remote workstation, and all the database processing goes on at the central site. Hope this helps.
    Wendell

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Network speeds (access 97)

    both front and back ends are Access - one has only tables and is at head office, the other is all queries,forms,reports and is sitting at the far end of the WAN.
    SQL seems a bit of overkill, the database is not likely to be very large and needs to be developed quite swiftly (dont they all!)

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Network speeds (access 97)

    yes the db is split. yes the be is at head office and the fe is on the local machine. yes it is still slow <img src=/S/sad.gif border=0 alt=sad width=15 height=15>

    Would the conversion of the access be take long and would the fe still get the data in the same way ?
    How does SQL know to only send one record at a time ?

    Thin Client is out of the question, the project does not warrant any expense <img src=/S/money.gif border=0 alt=money width=17 height=15>, other than my development time.

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Network speeds (access 97)

    I'd say that under any normal circumstances, WAN access to an Access DB by dial-up is going to return unacceptable performance. It isn't the database, it's the need to transfer those records both ways across across that slow connection. In 2000, you could at least use disconnected recordsets and other ADO goodies that would provide some improvement, with the updates happening intermittently instead of constantly, but I think you're out of luck with 97.
    Charlotte

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Network speeds (access 97)

    Conversion from Access to SQL Serve isn't a lengthy process - especially if your tables are small and it sounds like they will be at least to start. However SQL Server is a rather different beast, so learning enough to make it work in a few days isn't feasible. If you have a resource who could assist you, it should be pretty smooth though. There is an Upsizing Wizard available (from MS) for Access97 that works reasonably well. One of the more complicated steps is setting up ODBC data sources on each of the workstations - once that's done the tables look pretty much like linked Access tables, except that they have a different symbol.

    The reason that SQL is more efficient is that it only sends the records you request, while in Access the entire table is sent to you and the query is then executed on your workstation. So if you have several hundred records and you only want to view 1 of them, SQL can provide a definite improvement. All this of course assumes that you have SQL Server available and ready to be used - if not there are significant licensing costs and a server to be dealt with.

    As to thin client, the most economical would be PC Anywhere as it cost approx US$100, but it does require a workstation to be setup at the central site that would be the host. Hope some of this helps! <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16>
    Wendell

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Network speeds (access 97)

    The other thing to keep in mind is that justing Jet queries in the front end with a SQL Server back end still slows the front end response down. Jet just isn't efficient when it comes to queries.
    Charlotte

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Network speeds (access 97)

    Sorry, the details may not be detailed enough. The remote office is on a leased line - 128K.

    I would like to stay away from replication but this looks to be the only way to go....
    unless I take the leap to SQL

    thanks for the advice

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Network speeds (access 97)

    justing - Verb - ' to use a Jet database engine' !!!!?

    OK, hair splitting over. Are you saying Charlotte , that even if i go to the trouble of converting the database to SQL but continue to use the Access fe then the response will not improve ? If so, i take it then that i will need to create a stand alone fe with VB then ?

    Does it seem fair to say that Access is not an enterprise solution ?

  11. #11
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Network speeds (access 97)

    I'm suspicious that your problem may like at the workstation, not with the leased line. If your test environment only has 7 records, and it takes 15 seconds for the form to load, you may have other performance problems with the workstation causing slowdowns. Have you tried moving the back-end to the workstation to see what kind of performance boost you get? The workstation may be running 32MB of RAM and swapping to the hard drive like crazy or the user may have 20 apps open.

    If you do get decent performance with the back-end, consider making it the master and replicating to a copy at your main office so it can be reflected to the web. If nobody at the main office updates data, you could simply copy the back-end mdb file once or twice a day. BTW, Charlotte is correct that native SQL is faster (sometimes a lot faster) than using an ODBC linked table using the Jet engine. However, queries run against SQL tables are generally optimized to retrieve only the desired records at the server and then pass them to Jet (unless you join to an Access table - then all sorts of bad things happen), rather than retrieving the entire table as Jet would do if you had a Access backend, and then performing the query on the workstation.

    Hope this rambling helps solve your problem.
    Wendell

  12. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Network speeds (access 97)

    No, I'm saying that if you use Jet to query SQL tables, you're queries will still be slow. It may improve, but it will still be slow. If you want speed, you'll need to rewrite to call SQL stored procedures to return your records or run passthrough queries.

    Access/Jet is not an enterprise solution, but Access as a front-end to SQL Server is fine.
    Charlotte

Posting Permissions

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