Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Posts
    2

    Angry Unanswered: MS Access + Database Splitter

    Hello,

    Our software was initially developed for single user access, but with the ability to be used "multiuser". Now that the network databases are growing above the 400mb mark access to the system at times can be unusable. Ive read a few articles on the database splitter and it seemed as though it may solve some of my performance issues. However, since splitting my database and placing the back end of the server (linked through UNC name not a drive mapping) its become slower. Im new to the concept of linked tables and this perhaps i might have misunderstood the idea. I followed the wizard through and configured the link tables through the manager. Have i missed something? Is performace not the deciding factor of splitting a database?

    Thanking you in advance.

  2. #2
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238

    Re: MS Access + Database Splitter

    Originally posted by homeaway
    Hello,

    Our software was initially developed for single user access, but with the ability to be used "multiuser". Now that the network databases are growing above the 400mb mark access to the system at times can be unusable. Ive read a few articles on the database splitter and it seemed as though it may solve some of my performance issues. However, since splitting my database and placing the back end of the server (linked through UNC name not a drive mapping) its become slower. Im new to the concept of linked tables and this perhaps i might have misunderstood the idea. I followed the wizard through and configured the link tables through the manager. Have i missed something? Is performace not the deciding factor of splitting a database?

    Thanking you in advance.
    There could be many reasons why the database is slower. A common problem is the database design. An optimised database needs to be properly normalised for data entry (ie multi tables with relationships between them one-to-many, many-to-many etc). Reports can be optimised by flattening the tables into one reporting table. Indexes need to be used appropriately as well.

    A big problem for Access is that it does not work too well over a network or in a multi-user environment. Thats not to say it can't be done or shouldn't be done you just need to be aware of its limitations. For example Access processes all data request on the local machine, regardless of where the data is stored. This means it will pull across the network all the data before it will process it with a query or any other action you want to do with it on your local machine. SQL Server or Oracle processes the data on the server and only sends what is requested.

    Access can not handle too many users using the database at any one time. In my experience anything above 5 users and you will get all sorts of problems. A big problem with multi-users (any number of users) is record locking. If you are editing a record and I am also editing the same record whos changes are saved? Access can also lock records if a user is processing them.

    Another problem with Access is its size limitation of 1Gig. If your database ever goes above that size it will fail badly - ie the system tables can become corrupt so you need to make sure that you repair and compact the database on a regular basis.

    You could use replication to overcome many of these problems. The help file is quite good with hints and tips on optimisation

  3. #3
    Join Date
    Nov 2003
    Posts
    2

    Question MS Access + Database Splitter

    Hi again,

    Thanks for the comments. A brief background on the system;
    I use disconnected recordsets to allow user access to the system. I also use user id's for locking purposes. Basically i dont have a bound connection to the database, requests for updates/inserts/selects and so forth are controlled through once off hits (disconnected recordsets). To date, i am only aware of 2 record locks on the system, and that was because two PC's were setup to use the same UserId's. I have to agree with you though, ive been pushing for us to move to SQL server but it brings various other costs and headaches, one of the biggest selling points of our system is its cost and mobility (as quite a few clients use it on the road). However, more and more, as our clients systems grow, we are recieving support queries for speed and performance. I guess i was looking for a quick fix that would save me updating the code to use SQL (for my various functions CAST, IIF and so on). However, It does need to be done and thank you for providing me with a second opinion that i can take to my boss.

Posting Permissions

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