Solved

Unwanted expanding databases

Posted on 1998-08-23
4
120 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
  • 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 100 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

863 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now