Access 2000 File size

[Willie78]

Hi all

Can someone explain to me why an access file will grow by a hundred meg when all i have been doing is running queries?

No data has been inputed. I'm guessing maybe wrongly that it use's the disk space when running the queries and doesn't give it back.

A compact and repair reduces the size of the file back down which is a fix.

Also is there anyway to stop or at least help this from happening happening?

Thanks in advance

Paul W

 

[longhair]

Willie78,
make sure your queries are using fields that are indexed.
are the queries updating anything?
pulling data from another source?
regards,
longhair

 

[Willie78]

Longhair

not all the fields in the query are indexed just the key search fields. All of the data is local to the database.

They are simple but in some cases large select queries.

Some of them are also being used to produce forms.

Today alone the file size grew to 180mb. When I compacted it reduced to 1.7mb. As I said earlier no data has been added yet it grew 10 times.

I can't nail it down.

When queries are run does it use temporary disk space or are they run and stored in memory.

Cheers

Paul W

 

[longhair]

Willie78,
i belive access works just like all other ms products (excel, sql, etc) diskspace is allocated to hold all the data & expands when forms, queries, reports, etc are run. the only way to get the db back down in size is to run the compact / repair.
it still seems very odd that it is growing that much. i'd check each query and make sure that any of the fields that you are using for the search are indexed - i think this may be happening because you have one or more queries where you have 3 parameters but only 2 are indexed and you wind up doing a full table scan.
if you look in access help there is a command line switch to do a compact and repair - what you could also do is set that up in a .bat job and run it when you know no one has the db open.

regards,
longhair

 

[BNPMike]

That is wierd. I've never seen the database grow without adding something to it.

 

[Ed2020]

I've seen this with all versions of Access from 2000 onwards. I don't believe it happened in versions prior to 2000. It even happens in frontend databases with *no* data tables - only forms, reports, linked tables and code.

Opening lots of recordsets seemed to excacerbate the problem, as did having timer events running. From what I've seen the database won't keep growing until it hits the 2GB limit - it will level off way before that. Not a major problem unless you have very limited disk space, but certainly an odd one.

Ed Metcalfe.

Please do not feed the trolls.....