My back end is expanding

[Trigon]

Nope its not a medical condition.

I have created a split database for users to keep control of their daily work targets.

I have 3 tables Import, Old and Current. Data is imported into the database from the system into import, a query works out what cases are missing (finished with) and moves them from current to old, with another query then adding new items to current. This is scheduled to happen every half hour.

However, every time this happens the back end is expanding in file size by about 30mb this happens until it hits the 2gig limit. I then run compact and repair and it brings it down to 5mb again.

Im using 'CurrentDb.Execute "DELETE FROM TBLImportPost", dbFailOnError' to clear out the table before importing, but its as if its not properly deleting them from the DB.

Any ideas would be much appreciated.

Thanks

Chris

 

[namliam]

It is properly deleting them, however access simply does keep the space reserved and doesnt free it up again untill you do a compact & repair. The is called "Bloating"

Options:
1) Fix your database so that there isnt a seperate OLD table, instead just one table with records that are marked as OLD
2) Compact and repair your database on a regular basis using the windows schuduler or something. Say every sunday.
3) Fix your DB so that your import table is on a seperate backend database that isnt contantly locked by users (as well). As a result you no longer have this bloat in your "main" backend and can easily "code in" a compact and repair on that particular seperate backend every time the import runs

Just the #3 will likely resolve 90% of your problem and is easy to implement
2 Is easy to do if you are not worried about the PC being on and have a free window where no one is using your db.
1 is prefered though would still require 3 to happen as well, at this point though a sizable rework of your db is probably out of the question.

My advice, implement option 3 asap and see how things work out for you

 

[GinaWhipp]

Yeah, well, when running Action queries your database will expland and will not regain the space even after removing the records from the table until you compact and repair. Have you considered SQL Server? You can use the free version but the big plus for you is that it does a better job of managing it's growth (size) when records are removed.

 

[Trigon]

It is properly deleting them, however access simply does keep the space reserved and doesnt free it up again untill you do a compact & repair. The is called "Bloating"

Options:
1) Fix your database so that there isnt a seperate OLD table, instead just one table with records that are marked as OLD
2) Compact and repair your database on a regular basis using the windows schuduler or something. Say every sunday.
3) Fix your DB so that your import table is on a seperate backend database that isnt contantly locked by users (as well). As a result you no longer have this bloat in your "main" backend and can easily "code in" a compact and repair on that particular seperate backend every time the import runs

Just the #3 will likely resolve 90% of your problem and is easy to implement
2 Is easy to do if you are not worried about the PC being on and have a free window where no one is using your db.
1 is prefered though would still require 3 to happen as well, at this point though a sizable rework of your db is probably out of the question.

My advice, implement option 3 asap and see how things work out for you

Is there likely to be major problems if I was to compact and repair while people are in it? I could get this running every night say around 7pm but people leave their machines on all the time and stay in things so they can work from home.

Thx

 

[burrina]

I would suggest a auto log off feature and then allow them to connect as needed.

 

[Trigon]

Is there likely to be major problems if I was to compact and repair while people are in it? I could get this running every night say around 7pm but people leave their machines on all the time and stay in things so they can work from home.

Thx

I've just tried it on a test version and realised that it doesn't let you :-(.

It looks like option 3 is the way to go then. I can see people getting annoyed if I kick them out every night.