Is this database too big? RRS feed

  • Question

  • This database has been in use for years without problems, now twice recently users have received the 'Database is in an inconsistent state' message. The only option is to click [OK] to do a repair.

    This database lists as around over 525,000KB in the folder.

    There are 2-3 simultaneous users. The database has been split and all users have their own copy of the front-end.

    Most of the size of this database is textual notes and comments. What do you think of moving this to the file system and accessing it via hyperlinks? I'd need some automated way of creating the files from the MS Access text. It could be a really big task.

    Thursday, November 1, 2018 3:59 PM

All replies

  • Hi,

    Which file is 525MB? The BE or the FE? Either way, it doesn't seem to be a big file at all. Does doing a C&R on the file get rid of the error (for a while, at least)?

    Thursday, November 1, 2018 4:11 PM
  • The BE. The last repair fixed the problem for one week. I'm in the process of creating a new container for the FE and BE.
    Thursday, November 1, 2018 4:46 PM
  • Unfortunately, in a multi-user environment, especially one over an old network, data or file corruption can be caused by almost anything. You could try asking users to keep track of the steps they were doing in detail whenever they notice any unusual behavior from using the application. I found in several cases where one user managing to create a null record messed things up. Removing this vulnerability helped stabilize the app in most cases.

    Just my 2 cents...

    Thursday, November 1, 2018 4:56 PM
  • Consider switching to SQL Server Express as backend. This would mitigate the inconsistency issue.
    Thursday, November 1, 2018 5:10 PM
  • The BE. The last repair fixed the problem for one week. I'm in the process of creating a new container for the FE and BE.

    That sounds like the best thing to do. When databases start to destabilize the cause is usually corrupt system tables. Importing everything into new, blank databases (both FE and BE) usually fixes the corruption because fresh system tables are built as objects get imported.

    I would start with the back end and don't forget to make frequent backups. If the back end is on a server confirm with your server guy that the file is being backed up nightly.

    Bill Mosca

    Friday, November 2, 2018 9:07 PM
  • What version of Access are you running?  If it is Office365, Access 2016 or Access 2019 you may be dealing with a known bug, see:

    Also, the technical limit in size is 2GB, which you are well under, that said, when a database is in use, it can grow considerably in certain instances, and can then end up over the limits, so you don't ever want to get too close to the 2GB limit in the first place.  In your case, it doesn't sound like that is the case.  The advice given by the others of exporting everything to another blank database is very sound!

    Daniel Pineault, 2010-2018 Microsoft MVP
    Professional Support:
    MS Access Tips and Code Samples:

    Friday, November 2, 2018 11:57 PM