Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
OK- I'm Over the Database Limit- What Do I do? 1
- Thread starter shaine
- Start date
mailzero28
Programmer
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.
Good luck anyway
bye
Martin.
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.
Good luck anyway
bye
Martin.
shaine
Can you tell us what happenned? Presumably you were able to add data over the 1Gig so will it let you do queries?
I'm sure lots of people want to know how it got that big. Has it been efficient at that size? mike.stephens@bnpparibas.com
Can you tell us what happenned? Presumably you were able to add data over the 1Gig so will it let you do queries?
I'm sure lots of people want to know how it got that big. Has it been efficient at that size? mike.stephens@bnpparibas.com
- Thread starter
- #5
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."
What next?
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."
What next?
- Thread starter
- #6
Correction- The exact error now is:
"(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!
"(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!
Francescina
Programmer
If you want to stick to A97 (considering that Access2000 allows 2GB MDB files) then I suggest the following technique:
1. copy the MDB file
2. Open the original
3. Delete 50% of the tables
4. Close and compact this file.
5. Open the copy
6. Delete the other 50% of the tables
7. Close and compact this file
8. Create a new MDB
9. Link all tables from the original and all tables from the copy.
Now you have 1 "frontend" that can manage up to 2 GB data easily.
Nethertheless, I wonder what kind of data you are processing to reach this file size.
1. copy the MDB file
2. Open the original
3. Delete 50% of the tables
4. Close and compact this file.
5. Open the copy
6. Delete the other 50% of the tables
7. Close and compact this file
8. Create a new MDB
9. Link all tables from the original and all tables from the copy.
Now you have 1 "frontend" that can manage up to 2 GB data easily.
Nethertheless, I wonder what kind of data you are processing to reach this file size.
- Thread starter
- #8
Francescina:
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?
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...
I've seen advertisements for in a good e-mail magazine I read (goto 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.
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...
I've seen advertisements for in a good e-mail magazine I read (goto 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.
UncleJack
Programmer
- Sep 20, 2001
- 344
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.
Uncle Jack
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.
Uncle Jack
Looking over this thread I realized I made a serious mistake...
"And just to make things interesting, users disconnected by the network instead of closing the file normally can cause db correction."
Should read
"And just to make things interesting, users disconnected by the network instead of closing the file normally can cause db corruption."
Correction/corruption: Usually mistakes aren't worth mentioning but that one changed the meaning.
"And just to make things interesting, users disconnected by the network instead of closing the file normally can cause db correction."
Should read
"And just to make things interesting, users disconnected by the network instead of closing the file normally can cause db corruption."
Correction/corruption: Usually mistakes aren't worth mentioning but that one changed the meaning.
-
1
- #13
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.
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.
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question