MS ACCESS db growth limiting?

Question

I have an access front end db that is using linked tables and therefore shouldn't be storing much of it's own information. The purpose of this db is to run queries, but it grows out of control. I want to know if there is a way to limit the growth of it's log or cache files (I assume generated by query running) or if there is a way to clean the cache/log file?

Answer 1

You really need to compact and repair access databases if they use a lot of temporary tables. The reason is because if a query you use creates a table with say 1,000 temporary records once per day 7 days a week, that space taken up by those tables is not reclaimed until you compact and repair. Access breaks down when a given database hits about 2 GB in size, or when a table hits around 1 million records. Those aren't hard and fast values, but generally they are a limitation of Access.

Answer 2

Go into Access without opening a database. Go to Tools > Database Utilities > Compact and Repair.... If that works, you can create a VBScript or something and schedule it to run periodically using Scheduled Tasks in Windows.

Answer 3

Access supports 2 billion records per table.

Answer 4

I'm kind of curious why the front end is growing so rapidly....do you use a lot of temp tables? Code with lots of recordsets?



Use the compact and repair on the back-end also.