Append Queries and database size

  • Thread starter Thread starter rbm
  • Start date Start date
R

rbm

I understand that append queries, because they interface
differently than other queries, will cause the database to
increase in size everytime they run. I saw this myself in
a database that had a form that was displayed on a
separate monitor and updated by an OnTimer Event Procedure
every 30 seconds. When it "refreshed" it ran seveal
append queries to create a new table and populate it with
new current data. The end result was an increase in
database size (the backend database)of about 100KB every
time it refreshed. Initial database size was about 800kb;
at the end of a 10 hour tournament, it was up over
400,000kb (and I had changed the Timer to go at 90
seconds).

Any other ideas on alternatives to append queries?

My plan was to, on demand, delete all records in a table
and then repopulate it with results of every match in a
tournament. I want to start fresh every time to make sure
that I catch any changes that were made since the last
update.
 
Hi,

My name is Eric. Thank you for using the Microsoft Access Newsgroups.

You wrote:
"My plan was to, on demand, delete all records in a table and then
repopulate it with results of every match in a tournament. I want to start
fresh every time to make sure that I catch any changes that were made since
the last update."

Have you considered using UPDATE Queries?

If UPDATE Queries will not work then you may want to try Compacting your
database After the Delete and After the Append to remove the unused space.

You can do this programmatically, see the following for ideas:
ACC2000: How to Compact Databases At a Scheduled Time
http://support.microsoft.com/default.aspx?scid=kb;en-us;209979

Or search Microsoft Access VBA help topic for "CompactDatabase"

I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Eric Butts
Microsoft Access Support

"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."
 
I understand that append queries, because they interface
differently than other queries, will cause the database to
increase in size everytime they run. I saw this myself in
a database that had a form that was displayed on a
separate monitor and updated by an OnTimer Event Procedure
every 30 seconds. When it "refreshed" it ran seveal
append queries to create a new table and populate it with
new current data. The end result was an increase in
database size (the backend database)of about 100KB every
time it refreshed. Initial database size was about 800kb;
at the end of a 10 hour tournament, it was up over
400,000kb (and I had changed the Timer to go at 90
seconds).
Click to expand...

OUCH. Were you in fact using Append queries, or one or more (even less
efficient) MakeTable queries? A MakeTable not only takes up the space
used by the new table, but all its indexes *and* a lot of system table
overhead. Deleting the old tables doesn't help, since Access doesn't
garbage collect until you Compact.
Any other ideas on alternatives to append queries?

My plan was to, on demand, delete all records in a table
and then repopulate it with results of every match in a
tournament. I want to start fresh every time to make sure
that I catch any changes that were made since the last
update.
Click to expand...

Why not use a Select Query (which need not be updateable), displayed
on a Form? When you Requery the form it will rerun the query and give
you up-to-date data directly. There is generally no need to create or
fill a new Table just to display data!
 
Thanks Eric. I don't think Update Queries will work, but
I will definitely look into that. I also run into the
problem that, with other users accessing the backend data
tables, I can't compact the database unless I shut
everyone else off.
 
I am using Append Queries. I tried a MakeTable Query at
one point and had no sucess there because the new table
would end up in the frontend database and I would end up
losing the link to the one in the backend.

I will look at possibly creating a new form and using a
Select Query - great idea.
 
Back
Top