Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Nov 2001
    Location
    Burbank, Illinois, USA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Breaking Out an Access Database (2000)

    We presently have a main database installed on one central server which four schools access. We would like to break up the database so each schools information would reside on the server located in their school to speed up the database. But, we would also like to have these four schools tables, automatically update the main database table which would be located on a different server. I am thinking an update query might work, but is there an easy way to accomplish this?

  2. #2
    mcwilsong
    Guest

    Re: Breaking Out an Access Database (2000)

    Did you think about breaking the database into 2 files? One, that would house strictly the tables and one, that would hold all the queries, forms, reports, & modules. That way, you could house the tables files in one location for each school, but each school would have it's own copy of the front end that you could then change at will and not worry about affecting the data. Once you split the database, the tables would be attached (or linked) to the front end file. You can then set up, through a module, a link refresh command upon opening the database. So any changes you make to the tables will be appropriately refreshed upon opening your program. Just a suggestion.

  3. #3
    Star Lounger
    Join Date
    Nov 2001
    Location
    Burbank, Illinois, USA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Breaking Out an Access Database (2000)

    Thank you for the suggestion. I just want to make sure I understand what you are saying. I would split the database so one file would have the main tables and the other file would house the schools seperate table, all queries and reports. I would then link the schools seperate tables so they would automatically update the main table on startup?

  4. #4
    mcwilsong
    Guest

    Re: Breaking Out an Access Database (2000)

    Yes, that's it. Although, are you saying you have two types of tables? One set that is global to all schools and another set that is specific to each school? If so, this type of breakup would still work. However, you would have a higher degree of maintainence if/when there are table modifications. But at least it would cut down on the stress of the program itself. I don't have any existing code that handles the refresh part, but I'm sure if you check the Microsoft site, you'll be able to find developer solution databases with examples.

  5. #5
    Star Lounger
    Join Date
    Nov 2001
    Location
    Burbank, Illinois, USA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Breaking Out an Access Database (2000)

    Right now we have only one database. In that database is a main table that stores all student information for all four schools. That database also includes queries, forms and reports that are specific to each school. Having the database located on one server and each school accessing it thru the network is extremely slow. What I would like to do is split the database so each school would have a main table that would hold only the students in that schools info along with only the queries, forms and reports for that school. But, I would like the individual schools table (information) to update the main table which would be located on a different server. I would like to do this for all four of our schools. Do you think this is possible? Or would it be alot of work and headaches?

  6. #6
    mcwilsong
    Guest

    Re: Breaking Out an Access Database (2000)

    Let me make sure I understand your setup. Would this be an acurate protrayal of your existing database:

    Main Database:
    Tables: SchoolA, SchoolB, SchoolC, SchoolD
    Queries: SchoolA, SchoolB, SchoolC, SchoolD
    Forms: SchoolA, SchoolB, SchoolC, SchoolD
    Reports: SchoolA, SchoolB, SchoolC, SchoolD

    School A accesses: All tables, queries, forms, & reports labeled SchoolA.
    School B accesses: All tables, queries, forms, & reports labeled SchoolB, etc.

    You would like:
    School A Database: Tables, Queries, Forms, Reports
    School B Database: Tables, Queries, Forms, Reports
    School C Database: Tables, Queries, Forms, Reports
    School D Database: Tables, Queries, Forms, Reports
    Main Database: Links all objects from each School's database

    You would need to answer some questions for yourself:
    1. Would the main database be the one you would access?
    2. If so, would any modifications be made by you or someone local to the school?
    3. If you are to made all modifications, it could become a headache. You can't modify linked tables, you must go to the original source to make table changes.
    4. If someone local is to make modifications, then it could work. If you're also modifying queries, forms, & reports, you'll have to do this for each location, as each school will have a unique set of non-table objects.

    Let me ask another clarifying question:
    Is it feasible to migrate your program over to SQL Server 2000 to make use of its multiple users, with multiple data needs?

    Also, you may want to consider:
    A. Are the queries, forms, & reports so unique you can't use the same set for each school (i.e., modify the fields, button, etc. based on the user/school opening the form)?
    B. Is your program protected by passwords and user logins? If so, have you looked into using one set of forms that filters the data to the user/school logging into the database (kind of along the lines of Q:A)?

    I know that's probably more options than you wanted, but without actually seeing the database, I can only make guesses and assumptions. Sorry for being so long winded! (@

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

    Re: Breaking Out an Access Database (2000)

    Start thinking replication because update queries would be a nightmare to manage.

    You first need a split front-end/back-end architecture. The front ends should reside on the individual schools' servers if you can't put copies on the specific machines. Those copies should be identical to the database that contains the forms, queries, modules, reports, macros, data access pages on your central server. The front end should not be replicated.

    The back end, which contains the tables and relationships. should stay on your central server. However, if you turn a copy on the server into a design master, then create another copy in a different location on the server to act as the sync master, you can create a separate replica to run on each of the schools' local servers. They would enter and edit data and the changes would be passed back to the central server when the replicas were synchronized to their sync master.

    You would need to manage the replication centrally and make sure it happened at desired intervals, but the replicas would sync back to the sync master and that would sync to the design master. You need at least 3 levels in replication because sooner or later your design master or your sync master is going to go up in smoke and you need the other one to recover.
    Charlotte

  8. #8
    Star Lounger
    Join Date
    Nov 2001
    Location
    Burbank, Illinois, USA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Breaking Out an Access Database (2000)

    Thanks for the help. I am going to talk to our Network Administrator this morning and I am quite sure I will be getting back to you for more help.

  9. #9
    Star Lounger
    Join Date
    Nov 2001
    Location
    Burbank, Illinois, USA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Breaking Out an Access Database (2000)

    Thanks for the input! I am going to speak with our Network Administrator this morning regarding your post and Mcwilsong's post. I know I will be getting back to you soon!

Posting Permissions

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