Results 1 to 7 of 7
  1. #1
    seen is offline Novice
    Windows XP Access 2007
    Join Date
    Jul 2009
    Posts
    12

    number of tables vs mdbs

    Morninng gurus,

    I have a general question concerning tables. It may sound silly, but I'd like to have it done right the first time.

    At the moment I have multiple front end mdbs which users utilize for daily activities. My thoughts are that I'd like to centralize all of them so that the data is stored in 1 external mdb. That way, any cross referencing will be in sync.

    My question is this:

    Is it bad practice to localize ALL data tables into one external mdb? If so, at what point does it become bad practice or is it just bad design all together? The plan is to have it auto-backup daily so that corruption, if any, would only be limited to that time frame. I'd like to avoid corruption all together though, so if having everyone connect to the same mdb is bad, I want to know about that also. Alternatively, should I centralize only the tables that are currently being shared (ie. contact info, which wouldn't really be sensitive to 1 day of corruption) and keep everything else separate?



    I appreciate any thoughts.

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    As long as you are not contending with the size limit then I would have only one BackEnd in a central location with everyone having their own FrontEnd on their own machine. This is the accepted method for a miltiuser db. Here's one of the best articles I've found on the topic.

  3. #3
    seen is offline Novice
    Windows XP Access 2007
    Join Date
    Jul 2009
    Posts
    12
    That was a very interesting read, thanks.

    I have a follow-up question about the FE part though. Other than the user preferences thing, why is it good practice for each user to have their own copy on their own machine? I understand that if network connectivity is lost, users risk corrupting the DB, but won't that still be the case even if they have their own copies? Or did the article mean to say that they risk corrupting the FE?

    I've been having users connect to the front end over the network for a while now without any problems, but if it's really that bad I may consider changing that.

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    The FrontEnd needs to load on the local machine. If you keep the separate FrontEnds on a network machine then it just ties up the network every time you load it. It is not executing on the remote machine. If you are having everyone use the same FrontEnd then you are asking for corruption of the FrontEnd.

  5. #5
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    If your concern is updating the FrontEnds when there is a change there are several free tools that can do that for you automatically. Here's two:
    http://www.granite.ab.ca/access/autofe.htm
    http://www.btabdevelopment.com/main/...8/Default.aspx

  6. #6
    seen is offline Novice
    Windows XP Access 2007
    Join Date
    Jul 2009
    Posts
    12
    Quote Originally Posted by RuralGuy View Post
    If your concern is updating the FrontEnds when there is a change there are several free tools that can do that for you automatically. Here's two:
    http://www.granite.ab.ca/access/autofe.htm
    http://www.btabdevelopment.com/main/...8/Default.aspx
    Cool concept. Come to think of it, since the FE won't be shared I guess I can now have the users compact and repair their own mde's on close.

    Thanks!

  7. #7
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Glad I could help. Are we ready for the Solved thread tool yet?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Linking large number of forms and tables
    By jlcaviglia-harris in forum Forms
    Replies: 2
    Last Post: 04-17-2009, 09:19 AM
  2. Replies: 1
    Last Post: 03-12-2009, 09:55 AM
  3. Sum Of Positive Number and Negative Number
    By maysamab in forum Reports
    Replies: 1
    Last Post: 10-20-2008, 04:06 PM
  4. Joining tables two tables and another table that is not
    By DevintheDude in forum Database Design
    Replies: 0
    Last Post: 09-12-2007, 08:56 AM
  5. Consolidate MDBs
    By RHall in forum Database Design
    Replies: 4
    Last Post: 01-06-2006, 12:31 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums