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 gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
MDB File too large
- Thread starter Ray1127
- Start date
You have pretty much exceeded the 2gb limit, so you may have a hard time compacting since there is no room to work with. Try jetcomp.exe
This may buy you some time to figure out how to archive or split the db.
This may buy you some time to figure out how to archive or split the db.
Make sure to make a backup before trying to compact it thou, just in case.
Also you could take an older backup (assuming you have one) compact that, then delete all of the records from all of the table, compact again, then copy the good records from the current large table into the empty one.
Can we assume that there are significant deleted records in the MDB, or what else is there that makes you think that compacting it will reduce the size? Or is this access database really that full of good information, e.g. have you push access to it limits?
Lion Crest Software Services
Anthony L. Testi
President
Also you could take an older backup (assuming you have one) compact that, then delete all of the records from all of the table, compact again, then copy the good records from the current large table into the empty one.
Can we assume that there are significant deleted records in the MDB, or what else is there that makes you think that compacting it will reduce the size? Or is this access database really that full of good information, e.g. have you push access to it limits?
Lion Crest Software Services
Anthony L. Testi
President
Hi Ray,
You have pretty much reached the MS Access 2 gig limit.
If compacting fails, you might try Creating a new, blank MDB and import everything from your DB into the fresh blank db.
Is your Access App split into a frontend for programs, forms, code, reports and queries and a Backend of just Data Tables?
If you have already split the app into a Frontend and a Backend, then which is giving you the problem?
You can always create several different frontends linked to the one backend.
If the backend has grown, you can split the backend into 2 or more backends or move the backend to MS SQL
Just some ideas, Hope they help.
Hap...
Access Developer![[pc]](/data/assets/smilies/pc.gif "[pc] [pc]")
Access based Accounting Solutions - with free source code
Access Consultants forum
You have pretty much reached the MS Access 2 gig limit.
If compacting fails, you might try Creating a new, blank MDB and import everything from your DB into the fresh blank db.
Is your Access App split into a frontend for programs, forms, code, reports and queries and a Backend of just Data Tables?
If you have already split the app into a Frontend and a Backend, then which is giving you the problem?
You can always create several different frontends linked to the one backend.
If the backend has grown, you can split the backend into 2 or more backends or move the backend to MS SQL
Just some ideas, Hope they help.
Hap...
Access Developer
Access based Accounting Solutions - with free source codeAccess Consultants forum
- Thread starter
- #5
first no the Database is not split. It was functioning fine until this month. It was at about 200 MB the procedure calls for a staging table that is recreated each month for a Report that is a rolling 13 months. So if the report is run in Jan 2011 the table is overwritten with data from Jan 2010 through Jan 2011. The required data is membership so if the person is a member on the first of the month they are a member for the entire month.
What I think happened is in December we had a special request that the report be run for each year from 2000 through 2010 and the data for the entire 10 years was run. The reports for each year ran successfully but the DB grew to 2 GB. I deleted the table that was created and tried the compact and repair but it errored and the size still shows as 2 GB. I'm going to try and split the DB and see what happens. I'll let you know
What I think happened is in December we had a special request that the report be run for each year from 2000 through 2010 and the data for the entire 10 years was run. The reports for each year ran successfully but the DB grew to 2 GB. I deleted the table that was created and tried the compact and repair but it errored and the size still shows as 2 GB. I'm going to try and split the DB and see what happens. I'll let you know
- Thread starter
- #6
To rehash based on everyones tips.
1)if you have not copied it yet do so, quick.
2)Create a 3 brand new databases. They will contain the following
Front end: all forms, reports, queries, macros, modules
Back end 1: all data tables
Back end 2: the "staging table" structure only no data. This is likely corrupt so I would make a new one if I cannot copy the structure.
3)Import your data tables into backend 1 (not the generated one)
4)compact and repair
5)Import all other objects into the front end: forms, reports, queries, macros, modules
6)compact and repair again
7)copy and paste if possible the staging table "structure only" or create from scratch a new staging table. This will go in back end 2
8)Link to both back ends. Just like linking to a single back end just not done as often
This way if the staging table gets corrupted in the future, it will not impact your front end or data back end.
1)if you have not copied it yet do so, quick.
2)Create a 3 brand new databases. They will contain the following
Front end: all forms, reports, queries, macros, modules
Back end 1: all data tables
Back end 2: the "staging table" structure only no data. This is likely corrupt so I would make a new one if I cannot copy the structure.
3)Import your data tables into backend 1 (not the generated one)
4)compact and repair
5)Import all other objects into the front end: forms, reports, queries, macros, modules
6)compact and repair again
7)copy and paste if possible the staging table "structure only" or create from scratch a new staging table. This will go in back end 2
8)Link to both back ends. Just like linking to a single back end just not done as often
This way if the staging table gets corrupted in the future, it will not impact your front end or data back end.
One of the 'Tricks' I have been doing is having a seperate Master Report MDB that holds the just structure of the report data in it. At program start up I make a copy of that master and then all reports are run via that daily copy. In a way it is a hand compacting of report information. Does not help for your immediate issue but something to think about in the future.
Lion Crest Software Services
Anthony L. Testi
President
Lion Crest Software Services
Anthony L. Testi
President
Hi Ray,
I would split the Database, but to get you working right now, try this.
1) Create a brand new blank Database
2) Open up the 'Import' option and select all of the DB elements from your old DB.
3) If the Import works, then you should have a new, good copy.
If the import fails, then look at where the import stopped, an identify the object that failed to import.
Now, repeat the 3 steps above, select all objects except the one that failed and see if that works
Hap...
Access Developer Access based Accounting Solutions - with free source code
Access Consultants forum
I would split the Database, but to get you working right now, try this.
1) Create a brand new blank Database
2) Open up the 'Import' option and select all of the DB elements from your old DB.
3) If the Import works, then you should have a new, good copy.
If the import fails, then look at where the import stopped, an identify the object that failed to import.
Now, repeat the 3 steps above, select all objects except the one that failed and see if that works
Hap...
Access Developer
Access based Accounting Solutions - with free source codeAccess Consultants forum
- Thread starter
- #10
Already done. I created a new DB and imported all exept the temp table I thought was causing the issue. Worked perfectly. ReCreated the temp table with the correct amount of data the MDB is back to the 200 MB it was originally. thank you everyone for your suggestions. I have now made it a policy that the MDB File be compacted on the last day of every month so that when the next temp table is created on the 5th of the month it won't repeat the same error. Fortunately the process that caused the problem in the first place was a 1 time only process and won't be repeated.
Hi Ray,
That is good news. I am glad you were able to get everything working.
Now would be the time to split the application into a FrontEnd and a Backend.
This is just good programming for an Access Application
Hap...
Access Developer Access based Accounting Solutions - with free source code
Access Consultants forum
That is good news. I am glad you were able to get everything working.
Now would be the time to split the application into a FrontEnd and a Backend.
This is just good programming for an Access Application
Hap...
Access Developer
Access based Accounting Solutions - with free source codeAccess Consultants forum
- Thread starter
- #12
Honestly I wish I could. I've tried pointing out the advantages but with rare exception I'm not permitted to do that. Very frustrating to work here. Anybody knows of a database developer (Report Writer) position in Southern Cal be sure to let me know.
Sorry for venting but not a good day.
Sorry for venting but not a good day.
- Thread starter
- #14
I know. 80% of the MDB's I've put into production here have been trashed at one time or another. I've pointed out why they were trashed and was told I should've accounted for that possibility. I pointed out that I did and the recommendations I made. Was told that those recommendations were unnecessary as this is the way they've been doing these for years and not the cause of the crash my faulty programming was the cause. I've come to the conclusion that I need to get out of here ASAP. I'm still trying to figure out why if I'm so bad do I still have a job.
I need to point out that here I am not in IT and the people I work with are not IT people. They are essentially users. I need to get back to an IT job.
I need to point out that here I am not in IT and the people I work with are not IT people. They are essentially users. I need to get back to an IT job.
Hi Ray,
Good luck,
Hap...
Access Developer Access based Accounting Solutions - with free source code
Access Consultants forum
Good luck,
Hap...
Access Developer
Access based Accounting Solutions - with free source codeAccess Consultants forum
- Status