What now that Access97 DB reaches maximum size? 5

[KHerm]

We just had a process start failing that has been a reliable workhorse for a long time. The fail error didn't point us to the size problem, but started getting the 'Access has reached it's maximum size' while trouble shooting. The database was 1 gig in size. We ran Database Repair and Database Compact and have started again. The database went from a a gig to about a third of a gig.

Do we need to repair and compact often? Most of the tables are linked through ODBC to a SQLServer database. Do these linked tables contribute to the size?

Also, isn't the size limit supposed to be 2 gig?

We would sure like to know what the folk here know about this.

Ken

 

[DaveShmave]

Ken,

Access 97 has a size limit of 1 gig. Access 2000 has a size limit of 2 gig.

I would make routine (daily or weekly) compact and repairs of the Access database in your case.

HTH

Dave s-)

 

[Tim1]

Hi Ken,

Firstly, the maximum size of Access 97 is 1GB.

On the compact database, for some reason, if you run a lot of ODBC links, you will find that your Access database will just keep increasing in size until you reach the 1GB limit. The best thing to do is make a monthly job to run the compact routine. You should find that this reduces the size back down and you can keep re-running this as often as you need.

I have spoken to many techies about this, and nobody knows why it happens, it is just something that ODBC seems to do....??

Hope this helps,

Tim

 

[KHerm]

Dave and Tim,

Thanks for your help. You don't need to answer this, but now I'm wondering what Repair and Compact actually do. I know it can't hurt data in the linked tables, but do I run the risk of losing Access objects? Fortunately we have lots of backups, but right now Access doesn't seem very stable to me. (As you may have guessed, I'm not real familiar with Access, my experience is with Oracle and Sybase, but the luck of the draw got me an Access assignment!).... and so it goes.


Thanks again,

Ken

 

[ErikZ]

For some reason, when you delete something in Access, it doesn't really get deleted. It stays around forever. "Compacting" is actually garbage collecting. It removes the deleted material out of the database.

I don't know about the error correcting. I think that's new to Access 2000.

Later,
ErikZ

 

[scking]

Here's the story. Access uses disk space as scratchpad storage and doesn't return the diskspace. As the disk becomes fragmented the size of the database grows. Compacting does exactly as the name implies, it compacts the disk space into adjacent disk space exactly like defragmenting your hard drive only it does it only for the .mdb file. Unless you regularly compact the databases they will always grow in size. If you ignore them they could get extremely large and cause severe performance issues. And don't only compact the front-end and ignore the back-end. You will have to open each individually to perform the compaction.

Steve King Professional growth follows a healthy professional curiosity