MDB Space Usage Growth

[rharding]

I have a process which runs automatically once a day looping through a set of table names deleting all the data from the table (DoCmd.RunSQL "DELETE * FROM " & MyQry!Name) and then importing data from CSV files (DoCmd.TransferText acImportDelim,...) back into the table. Some of the tables are extremely large.

The VBA code and the data are on separate MDBs. I am using Access 2002-2003 file format.

When the data MDB is fully compacted it is about 280-290MB. After one nightly processing run the database has grown to over 800MB.

Questions:
1) Doesn't Access reuse space if a table is deleted?
2) Is it possible that Access is maintaining a temporary table of deleted records which is causing my MDB to grow? If yes, can I tell Access not to maintain this table? If yes, how do I tell Access not to maintain this table?

[Killer42]

1) Doesn't Access reuse space if a table is deleted?

Nope. :)

2) Is it possible that Access is maintaining a temporary table of deleted records which is causing my MDB to grow? If yes, can I tell Access not to maintain this table? If yes, how do I tell Access not to maintain this table?

Like much database software, Access simply ignores the wasted space, for performance reasons. To clean it up, you have to compact the database. This can be done manually under the database tools menu (it's called "compact and repair"), or from code. NeoPa has posted code here to do the job - try a quick search on something like "NeoPa compact database".

Note that probably the best time to do the compact is immediately after your delete. With nothing in the tables, compacting the database will be very quick. You could do it after the import, but then it has to copy your 300Mb of data again. You see, compacting basically copies all current data (plus forms, etc.) to a new database, which leaves behind the deleted records. Then it deletes the old one and renames the new one. So the less there is to copy, the better.

[rharding]

While I understand that Access is ignoring the MDB space that was used by the tables that were deleted, I am not certain that fully explains the quick growth in space usage. The data that I am loading does not increase in size significantly over time. I would therefore expect a fully compacted MDB of 380MB to about double in size after a delete and reload processing run. What I am seeing is an immediate jump to the 2GB limit.

I will say that this does not happen every time we make the processing run. For instance the last time it hit 2GB I got everyone out of the MDB, compacted it, and then had the reload process run. It jumped from 380MB to 800MB. Yesterday afternoon I compacted the MDB to 380MB and this morning it had jumped to 2GB.

This MDB is used by people in the USA and in Europe. I have no way to ensure no one is using the MDB at the time the process is suppose to run. Is it possible the problem is caused by someone actively or inactively using the MDB at the time the delete and reload process runs?

[rharding]

Additional information.

This is a reporting database. Aside from my once a day delete/reload of the tables, very little table updating is done by users.

[Killer42]

While I understand that Access is ignoring the MDB space that was used by the tables that were deleted, I am not certain that fully explains the quick growth in space usage. The data that I am loading does not increase in size significantly over time. I would therefore expect a fully compacted MDB of 380MB to about double in size after a delete and reload processing run. What I am seeing is an immediate jump to the 2GB limit.

Oh! Well, that's a different story. You said it was growing to about 800MB, which seemed annoying but not unreasonable.

I will say that this does not happen every time we make the processing run. For instance the last time it hit 2GB I got everyone out of the MDB, compacted it, and then had the reload process run. It jumped from 380MB to 800MB. Yesterday afternoon I compacted the MDB to 380MB and this morning it had jumped to 2GB.

That is odd. However, I suspect splitting the database may be part of the answer. I think reporting often causes a lot of temporary stuff to be created (which I think you suggested originally). Maybe people are creating lots of temporary copies of the data in your database, or something.

This MDB is used by people in the USA and in Europe. I have no way to ensure no one is using the MDB at the time the process is suppose to run. Is it possible the problem is caused by someone actively or inactively using the MDB at the time the delete and reload process runs?

I'll have to defer this to the real Access experts here.

I will say again, though - if you haven't already, I'd suggest splitting the front-end user interface) from the back-end (data). That way, hopefully any temp stuff the users create will be in their own front-end database and won't all be lumped in together.

One thing my be worth noting - I'm almost certain you cannot compact/repair unless you have exclusive use of the database.

[Killer42]

... I would therefore expect a fully compacted MDB of 380MB to about double in size after a delete and reload processing run. What I am seeing is an immediate jump to the 2GB limit.

I also wanted to say that while 2GB does seem extreme, I would expect it to increase considerably more than the 380MB. I do large-ish imports to a big Access database fairly frequently, and I always compact afterward because it does leave a lot of wasted space. My guess is that it uses a lot of space rearranging indexes and so on.

[NeoPa]

