Size of access database grows after Preview of report (1 Viewer)

[mimijp]

Hi

I really need help with this. I have a database that is quite large and is accessed through VPN. When I shrink and compact the database it goes to a respectable size. But over time everytime a report is previewed the size of the database grows. Or every access it grows to double its size in no time at all.

Any ideas... on what would make it grow and how to stop it.

Thanks it is appreciated.

 

[Phonik]

Hi

How are you generating 'feeding' your report with data? Are you using SQL string or a QBE? (Physical Access Query)

 

[mimijp]

More info.

I am not really sure. It is an access database on a server that is accessed through VPN and also on the network by different users. Not SQL that is for sure but I am not sure what the other option is.
Thanks for giving me a hand.

 

[neileg]

Queries do tend to bloat the size of the database. You need to do a compact and repair to recover the dead space. You can set this to run automatically on close.

 

[mimijp]

It is hard to do that because the database is open most of the time so I can't get a pack and repair automatically on closing. Do you have any suggestion of things I could do. I am willing to change the database if needed.

 

[gemma-the-husky]

is it a problem?

as long as it is backed up regularly it should be safe

I support several A97 databases over 100Mb in size, with no performance issues

A97 limit is 1Gb, later versions 2Gb

 

[mimijp]

When it gets to big it is way to slow on the VPN that is why. Is there any other option than compact because users are always in the database. Can I program something

 

[RuralGuy]

How about using one of these? Kick Users Off - Detect Idle Time

 

[neileg]

I'm not convinced that a bloated database is any slower than a compacted one. The speed depends on data transfer, bloat is empty space.

 

[Dennisk]

It sounds as though the report is using a temp table.
Temp tables for reports have the contents deleted then re-generated for that report run.
As time goes by the number of deleted records increases therfore increasing the size of the Access db.

If this is the case then you could put the temp table into a db of its own link the table.

 

[mimijp]

Thanks everyone for the replies. I am look into different suggestions especially the temp table. But in the meantime I will continue trying to compact and repair. Thanks again for your suggestions.

 

[mresann]

The best solution would be to create a database on the fly, install the temp tables to that external database on the fly, then link to that database for your report data. Once the report is closed, you can use the close action to unlink the database, then delete the database. In addition, when you close your main database it can perform a "cleanup" function to delete any databases you may have created in the first place.