I have just gone over the 1 GB limit for an access database file (.mdb). What do I do? I can't repair, compact for fragment my database. Any ideas so I can get access to the database and delete some entries?
I wonder if I am the only one who is impressed with the fact that you have a database this size?
Anyway that aside - can you create another .mdb and make links to your tables?
If the answer is yes, create queries that will extract the information into tables that are of a manageable size - or alternatively, write queries that will reduce the size of the tables significantly, and then import data and structure of the tables into a fresh .mdb
When you try to repair your database, is it resident on the server, or on your desktop? If its on the server, try copying down to your desktop and attempt to compact and repair then - I've seen this make a difference.
Why not just create another database, import stuff from the full one to the new one and compact away? By the time you get everything imported it should be down to size.
Thanks for the input guys: OK, So I'm trying to import the old database into a new one, but I'm getting a message saying that the .mdb file isn't recognized in the index (or something like that).
Has the database been corrupted as well? When I try to run the repair, I only get a message saying "Your file is over its maximum size."
"(the filename) isn't an index in this table. Look in the indexes collection of the TABLEDEF object to determine the valid index names"
What does this mean? I'm a lowly network guy with little or no programming/Access knowledge. I just started here and was given a full database to deal with- help!
Thanks for your response- But I can't open the original! It is telling me that the "file isn't an index in this table. Look in the indexes collection of the TABLEDEF object to determine the valid index names."
How do I open the original?
The first thing you should do when you run into a corrupt datbase is to back up the file.
Second try and compact and repair it. It may be that some user converted the database to a later version of access than everyone is using. Old versions always report newer versions as unreadable and corrupt. Once you've determined what version the database is (and disposed of any bodies where appropriate j/k), using the appropriate version try compacting again. You can stop with this laundry list as soon as something fixed it... Make sure you are not importing hidden and system tables by making sure they are hidden (Tools Menu, Options, View Tab uncheck Hidden objects and system objects). Then try to import from a new file. If that fails try to open the file. If you can, Export tables one at a time to a new database (this is painfully slow but your grasping at the last few straws here). I'm not sure how many versions it goes through but there is an MS utility that called JetComp that allows you to compact database files. It is supposed to be able to fix some cases of database corruption that MSAccess can't. Last version I saw (I'm ramping up to move to 2000 from 97) was available in the Jet 3.5 update (I think it was SP4) for Access 97.
If that doesn't work, restore the file from backup or seek the help of professional recovery company. I've been lucky enough never to have to use one, so I can't give any strong recommendations...
to sign up for all kinds of Woody's magazines on MS products.) The magazines are definitely worth reading as it tells you about the problems and updates on the products. Definitely worth having it as a fixer of things that you don't use.
One more thing that you must know: If so much as one user is in the database when your backup runs, even if the backup will use a share file lock you may retore a corrupt database. This database may be repairable. You'll know you backed up a database in use if it backs up the ".ldb" file the same name as the ".mdb". And just to make things interesting, users disconnected by the network instead of closing the file normally can cause db correction. So be sure that the users know this.
One more thought...
You might be able to import all but one messed up object. So if it stops (possibly on the first object), skip that when you try to import.
The error you're receiving indicates that there's something wrong with one of the system tables in Access. This probably came about from either a missing or partial entry in one of the tables that Access maintains on your database, probably as a result of going over the size limit. There could have been a table update in progress when the oversize limit occurred leaving the table in a corrupt state. These can be difficult to repair without loss of data.
Have you tried to open the database is code and import tables from it? Have you tried to link to the database? Can you copy the database and then compact it with WinZip so it can be sent to someone with more knowledge in the field? Terry Broadbent comes immediately to mind.
For those in this thread that have been wondering, it's not that hard to make a database that exceeds 1GB. All you need is an OLE field that you put .jpg images in. For some reason, those picture files take up a LOT of room in Access! (A 200kb picture makes the .mdb file size increase by about 2MB!):-0
And yes, I know this from experience. When I hit the 1GB limit, my database wouldn't open from that point forward, either. I wasn't too far into the development, though, so I ended up recreating the whole thing from scratch. Sorry, but I don't know any other way to do it. My best suggestion would be to try creating a new database, then importing maybe half your tables from the oversized one.
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.