wfairbanks
MIS
How do I compress a large db?
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 Shaun E on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Compressing a database 3
- Thread starter wfairbanks
- Start date
- Status
-
1
- #3
Oddly enuf!
I just checked the following on a developer's site
By Dan Haught, Executive Vice President, FMS
ARTICLE INFO
MICROSOFT ACCESS ADVISOR ZONE
Doc # 12415
15 May 2003
Length 1 page
Over time, the part of your Access database that stores VBA module code can become bloated and, in some cases, can cause strange or otherwise unexplained behavior. As you compile and run code, VBA leaves behind old states of code that are no longer valid. Often, the Access Repair and Compact operation doesn't free up this space. To solve these problems, use the undocumented Decompile command line switch for Access. This switch is supported in Access 97, 2000, and 2002 and tells Access to discard all compiled code. This leaves you with just VBA source code, ensuring nothing is left behind to bloat your database.
Rollie E
I just checked the following on a developer's site
By Dan Haught, Executive Vice President, FMS
ARTICLE INFO
MICROSOFT ACCESS ADVISOR ZONE
Doc # 12415
15 May 2003
Length 1 page
Over time, the part of your Access database that stores VBA module code can become bloated and, in some cases, can cause strange or otherwise unexplained behavior. As you compile and run code, VBA leaves behind old states of code that are no longer valid. Often, the Access Repair and Compact operation doesn't free up this space. To solve these problems, use the undocumented Decompile command line switch for Access. This switch is supported in Access 97, 2000, and 2002 and tells Access to discard all compiled code. This leaves you with just VBA source code, ensuring nothing is left behind to bloat your database.
Rollie E
Re Decompile switch.
Yes the article is correct in that the decompile switch removes unused code modules, strips out the p-code compiled version of the code, and fixes a boat load of bizarre behavior of your databases besides, but it does not reduce the size of the database. After a decompile you STILL have to compact to recover the space freed by the decompile.
Lambert
Yes the article is correct in that the decompile switch removes unused code modules, strips out the p-code compiled version of the code, and fixes a boat load of bizarre behavior of your databases besides, but it does not reduce the size of the database. After a decompile you STILL have to compact to recover the space freed by the decompile.
Lambert
-
2
- #8
psemianonymous
Programmer
Decompile is very useful, but a lot of the size loss is artificial if you don't recompile the modules after decompiling. For most versions of access, CTRL-G, Debug menu->Compile And Save All Modules.
So the order goes to Decompile:
1. Back up your app
2. Start - Run
3. msaccess.exe /decompile "C:\Path\FileName.mdb"
4. Go into your database;
5. CTRL-G to bring up the Immediate Window,
6. Debug menu->Compile And Save All Modules, OR
type in: RunCommand acCmdCompileAndSaveAllModules
THEN compact the database. That's a better shrinkage estimate. And yes, it does shrink your database the first time you do it (or it has for me).
I love to link to Tony Toews' site, because it is incredibly detailed&informative:
So the order goes to Decompile:
1. Back up your app
2. Start - Run
3. msaccess.exe /decompile "C:\Path\FileName.mdb"
4. Go into your database;
5. CTRL-G to bring up the Immediate Window,
6. Debug menu->Compile And Save All Modules, OR
type in: RunCommand acCmdCompileAndSaveAllModules
THEN compact the database. That's a better shrinkage estimate. And yes, it does shrink your database the first time you do it (or it has for me).
I love to link to Tony Toews' site, because it is incredibly detailed&informative:
foolio12 has it right.
How's this for some decompile results.
Starting with an MDB at 6,290 Kb. After decompiling it went to 6,422 kb - an increase of 132 Kb. Then I compiled all the modules and its size remained at 6,422 Kb. Finally, after compacting the size went down to 3,926 Kb.
Like I said, decompiling is not how you compact a database (though occasionally it may reduce the file size). The only way to actually free up wasted space is to use the Compact routine.
Oh, and did I mention that decomplie is undocumented and so unsupported, so you'd better make sure you always make a backup copy before you decompile. It can and sometimes does mess things up.
How's this for some decompile results.
Starting with an MDB at 6,290 Kb. After decompiling it went to 6,422 kb - an increase of 132 Kb. Then I compiled all the modules and its size remained at 6,422 Kb. Finally, after compacting the size went down to 3,926 Kb.
Like I said, decompiling is not how you compact a database (though occasionally it may reduce the file size). The only way to actually free up wasted space is to use the Compact routine.
Oh, and did I mention that decomplie is undocumented and so unsupported, so you'd better make sure you always make a backup copy before you decompile. It can and sometimes does mess things up.
- Status
Similar threads
