Solved

Unwanted expanding databases

Posted on 1998-08-23
4
119 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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…

758 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

19 Experts available now in Live!

Get 1:1 Help Now