DB Size Increase

[ttp]

I have noticed that the size of my database is increasing after I run a make
table query. The number of records and data retrieved is remaining the same.
What could be causing this? How can I fix this issue?

 

[fredg]

I have noticed that the size of my database is increasing after I run a make
table query. The number of records and data retrieved is remaining the same.
What could be causing this? How can I fix this issue?

Compact the database often.
Tools + Database Utilities + Compact and Repair

 

[Dirk Goldgar]

I have noticed that the size of my database is increasing after I run a
make
table query. The number of records and data retrieved is remaining the
same.
What could be causing this? How can I fix this issue?

Access (Jet) databases normally grow in the course of use. Occasionally
compacting your database, using the Tools -> Database Utilities -> Compact
and Repair Database... menu item, should restore it to its minimum size.
However, unless you are getting extreme and abnormal growth, or the database
file is approaching the maximum size of 2GB, you needn't be too concerned
about the growth.

 

[Roger Carlson]

The size of an Access database will increase dynamically when you need it
to. One of those times is when you use a Make Table query. However, it
will not automatically shrink again when you delete data. To do that, you
must Compact the database.

In Access 2003 and before, go to Tools>Database Utilities> Compact and
Repair Database
In Access 2007, go to the Start button (upper left), go to Manage, then
Copact and Repair.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Maarkr]

just to be sure...you aren't running the same make table query without
deleting the old one, are you? You have just the one table name and not
Table, Table (1), Table(2), etc.

 

[Dale Fye]

One way to work around this is to create the make table in another database.
I will frequently create a apptemp.mdb file and create tables in that which
are are generally temporary in nature. Then, I link those tables into my
application, and rather than running make-table queries, I delete from and
insert records into these tables. This will prevent your application file
from bloating, but will require that you occassionally compact the
apptemp.mdb file.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[Tony Toews [MVP]]

One way to work around this is to create the make table in another database.
I will frequently create a apptemp.mdb file and create tables in that which
are are generally temporary in nature.

See the TempTables.MDB page at my website which illustrates how to use
a temporary MDB in your app.
http://www.granite.ab.ca/access/temptables.htm

Then, I link those tables into my
application, and rather than running make-table queries, I delete from and
insert records into these tables.

However relinking the tables may increase the size of your FE MDE
about 4k each time you relink. I have to experiment a bit more with
this the next time I use this technique. I may decide to use queries
with the IN clause to get around this. Maybe.

This will prevent your application file
from bloating, but will require that you occassionally compact the
apptemp.mdb file.

I create and delete the temp MDB as required. That's a very fast
operation. But your solution works as well. I'm just pointing out
an alternative.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Dale Fye]

Tony,

I've done it both ways. But if I have more than a couple of these temp
tables, I just create a module that will create the temp database, and then
create the temp tables in it. Then I link them once to the application and
never disconnect them. The application checks to make sure the tempdb is in
the same folder as the application (or sometimes I'll use the windows temp
folder). If it isn't there, it runs the above code.

Dale

 

[ttp]

Thanks alot. This worked. I will now use the compact and repair command in
my macro to keep the size down for the users so that this won't become an
issue.

 

[ttp]

I tried using the compact and repair commands in my macro using Run Command
function. When I ran the macro, I received a message stating that I can not
use these commands while running a macro. Thus, not sure why they are
available as choices. My macro will be run by an user weekly. Thus, the
file size will be steadily increasing. Is there an automatic method to
compact/repair without having an user go into MS Access? Can I compact the
db size outside of MS Access? If I run the macro from my desktop, how can I
compact or maintain the size without going to Access>Tools Menu?

 

[Douglas J. Steele]

You can schedule a job using Windows Scheduler (or any other scheduling
product) that will compact the database for you, or you can look at 3rd
party products like FMS's Total Visual Agent
http://www.fmsinc.com/MicrosoftAccess/Scheduler.html

To schedule your own job, you need to use command-line switches, as
explained in http://support.microsoft.com/?id=209207

Personally, what I prefer doing is creating a batch file that a) renames the
current back-end database b) uses a command-line switch to compact the
renamed database to the "correct" database.