Bandage Solution Required-Kill Process

[HiBoo]

I've got a unique problem I need to address and would appreciate some assistance if possible for solving this problem.

Scenario: Retail Store(s) having 2 or more workstations + 1 server computer. Access97 database located on server computer. Workstations have an front end application that connects to the access97 db on the server. On a nightly basis (close of business), the policy is to shut down each workstation, thus closing the open application, and then run an end of day process from the server to finalize business.

Problem: Policy is not always followed and sometimes the workstations application is not always shut down. Additionally, the workstation may have been shut down but the application was not closed properly, thus leaving the server computer to think that a connection to the database still exists.

Resolve: What I'm looking to do is create a VB6.0 executable that I can schedule on each of the workstations and or the server that will automatically shutdown or close any connections to the database. I'm not sure of the best practice to do this though and could use some suggestions and or sample code. I've currently developed a reporting tool that runs on the server that tells me if there is a .ldb file for the database. If it does then that tells me that there is an open connection to the database but I don't know where that connection exists or how to get rid of it. I attempt to delete the .ldb file using another scheduled application. If I can delete the .ldb file then I know it was a bad connection and can get rid of it. It's when I can't delete the .ldb file that I've got issues. I've got those at the store (non techs) who tell me that they've closed all the connections but I don't know if I can trust their means of closing it down. The reason I need to close all open connections is that part of the business process is to compact and repair the database nightly. This process fails if there is an open connection thus causing further problems down the road. There are other process that run on a shedule and if one fails they all do in a nice chain reaction. I'd like to ensure that each step runs as it should starting with possible a forced shutdown of each workstation application and then a test from the server to ensure that there are no open connections to the database, valid or not.

Any thoughts or suggestions? If you need more info on what I'm doing then please advise and I'll provide more info on the process I'm dealing with.

Thanks in advance,

 

[SavantMan]

There is a REALLY dirty solution which you may or may not want to consider, and it could be problematic. Instead of forcing the shutdown of the workstations, how about a re-boot of the server just before your process kicks off? If there is anyone connected, they won't be after you re-boot.

 

[HiBoo]

Shutting down the server is unfortunately not an option although it was considered. Also to clarify, I'm not concerned about shutting down the work stations. (although we could do this if necessary) The main concern is to ensure that there are no connections to the database on the server so that the scheduled processes that run at night will work without fail. I don't know from which point to focus? From the server or the workstations or both. If I could figure out if there are connections to the database from the server other then the existance of the .ldb file and then kill those connections then I think I'd be heading in the right direction. I guess though from that point this would have to be checked from the workstation because then again how could one kill a process on a workstation from the server?

 

[Som76]

Hi

Its a temporary solution but might wotrk for ur situation.

When the client makes the connection, keep th client machine name in a Connected_Cleints table. When the client dissconnects, u can remove the name from this table.
So if client workstation is still on or not terminated properly, then u can find the client name on this Table. Then u can take necessary step on that machine.

If this doesn't work for u, then u might require to go for n-tier application, where all ur Data access will be done on Server machine on a gata Layer and it will return the necessary record to the workstation. This scenario will not have any client machine getting connected to Server. On every request to Data layer, data layer will handle the connection and fetch the data & return to workstation through a recordset or variant array.

All the best
Som