Solved

Unwanted expanding databases

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Put text in a picture ASP.NET C# 2 60
Excel Automation VBA 19 88
Need Nag Screen for Reboot if system up time is over 14 days 12 61
checkbox to hide entire section 10 41
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…
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…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

839 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