Hi guys,

Got a slight problem that really needs to be addressed...

In our factory we have a couple of computers setup, that the staff out there can perform various operations on. But because of the sheer amount of data that is being handled, and accessed in the back-end at any one time, the back-end becomes quite bloated, and therefore starts getting a little sluggish.

I know that I can't be the first person to encounter such a problem. I also know that doing a simple compact and repair (or using a .bat file, which is what I actually use) will clear it up. The issue being though, that to perform that on the back-end, I have to go around to all the PC's and log them out of the database. This is obviously a huge pain, and as we're expecting to move soon, as well as scale up both the business and the database, the problem is only going to get worse.


What I'm looking for is a script that can be run from somewhere (don't really care where), that upon my command will terminate all open clients accessing the database, so that I can compact it to its more regular 20MB filesize.

The only way I can think of achieving this is messy, and involves Java and sockets, and all sorts of stuff that I don't want to have to program if there exists a much simpler way to achieve this goal.

Just so you know the network infrastructure:
QNAP SERVER - Database Back-end
<->
Wired Network - Utilising two 20 socket switches and a couple of routers
<->
6 Workstations (at the moment) - Individual Database Clients (Front-end)


Any ideas and/or experience from people who have encountered similar issues?


Thanks in advance guys.


PS: Same goes for the need to add tables, change data types, rename columns, all that jazz too...

When users are working on databases it is not advisable to intervene and close the database without their knowledge, doesn't matter what method you follow.

Instead, you can send an Alert through Network and give them a target time to close the databases for maintenance. You can use the NET SEND command (Works in Windows Networks, if enabled) to send messages like the example given below:

C:\>NET SEND <Client Machine ID> "Message Text"

You can create a Batch File with separate lines for each client machine and run the batch files from desktop.

Check the following Links for some of these examples:

Sending Alerts to Workstations
Database Open/Close Event Alerts

If you want to go with your original plan then you can get VNC (Virtual Network Connection) Server and Viewer Programs installed on your machine and Viewer program installed on Client Machines. Set a common password on the VNC Viewer control on client machines. Check the VNC program help documents. VNC Viewer is free to download from Internet, you have to buy the VNC Server program.

I have built in the ability you described into my databases.

You'd first need to know who is "online" within the db. Within my main form that remains open all the time, or the switchboard if you are using that, you'd need to log that the user is on. I use a sql statement that runs when the db is open. Something like "update user_information set online = 't' where username = " & "'" environ("username") & "'"

Of course you'd need to set up the user_information table with the required columns such as UserID, a column to show the user is online, etc.

At this point you could create a form that acts as a message. Set up a timer on the switchboard to run to check a messages table. In that table you'd need the userID, a column to show if it's been read, and the subject, etc.

The timer would run the sql and find the unread message based on the userID and open the message form with the new message.

You could set this up to allow only the message to show, and not reply, or a form could be created to allow the user to message back.

With the user information table created, you could actually achieve what you want, such as kicking off the user, but as already mentioned, it's not a good idea generally.

That is the general idea.. if you'd want to create messaging within your db.
I am no expert, it can be done better I'm sure, but that's what I've done and it has been successful.

Just saw your other post about not having user login accounts on the machine.. so the above may not be an option.. although the should have something you could use.. machine name maybe?