Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2003
    Location
    Boston, Mass. USA
    Posts
    81

    Unanswered: multiple backends?

    I am responsible for several databases that are highly transactional. Once is used by only 3-4 users, the other by 20 or so.

    Right now the back end is in a separate MDB file. All tables are in this one back end (excepting one or two samll ones in a separate mdb due to network folder permissions).

    I am thinking about putting each table in a separate MDB file. I am mainly considering this for the one with only 3-4 users ( later perhaps the other?). For the 3 user database, this would be about 5 separate backends with each one dedicated to only one (large) table. This database tends to be slow because archiving has only been adhoc (usually when desparation sets in). Also it is hard to archive since users often come wanting data from way back when. I thought perhaps this approach might help performance while not having to archive data as quickly (for those "way back when" requests)

    I'd appreaciate any suggestions about this, for or against it, or suggesting other methods.

    Thanks,
    Mark

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    What you could do (this is what I'll be doing ...) is create reports that are specifically targeted to an archive ... Then you can slim down the current data weeding out all the old into the archive ...

  3. #3
    Join Date
    Oct 2003
    Location
    Boston, Mass. USA
    Posts
    81
    Yes, special reports could be created (or revised) to point to the archive. For my appilcation this won't really work, since often the requests involve combining yesterday's data with that from 11 months ago - so I have to move the archived data back into the original table before running it, so all the data is in one place. (The requests are so infrequent, and unpredictable enough that building a special report with a query that merges the two tables is not practical---what fields they want included often vary).

    Is there any reason why using multiple back ends, such as one per transaction table is not a good idea?

    Mark

  4. #4
    Join Date
    Nov 2003
    Posts
    150
    just a though, why not use something much faster than Access as a back-end? MySQL comes to mind...
    Cheers!

    Mr.Crud

    There are 10 kinds of people, ones that understand digital, others that dont

  5. #5
    Join Date
    Oct 2003
    Location
    Boston, Mass. USA
    Posts
    81
    yes, thank you for the suggestion! SQL would be a definite improvement. Unfortunately convincing the powers that be of the value of investing resources in making such a change is quite an uphill battle.

  6. #6
    Join Date
    Nov 2003
    Posts
    150
    the fact that MySQL is free couldnt hurt!!!
    Cheers!

    Mr.Crud

    There are 10 kinds of people, ones that understand digital, others that dont

  7. #7
    Join Date
    Oct 2003
    Location
    Boston, Mass. USA
    Posts
    81

    Cool

    well, yes but even if no courses or books for staff are paid for (ie staff teach themselves the new skills) there is still staff time taken from other projects or client deadlines :-) You've just got to love bureaucracy, lol

  8. #8
    Join Date
    Nov 2003
    Posts
    150
    nothing more to say...

    Cheers, and good luck!
    Cheers!

    Mr.Crud

    There are 10 kinds of people, ones that understand digital, others that dont

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    staff teach themselves
    what else are the weekends for?

    izy
    currently using SS 2008R2

Posting Permissions

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