We have around 20 databases in our department. Some are linked to certain ones for different reasons. We have one database which stores a lot of data, lets call it main.mdb. main.db has 3 tabkes that are linked. the tables reside in data1.mdb. I want to compact main.mdb but it wont let me becuase everyone who is logged into the tables on data1.mdb shows up as having main.mdb open exclusively. I am able to kick people out of main.mdb becuase I am the administator. I have special code that helps me do this. i am not able to kick these others out. If they do not physically have my dabase open, i can NOT kick them out.
How can I compact my database while having these other people logged in. There are really too many people to call to have them log out. At times, there are 50+ people logged in. This is a 24 hour company so coming up here when nobody is here is impossible.
SUMMARY: How can I compact my main database while there are linked tables in MAIN from another database? Becuase the database I am linked to is highly used, it would be impossible to have everyone log off.
This is not ideal as you will have to kick the users out (but only for a few minutes). In fact it's more of a method of getting all users out of all the databases at the same time.
Make a form that opens when the database opens (it must stay open all the time the users are in the database so make it the menu of something.)
Base this form on a table and link the same table to all of the databases.
Make the form (in all of the databases) have a timer event to check see if a field in the underlying table (call the field ForceShutDown and make it Boolean) make the ontimer check see if this is true and if it is close the database.
Once all users are out (give them a msgbox saying they've got 1 min or they will be pissed) compact the database and then remove the ForceShutDown).
No exactly what you want but may help with the logistics.
I kinda get what your saying. Right now, I have 2 hidden forms that run on startup. One is called DetectIdleTime and it detects whether or not the system has been idle for 30 minuets, if so, it automatically shuts the database down. The other is SystemShutDown and it refreshes every 10 seconds. The SystemShutDown is just a macro. If I want to force users out, I just open the macro and type in the EXIT command and hit save, the next time that macro refeshes, the user gets kicked out.
My problem is it only runs one people PHYSICALY logged into the database. The problem I have is MY database has a linked table from someone else. So, my ldb shows those users as logged into my database when actaullty they are not, they are in their own database. Another department manages that database and I may be able to get them to agree to this shared user table you are suggesting, its just they may not like it becuase they need to be in their database all the time.
Of course a few minutes out shoudlnt hurt them, its just getting them to agree to letting us implement this procedure.
You can always write a small program to read the ldb file to obtain a list of workstations that are currently using your database.
With that list you can create a bat file to NET SEND a message to the workstations instructing them to exit the specific dbs linked to yours. Telling them that a corruption has occurred and you need to take the system for immediate maintenance.
After you have finished compacting the db. create & trigger another bat file giving an all clear message.
If you have a lot of dbs linked to yours, and you have no control over people creating new ones, then I maybe its time you considered migrating the data to a more robust platform ie SQL or Oracle db.