Database Maximum Size

[cavscout]

My Access 97 database has reached the maximum size. If I understand all I've read on the web, I think this is due to a couple of tables being around 15,000 records. One table has about 15 fields the other more like 30 fields.

I still need access to all of the records, but is there a way to separate the older records into another table and have it work as an extension of the original?

The only other option I've heard is to migrate to SQL?

Any direction is appreciated.

Thank you.

 

[Vassago]

Hmmm, I have tables with over 200,000 records on them and I'm not getting any maximum size errors. We also have a database that is already 789 MB because it holds an archive of EVERYTHING. Maybe you are having a different problem?

 

[cavscout]

Thanks for the quick reply,

I thought 15,000 was kind of low too. There aren't any other large (More than 20 records and five columns) tables in this DB. A linked table shouldn't affect size should it? When I look at the size of the DB it's 1.04 Gig. Sounds crazydoesn't it?

Any idea where I should start looking?

 

[Vassago]

That's a pretty large database. Have you tried compacting the database? That probably would drastically reduce the size. I think you're problem is more related to the large size of the database more than the number of records in your tables. Try compacting and let me know what happens.

 

[cavscout]

Thanks, I'll try that.

One of my co-workers thought the compacting would be temporary because we run updates to this DB from another DB. Would I have to compact the DB after each update?

 

[Vassago]

You probably won't have to after each update. You can also code your database to compact after it updates. First try compacting to see if that helps. How did you manage to get such a large database? Do you have a lot of queries?

 

[cavscout]

Thanks!

The database is really very small only about 5 queries, no forms or reports, one macro that updates one of the tables and no modules.

Just about our entire office taps into this database constantly via other databases for data entry and reporting.

Thanks for your help. I'll let you know how it goes.

 

[Autoeng]

You definately need to compact. You are no where near the max size of a 97 db. You can get the specifics of 97 by entering "limits" in help.

 

[cavscout]

Database maximum Size

Thanks for your response. I tried to Compact last night while the network was not busy. After clicking 'Compact' the progress bar appeared and went all the way across but it never actually compacted the DB.

I tried compacting some of my smaller DB's and it worked fine. Very cool by the way. They reduced to about 1/3 of the size!

Could the fact that the DB is already to it's max be making it difficult to compact? Is it possible to compact one table at a time?

Also, there may have been an update running while I tried this. I should have suspended the updates last night just to avoid problems.

Thank you again.

 

[Autoeng]

I don't think that the max size caused the problem but you definately have to suspend anything going on. Plus, this is going to take awhile to do so don't be suprised if it take a long time to finish.

 

[cavscout]

Database maximum Size

Thanks!

 

[jtvcs]

If still having problems compacting. Try creating a new database, then File | Get External Data | Import all the objects from the current database. See if this doesn't change the effective SIZE of the overall database. It could be that your daily updating of the active database is creating a lot of temp files inside the mdb -- this would lead to an oversized file. Good luck.

 

[cavscout]

Thanks, Good Suggestion.

 

[The_Doc_Man]

Another thought:

I had trouble with a DB and had to compact it, but COULDN'T! The problem was that a quota had been established on the folder and I couldn't hold both the big ugly mother and the new, cleaner version at the same time. I won't try to explain all the CRUD I had to go through to clean that up, but I would also look at the quota and or free space available on that disk BEFORE you attempt the compression.

Remember, a compression automatically does what jtcvs suggested. It creates an empty database and imports every permanent object into that file, then deletes the old copy and renames the new one to the old name. BUT.... that means that for the duration of the compression, BOTH databases must coexist. By default, in the same directory.

Perhaps you could try a compression through the menu bar for the case where a database isn't open at the time. In that case, you get the option to specify the destination devices, whereas when you do the compression of an already-open database, Access assumes you mean "compression within the current folder." That might be at least part of your problem.

(Oh, by the way... I can't swear to this on a stack of Bibles, but I think Repair operations do the same sort of thing...)

 

[cavscout]

Maximum Database Size

Thanks to everyone for the help.

I finally realized that the database had compacted but Access had not deleted the orginal huge DB and renamed the new compacted version to the old name. It had named it 'db1.mdb' and it got mixed in with all my other DB's. I went in and renamed it manually after the new DB was filled.

I think this is due to a permission somewhere preventing the DB from being renamed in this folder because I can compact in other folders and Access handles the process perfectly, all though I did notice my 'Context Help Messages were deactivated so I had to go in and re-enter the help message to make that work again.

My record counts are identical, and so far I've not heard of any data loss from recent data entries but I'll keep watching that for a while.

If I've not answered a question please let me know and I'll follow up.

Thanks Again!

 

[Autoeng]

So... How much did it compact by?

 

[cavscout]

Maximum database Size

It Dropped from 1.04 G to a little over 6 M

 

[Vassago]

Yeah, I would say that's a significant difference. Did you have a lot of updates happening in the database? This causes Access to create the temporary junk that it doesn't know how to delete on it's own. Also, a lot of queries running can create this "junk" too.

Glad to hear that everything looks better. Let us know if we can be of further assistance. I think we've all had similar problems.

I had a problem like you did Doc_Man with the networked drive being full. I know what it takes to get something like that cleaned up. We have data on our network drive from years ago that could easily be tossed out. I don't think anyone knows how to clean up after themselves.

 

[cavscout]

Maximum Database Size

We do have many queries running in normal operations and we also run queries that update many tables that feed other DB's.

Data appears to be intact and all is well!

Thanks Again.

 

[The_Doc_Man]

Cavscout - If I may suggest it, you need to set aside a time to do a regular maintenance operation on your DB. I have mine set up to do once-per-week cleanup. My users now know that at the appointed time, they had better get the he|| out of the DB because otherwise I will come after their shaggy bu++s.

At that time, I make a copy of the DB, then repair and compress it. If that works, I make a copy of the compressed DB (in a different place than the working copy or the backup copy.) Then and only then do I delete the previous backup copy. As wasteful as that sounds, regular maintenance will keep your 6 Mb DB from regaining its lofty pinnacle of 1.04 Gb. And it is FAR cheaper to keep a copy of a 6 Mb DB than it is to keep a copy of a 1.04 Gb. DB.

Vassago, I know what you mean about supposedly adult users who leave behind messes for others. Have you ever noticed my tag line? Sometimes I think the folks here couldn't empty p|$$ out of a boot with the directions written on the heel, much less clean up after themselves.

Just remember, USER is another four-letter word!