Cannot get 2k DB to compact 1

[matth]

I have an Access2000 database that we update every night. At the end of the update, the script runs a compact on the database. Sometimes the script halts due to the data, which causes the database to grow (seemingly exponentially). Currently, the database sits at over 2 Gigs. When I run compact database, I receive an "illegal argument" message, it continues for 40 min., then looks like it finishes. However the size remains, and we are not able to run a compact with the desired results.

Any ideas?

Matthew

 

[gwinn7]

You may have reached the database size limitation. Check the FAQ on this site. There are several related references which discuss database size limitations in Access.

Unfortunately, I was not initially able to located any Microsoft KB articles directly on this subject. Weird! Anyhow, I hope that points you in the right direction.

Gary
gwinn7

 

[matth]

I have hit this size in the past and been able to compact (but thinking back, it may have been dumb luck). I am just looking for anyone who has a rock solid answer on this one.

Thanks Gary,

Still looking.

Matthew

 

[ghubbell]

Your rock solid answer is this: Do you have a spare 2Gb+ on the drive where you are doing your compacting? Best as I understand the Db is rebuilt afresh, and the old one destroyed, hence the requirement for 2 x Db size. You receive absolutely no warning of a space requirement either. Oh, and don't try to do this over a network if at all possible. Gord
ghubbell@total.net

 

[gwinn7]

Oh yeah, btw, what he (Gord) said!


Gary
gwinn7

 

[matth]

Currently, I have 15 Gigs of free space on the drive. It shouldn't have a problem.

Does anyone know what the invalid argument message means?

Matthew

 

[ninfan]

You might want to try importing all the objects into a new MDB then try to compact it. If it works, replace the prior version with the new version. I have had cases where an MDB was corrupted. This worked for me.

HTH

 

[ghubbell]

Matthew, try reloading Access and do a repair. Perhaps this is just one of those "gremlins" that has snuck in to cause you grief. Gord
ghubbell@total.net

 

[skfoster]

This case happened many times for me. Because the size of your MDB close to 2GB, there is no space to swap. Do the following steps:
1) from my computer, copy this MDB (A) to new MDB(B)
2) delete all tables in B
3) import one bigest table from A to B
4) compact B
5) check the size of B, repeat 3) and 4) if necesary, or import the rest of all tables.

 

[matth]

Issue Resolved:

First, followed Gord's advice and repaired Access itself, unfortunately it didn't help.

Next, I pulled Jetcomp.exe from Microsoft's support page. It is able to compact the database without opening it. I never had a problem opening the database, but it was worth a shot. It compacted with no problems. I also made sure that I had the "latest and greatest" service packs applied.

Figured I should post incase someone else searches for this resolution later.

Thanks to Gary, Gord, Ninfan, and Sherman for the input.

Matthew