Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2013
    Posts
    15

    Unanswered: Best Way To Access Database On Server, Access 2003

    Hello,

    I have a large Access db (300 MB at least) which is located on server. It is called patients.mdb and it is a Access 2003 version. This db is used by 4 PCs:

    1. PC i - Win XP - Office 2007
    2. PC ii - Win XP - Office 2003
    3. PC iii - Win XP - Office 2003
    4. PC iiii - Win XP - Office 2003

    On all these PCs there is one simple shortcut which opens patients.mdb on server.

    This is the *current* condition of my db:

    - it works very slow,
    - it is huge and it takes a lot of space (300 MB),
    - some users can't open db from shortcut from desktop, instead they have to open Access and than open db from shortcut.

    This is what I wand in *future* for my db:

    - to work much faster,
    - to avoid error caused from opening db using Office 2003 and Office 2007 from two or more PCs,
    - to decrease size of db,
    - to avoid craching db and similar.

    My question is how can I realize my *future* requests and what are your recommendations based on your experience.

    Any help is appreciated and many thanks in advance for prompt reply!
    Adi

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The fist thing to do consists in splitting the database in one front-end (queries, forms, reports, macros, modules) and one back-end (tables and relationships). Each client receives its own copy of the FE, while the BE remains on the server.

    Access provides a tool to help you in doing this. Keep a backup copy of the database as it is now before doing anything, though.
    Have a nice day!

  3. #3
    Join Date
    Mar 2013
    Posts
    15
    Thank you very much for your suggestion.

    I have made changes based on your suggestions and here are some important updates:

    - I have split database to FE and BE,
    - I have made copy of every FE on 10 user PCs (yes I had to install it on extra 6 PCs) and I have placed BE on server.

    These are the results:

    - Users are complaining still on slow speed specially when they make some Search ot run Queries,
    - On some PCs I *had* to remove copy of 'local FE' and put direct shortcut to 'server FE' because it worked faster.

    I am not user if I am right but it looks like that my server is full and lacking of space and memory because I have done everything suggested but no result at all unfortunately;(

    I plan to put this BE part of database on brand new server and then to check if that helps..

    If you can give me suggestions or warn me if I made something whrong, please do not hesitate to write.

    Thanks,
    Adi

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I doubt moving to a new server will make that much difference. Increasing the amount of memeory in the server may help
    Checking its network connectivitiy may help (speed, throughput and number of hops to the clients. Acces is a file server system niot a client server system.

    If a search is causing delays check what the search is on, make certain the most commonly used columns are indexed

    Use the showplan/query plan to find out if your queries can be improoved.

    Check your network has adequate capacity. Access can be a network hog as it has to read all the index and or data to process.

    What you could consider doing is miving to a server database
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2013
    Posts
    15
    I doubt moving to a new server will make that much difference. Increasing the amount of memeory in the server may help
    Checking its network connectivitiy may help (speed, throughput and number of hops to the clients. Acces is a file server system niot a client server system.

    If a search is causing delays check what the search is on, make certain the most commonly used columns are indexed

    Use the showplan/query plan to find out if your queries can be improoved.

    Check your network has adequate capacity. Access can be a network hog as it has to read all the index and or data to process.

    What you could consider doing is miving to a server database
    I agree, moving to new server wont solve the issue.

    Since I also thought that network connectivity might be the issue, I have replaced CISCO switch and I have reduced number of hops to the clients so right now I am waiting results from users. They already said they notice some improvements but I am not sure yet.

    I definitely plan to upsize Access to MSSQL Express db in order to speed up the work on this db.

    Greetings,
    Adi

  6. #6
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    I have made a career out of upsizing databases to sql and maintaining access front-ends. Migrating to sql will not guarantee a performance improvement and in fact some routines my run slower. Definitely if four users use the application - you need too split the database and link to the data files on the server. SQL will give you data integrity enhancements, security enhancements and stability enhancements - but unless you truly start migrating your action query objects to currentdb.executes or passing the sl statements into a stored procedure using the ADO command object - there probably will not be a noticeable performance increase.

    With that said - splitting the database and eliminating the 300 mb pull across the network for various users will increase the performance of your system.
    Dale Houston, TX

Posting Permissions

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