Compact an Access database before close.
Good afternoon,
I have a Marco that runs an update query in Access, answers all of the pop-up questions, and then closes the database. I would like to add a little code to the macro that would first compact the database before it closes it. I don't see it as one of the options in the drop down under "Actions". Just in case, here is a link to my original question that helped me create the macro in the first place: https://www.experts-exchange.com/questions/22478180/How-to-Create-a-Macro-for-an-Update-Query-in-Access-2003.html
I know I can set the database to automatically compact when it closes, but I believe it may error if two people are in the database at the same time, and one of them closes it, which would result in the database trying to compact when someone else is still in it. Is that correct, or is that setting a per user setting?
Thanks in advance for the help, Jon
I have a Marco that runs an update query in Access, answers all of the pop-up questions, and then closes the database. I would like to add a little code to the macro that would first compact the database before it closes it. I don't see it as one of the options in the drop down under "Actions". Just in case, here is a link to my original question that helped me create the macro in the first place: https://www.experts-exchange.com/questions/22478180/How-to-Create-a-Macro-for-an-Update-Query-in-Access-2003.html
I know I can set the database to automatically compact when it closes, but I believe it may error if two people are in the database at the same time, and one of them closes it, which would result in the database trying to compact when someone else is still in it. Is that correct, or is that setting a per user setting?
Thanks in advance for the help, Jon
oops sorry
You cannot compact an open mdb per se. If two people are actually in the mdb, you will get an error if you try to compact it from Tools>>Database Utilities ...
mx
mx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The 'setting' you refer to is *not* per user ...
mx
mx
First of all, you need to split your database into a front end and a backend. You'll not need to compact the front end after that.
You can then manage your compact and repair for the Backend by simply checking for a lock file for the database name, if no lock then you can shell into your back end, have IT set up as Cap explained so that all you have to do is open it then close it and it compacts on close.
J
You can then manage your compact and repair for the Backend by simply checking for a lock file for the database name, if no lock then you can shell into your back end, have IT set up as Cap explained so that all you have to do is open it then close it and it compacts on close.
J
see this link for Nelson's brilliant answer to this issue.
https://www.experts-exchange.com/questions/21517225/Compact-Repair-Solve.html
https://www.experts-exchange.com/questions/21517225/Compact-Repair-Solve.html
Actually, compacting the front end on a very regular basis is just as important as compacting the back end. With multiple users on the front end (even one user), over time internal indexes and pointers can easily get hosed up, not to mention the bloat factor. So, compacting the front end is just good preventative maintenance ...
mx
mx
ASKER
If I set the backend to compact on close, will it only compact when it is physically opened, or will it compact when it is opened by the frontend too? Thanks, Jon
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for that information. It is wierd taht you should mintion that because I used to be a DBA in the navy, and in the Access database I inherited I could open the .idb file with Notepad, and it would show me the computer ID just like the code you sent me does, but that is the only database I have seen do that. Is there something I can do to make this database's .idb file function the same way? When I open the .idb file with Notepad now it returns this "ぅ〳㈰7
try opening it with Word.
ASKER
There is another SQL database that houses the the computer IDs, and the peoples' contact info that use them. If I careate an ODBC linked table to that information, is there a way I could create a query that would call the ShowUsers module I just created, and return the computer names and their owners' information? If you want me too, I can start a new question, and give you guys credit for the original question. Thanks, Jon
ASKER
Thanks, it worked with Word.
ASKER
It also opens correctly with IE7 too. Thanks,
*** ALSO*** I would suggest (read: HIGH Recommend) reading this article by Kaplan about C&R, which was recently re-printed in Access Advisor (Feb or Mar 2007) and confirmed by Ken Getz ...
http://www.trigeminal.com/usenet/usenet023.asp?1033
Save your self the trouble and use Michael Kaplan's addin (trigeminal.com) that I posted above. It does all the work for you - and it's *free*.
"Have you ever wanted to close a database, then maybe compact it, and maybe open it back up, open up another database entirely, and maybe even run a procedure inside the database when it is opened?"
mx
ASKER
Thanks again, Jon
tools>options>general
check the Compact on CLose