reduce Access 2007 database size

[Roxana]

Hello,
I am taking over an old application in Access 2003 which has reached 2GB in
size.
When the user tried to compress the database, he got an error message.
I converted the database to Access 2007 and now the size shows 2,096,160KB.
I archived some of the data and reduced one table from over 900,000 recs. to
200,000 and I deleted some 30 back-up tables. I run the compact and repair
but with no results. The database size remains the same. After the compact
and repair is being run, a new database is being created: “database.mdbâ€.
What I find strange is that it creates an “mdb†file when I am working with
“accdb†files. Can anyone help me with some suggestions on how to handle this?
Thank you!

 

[Shaun Slaney]

Did you ever get your access sorted out!!!!

 

[Jeff Boyce]

Roxana

One approach might be to create a new (empty) .accdb file and import pieces
from the over-sized db.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Roxana]

This is a complex application with many tables, queries, forms and modules .
I guess I should try to determine if there are any tables/queries which are
not being used in the process and delete them...maybe they were creted ad-hoc
and left in the database .
I am now trying to archive data and delete the backup tables in Access 2003,
without converting to 2007.

 

[Jeff Boyce]

Roxana

My suggestion would likely NOT have resulted in a new db the same size, as
in the import process, Access would probably not import the empty spaces.

And you could import a portion of the db, then run Compact & Repair, then
import more, and so on...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jerry Whittle]

#1 I agree with Jeff's solution of importing into a new database file.

#2. The C&R probably didn't work as evidenced by the extra database.mdb
file. Why it's an mdb vs accdb, I haven't a clue.

Try searching for the proper JetComp utility. It does a better job of fixing
problems and gives you some extra control when doing the compacting such as
requiring that you create a backup first.

 

[Roxana]

Thank you all so much. Great help!

#1 I agree with Jeff's solution of importing into a new database file.

#2. The C&R probably didn't work as evidenced by the extra database.mdb
file. Why it's an mdb vs accdb, I haven't a clue.

Try searching for the proper JetComp utility. It does a better job of fixing
problems and gives you some extra control when doing the compacting such as
requiring that you create a backup first.