Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Unwanted expanding databases

Posted on 1998-08-23
4
Medium Priority
?
126 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

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 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…
Suggested Courses

618 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