Compacting Problem

[gaRed]

I am working on a database another programmer created. At some point the DB was converted from Access 97 to Access 2000. My boss has always felt that a few parts of the DB (mostly tables I think) did not get converted and are still in 97 format.
I am having some trouble with a compacting button that the previous user created. I think it is having trouble with the "acCmdCompactDatabase" statement. Does anyone know if this is strictly a 2000 command or if 97 recognizes it? Any additional theories?
Thanx!

 

[JPeters]

AC97 Does recognize that the acCmdCompactDatabase. It's a constant, so it can be used anytime. However, AC97 doesn't like to be compacted through code from WITHIN the database. Easiest way to do it that I've found (assuming the DB needs constant compacting) is to write out 2 command lines in a batch file you create with notepad. 1 shortcut to compact the database.. then when that one is done, a shortcut that opens the database. Open notepad, save it as YourDBNameCompact.bat. Paste something like this into it:

REM Opening Database...
@echo off
REM This compacts the database.
"C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" "C:\losscontroldbase.mdb" /compact
REM This opens the database after it is compacted.
"C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" "C:\losscontroldbase.mdb"
exit

then save it... and double click it to run it. Just an idea.. ;-)

-Josh ------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------

 

[gaRed]

That seems to be compacting the db OK, but I keep getting an error saying that the old database could not be deleted and replaced by the new, compacted copy because it is read-only. It is not set to read-only in the properties list. Any ideas?

 

[JPeters]

You get that error from the batch file? That seems a little odd, we aren't overwriting anything in this procedure. I'll think about it. Until then, anyone else out there have any idea why it would do this?

-Josh ------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------

 

[JPeters]

Read this faq that Ben Ohara wrote. It's brilliant.

How do I compact my Access 97 database from code?
faq705-1955

-Josh ------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------

 

[gaRed]

Would it be better to find which parts of the DB were not converted to Access 2000 and convert them? Supposedly, the code is already there to compact it, it's just having 97 troubles.

 

[JPeters]

Doesn't AC2k convert the entire thing? I didn't think that it left particular parts in AC97. We should get ThorMstr in this thread. He's a whiz when it comes to converting databases. He's probably got the answer in that brain of his... somewhere... I know he'll read this sometime soon anyways, he's like me. Not many threads go by us unread. ;-)

-Josh ------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------