?
Solved

Unwanted expanding databases

Posted on 1998-08-23
4
Medium Priority
?
125 Views
Last Modified: 2010-04-30
I have an application in VB5 that uses an Access database.
There are lots of Querydefs in the database that the program uses. Just opening and shutting the program causes the database to grow in size. I am closing  and setting the querydefs to nothing but still it grows. Any ideas? What should I be looking for?
0
Comment
Question by:brucerock
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 3

Expert Comment

by:a111a111a111
ID: 1430473
Make it read only.
0
 
LVL 3

Expert Comment

by:SPECIALIST
ID: 1430474
What you need to do is create a macro in your access db or code in vb or the db that says this:
private sub command1_click
SendKeys "%tdc"
end sub

This will compact the database every time the event is executed.  You can set the event to whatever you want.
(else you can do this manually by going in access and go to tools database utilities--compact database.
This will significantly reduce the size of the db.



Specialist



0
 

Author Comment

by:brucerock
ID: 1430475
I cannot make it read-only as it is a read/write database. My statement is that it will grow bigger regardless of whether I enter data or not. Just opening and closing will make it grow bigger.
I have a routine that will compact the database on exit but that is avoiding the issue of WHY it is expanding. It is a large database that will take time to compact every time it is closed and it doesn't seem very professional.
0
 
LVL 3

Accepted Solution

by:
SPECIALIST earned 200 total points
ID: 1430476
You don't have to compact it everytime, but I have a database at work that has over 25 meg, when I compact it, it is about 3 meg.  

Why is your database expanding?  Everytime you open a table, query, form report, data gets "misplaced" it loses indexes, etc.  Take your database now, and look at the size, compact it and look again.  when you compact it it reclaims wasted space.  I assure you this is your only real option.  If your database it just too large then you need to start considering a SQL server.

If you don't want to spend 30 seconds to compactit on exit.  You can create a routine that will run everynight at midnight that will open your databases, compact them and close them.

If you find that your database does not get smaller after you compact it, then either your database is inefficiently designed or it is just getting too large for Access.  I program Access/VB for a living this has been my experience, I really doubt you will find any other solution.

If you find that my answer makes sense, and you accept it, I can give you some ideas on how to setup that compact utilty routine I told you about, post your email address I will send it to you, being that it is too large to post.

Specialist
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month15 days, 6 hours left to enroll

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question