Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Sep 2002
    Location
    Moline, Illinois, USA
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DB Too Large? (Access 2002)

    How large is too large? We recently had our database "crash". It became corrupt and we were unable to repair. Had to restore from back up. Advice we received was to reduce size of database.

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Mishawaka, Indiana, USA
    Posts
    145
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DB Too Large? (Access 2002)

    How large is your database in MB? How many records do you have?

    In Access 97 searching in help, "Specifications, Microsoft Access", yields all the information about limits.

    If you have ole objects imbedded your database will get very large quickly. I found it best to hyperlink to objects.

    If you have memo fields, the database is more prone to corruption.

  3. #3
    New Lounger
    Join Date
    Sep 2002
    Location
    Moline, Illinois, USA
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DB Too Large? (Access 2002)

    My database is 222 MB. Our largest table has approx. 16,000 records - but there are 180 fields in the table. I have OLE objects in my reports. I have fields where we type notes, but I don't think they are true "memo fields". What do you think? Thanks for all your help. I appreciate your time!

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: DB Too Large? (Access 2002)

    That's on the large size for Access97. 180 fields in one table is probably not good from a design view - it usually means you have really normalized the table design. Is your database split into a front-end and a back-end. If not, you should definitely consider it. In addition, you could look at putting some tables in one back-end and some in another in order to reduce the size of individual databases. How many users does the database have?
    Wendell

  5. #5
    New Lounger
    Join Date
    Sep 2002
    Location
    Moline, Illinois, USA
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DB Too Large? (Access 2002)

    We have 20 users. And we don't have any groups or restrictions. We are using Windows XP and Access 2002.

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: DB Too Large? (Access 2002)

    Ah - I saw Access 97 in one of the replies and made a wrong assumption. Access 2002 databases can grow to 2GB so you aren't in too much trouble at 220MB, but the table design is dicey, as the maximum number of fields in a table is 255. Twenty users can stress a database if they are active, but we have one client that grew to about 110 users before they finally switched to SQL Server as the backend. But they were only doing an occasional edit or lookup. In any event, I would look at splitting the database and moving the front-end to the user's workstation. For one thing, if you want to make any design changes in forms or reports, you will have to take exclusive control, which means kicking everyone out.
    Wendell

  7. #7
    New Lounger
    Join Date
    Sep 2002
    Location
    Moline, Illinois, USA
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DB Too Large? (Access 2002)

    Oh, you are so right!! We are routinely kicking everyone out of Access to make changes to reports and create new ones. We realize our large table is improperly designed, but hesitate to take the time to fix it right now, since we are unsure if Access is our best solution.

    Thanks so much for your input. It has been really helpful and reassuring!

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: DB Too Large? (Access 2002)

    Is you database fairly new - or did it evolve from Access 97 or 2000? If it's fairly new, someone will want to change your large table in the near future, and it will get worse. Even if Access isn't the right solution (which isn't often the case), if you convert to another database, the structure issues should be dealt with now. Otherwise it may get so top-heavy that it never survives, and it would not be happiness if you lost all your data. It would help to understand what kind of an application this is. There are lots of folks in this forum who have had extensive experience with the issues you are facing, and would be happy to share their experiences and knowledge with you.
    Wendell

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

    Re: DB Too Large? (Access 2002)

    Like Wendell, I would urge you to redesign your table, regardless of whether Access is the best solution for you. For any solution to perform well, you need a decent table design. One of the reasons that Access gets a bad rap with IT departments is that they so often encounter poorly designed Access databases and don't realize that there is any other kind. I've had much larger databases than that even in Access97 without encountering serious problems. However, shoving a bunch of objects into a database isn't usually the best way to design it, and with a table with a large number of fields, you'll run into query limitations as well.
    Charlotte

Posting Permissions

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