all querys adding at least 5-40 kb to my frontend database

[chiefvj]

Does anyone know why whenever you run any kind of Query Access will automatically add kB to the size of a database.

1. I have create a new database and imported the Queries, Forms and Reports and I have the same problem.
2. I have created new queries linked to the same tables and the problem still persists.
3. I have created new tables and new queries linked to the new tables and still the problem persists.
4. I have Compacted after I make changes to the database.

After a while even compacting will not return the database to its orginal size.

The frontend is only 872 kB so its not a problem as yet. However once it is online... with multiple users...this thing will get very larger.
Does anyone have any IDEAS why this is happening.
thx chiefvj

 

[fneily]

Anytime you create something "new", it'll increase the size of the database. Also, if you delete tables, the space from the deleted table is not reused - thus compact and repair.
New queries must be compiled. An optimizer evaluates the query to determine the fastest way to run it. This compiled version is ran the next time. This adds size to the database. Also, if the query is changed, then it must be compiled again. This adds size to the database.
That's another reason the tables are kept on the server and each client keeps queries, forms, reports, macros locally.

 

[chiefvj]

Thanks...

 

[chiefvj]

Just a few more observations.
1.All my tables reside on a server.
2.I Compile after every change made to the Visual Basic code in the events procedures.
3.I notice that when I run a Query the file size increases on the front end.
4. How do you recompile an individual query?
thx

 

[Crowley16]

you can't in access, but there is a setting which forces compact/repair on close of database.

although this is not always a good idea...

--------------------
Procrastinate Now!

 

[fneily]

To compile a query, run the query by opening it in Datasheet view and then close the query WITHOUT saving it.

 

[chiefvj]

More observation...
1.All my other applications run fine without add bytes to my frontend.
2.The Original Application was working fine...I made a modification to 2 of the queries and that is when the problem started.
3.I still have the unmodified application and that runs without adding bytes to the frontend( when ever i run queries).
4. If I modify a query in the original application the problems then occurs.
thx

 

[fneily]

2. That's what I told you.
3. That's correct. Queries were already compiled.
4. See 2
Did you read my post??

 

[lameid]

Another way of looking at this is that until all the queries are compiled (which happens sometime after a design change is saved), an MDB file may grow as inaddition to the program code that runs the code it may add ready to execute code. Once all this code exists and nothing is deleted, added or modified in the file it will eventually reach a stable size.

Also, use a split database and put the front end on the local computers (as already mentioned) to alleviate the network utiliztion problem caused by size.

If you don't believe us, open your database compact it. Compile your modules. Open each query and note database growth. Compact again.
Note the size. Run a query. Note the same size.