Code found in this thread (Compact / Compress Access File using code)
I've never really understood why Access gets so bloated (ever since Access 2000). Killer's advice is about right but I'm not sure what to do about never having the database available and unused.
You could try Compacting to a different file (the code supports that) then renaming it when it's not in use - can be hard to arrange though depending on usage.

[Killer42]

Code found in this thread (Compact / Compress Access File using code)
I've never really understood why Access gets so bloated (ever since Access 2000). Killer's advice is about right but I'm not sure what to do about never having the database available and unused.
You could try Compacting to a different file (the code supports that) then renaming it when it's not in use - can be hard to arrange though depending on usage.

Yes, as long as there is absolutely no chance that someone needs to update the database in the interim, you could compact/repair to a second database, then have a process that keep trying to switch names around until it succeeds. Interesting question - is it possible to rename a database file that someone has open? You might initially think "of course not!" but think about it - once the file is open, the name is really immaterial.

Depending on how the Access-based system works, you could also implement things such as throwing out all current users, warning users to please log out, automatically connecting users to the latest in a series of db files, etc etc etc.

[NeoPa]

Renaming open files, though an O/S issue rather than an Access one, is not possible in Windows. This is regardless of the type of open (Access opens for Read/Write always of course).

[rharding]

Code found in this thread (Compact / Compress Access File using code)
I've never really understood why Access gets so bloated (ever since Access 2000). Killer's advice is about right but I'm not sure what to do about never having the database available and unused.
You could try Compacting to a different file (the code supports that) then renaming it when it's not in use - can be hard to arrange though depending on usage.

Thank you for the link to the compaction code.

Remember I have a front-end MDB (MDB1) and a data MDB (MDB2). Now my question is:

I know you can start Access standalone and compact MDB2 and you can open MDB2 in Access and compact it in place. However, can MDB1, which deletes and reloads the tables and therefore has the tables from MDB2 linked (attached) to it, run the compaction code against MDB2? Another way of asking this question is: If I cannot compact MDB2 when someone else has it opened via their use of MDB1, can I compact MDB2 when I have it opened from MDB1?

[NeoPa]

You will need to test but I think the answer to that is :
"As long as you're not accessing any of the linked tables at the time."

[Killer42]

You will need to test but I think the answer to that is :
"As long as you're not accessing any of the linked tables at the time."

I may be wrong, but my experience with this seems to be that as long as you have a database open which is linked to it, it's considered open.

In terms of this example, that would mean that (AFAIK) you can't compact DB2 as long as anyone has DB1 open. As you said though NeoPa, this would need to be tested.

[Killer42]

Renaming open files, though an O/S issue rather than an Access one, is not possible in Windows. This is regardless of the type of open (Access opens for Read/Write always of course).

Not if you say to open it read-only. Or if the file has the read-only attribute on when Access comes to open it. That might be a good idea, for the general users, if they don't need to update it.

[NeoPa]

I may be wrong, but my experience with this seems to be that as long as you have a database open which is linked to it, it's considered open.

In terms of this example, that would mean that (AFAIC) you can't compact DB2 as long as anyone has DB1 open. As you said though NeoPa, this would need to be tested.

I compact my linked databases with code in my Front-end database every month. I think that means it works (at least from Access).
I should clarify - I do compact to a differently named file before renaming it back. As long as nothing is currently accessing data from the database being compacted, it is not kept open by Access.

Not if you say to open it read-only. Or if the file has the read-only attribute on when Access comes to open it. That might be a good idea, for the general users, if they don't need to update it.

I didn't think it would open if set to R/O but I tested it & it does so...
I don't believe you can rename it though, as - as I said before - the O/S itself won't allow a rename if there are any processes that have it open (R/O or otherwise).

[Killer42]

I compact my linked databases with code in my Front-end database every month. I think that means it works (at least from Access).
I should clarify - I do compact to a differently named file before renaming it back. As long as nothing is currently accessing data from the database being compacted, it is not kept open by Access.

Um... you didn't just imagine it every month, by any chance? :)

Actually, I'm glad to know that's possible; I might make use of it in the import procedure in my gigantic database. (I might also split into smaller chunks from now on, as compacting a gigabyte or so can take a while, especially on a network drive.)

I didn't think it would open if set to R/O but I tested it & it does so... I don't believe you can rename it though, as - as I said before - the O/S itself won't allow a rename if there are any processes that have it open (R/O or otherwise).

Fair enough. I did know for certain that you can open them when they're R/O, as I do it all the time. You know my biggy is split into chunks. Well, I keep all but the latest chunk R/O for added safety.

I had been assuming you would turn off the R/O attribute before trying to rename.

[AricC]

If you have people adding/editing/deleting from the database in the US and EU maybe you should think about MSSQL or something where you can lock the tables during updates. Are you having problems with data corruption?