Client/Server Reliability

[bjd4jc]

I am working on a critical application that needs a reliable database connection.

It is a Clock In/Out application that runs simultaneously on about 15 computers throughout the manufacturing plant and offices. It connects to an Access database out on a server. If the server goes down, currently we have to go out to all 15 computers and restart the application for it to connect to the database again. I am using ADO 2.6 to connect to this database. What I am wanting to happen is for the program to use the normal table when all is well, but when disaster happens, it would use another database so we don't lose any information. And, when the normal database comes back up, the connection to the backup one is dropped and the normal restored and then the data in the backup database would be pushed over to the normal database. Does anyone have some tried and tested code to do this?

 

[Foada]

I have done an app that did do something similar to what you are talking about. The major concern I see with your situation depends on if you need to have any editing of your server database while the server is down. If so, the whole thing gets real messy trying to merge all the edited data back to the server when the server comes back on line. If all you need to do is have your clients confirm that the server is online and allow editing if it is or prevent editing and only allow reading if it is off line it is not too bad. If you can answer some of these point I may be able to point you in a direction. Anything is possible, the problem is I only have one lifetime.

 

[bjd4jc]

There is no editing of records. That process isn't as critical so if the server is down, then the editing portion of the system is down and that's OK. We will need to be able to keep loggin Clock IN/OUT records. They will just then need to be added to the correct database when it is back online. They don't depend on the matching of any other records.

Thanks

Brian

 

[Foada]

Brian,

Do you have a server app too or does the server just provide a central location for the database? Anything is possible, the problem is I only have one lifetime.

 

[bjd4jc]

There is no server app. The server just provides a central location for the file to reside on. Each time the app runs, it connects to the database on the server so there could be multiple connections to this database.

 

[Foada]

Ok bjd4jc here is what I did (not to say that it is the best or only way). I used two global variables to assign the handle to the connections one to the server db and one to a local db for the temp table.

Public hServerDB As New ADODB.Connection
Public hLocalDB As New ADODB.Connection
Private Const sDBConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info = False;" & _
"Data Source = "


Then when my app started I connected to both the server and the temp db

hServerDB.ConnectionString = sDBConnection & "\ServerDB.mdb"
hServerDB.Mode = adModeReadWrite

hLocalDB.ConnectionString = sDBConnection & "\LocalDB.mdb"
hLocalDB.Mode = adModeRead


I don't know how busy your network is and this is where it can get messy, but I just set my connection timeout property for my server to the time I wanted to use as "server failed" time. I trapped my errors when I attempted to access the server and wrote my code to use the hServerDB if I could otherwise I used the hLocalDB, which ran a While loop to test the hServerDB connection periodically. Once I could connect to the server again I switched back to the hServerDB handle for all my db code. I hope you can follow what I did, and like I said there may be a better way. Let me know if this helps. Good Luck Anything is possible, the problem is I only have one lifetime.

 

[PhilMatAmber]

It's not exactly what you were looking for, but why not use a client-side, batch update, disconnected ADO cursor to make your updates. Use a timer to keep reconnecting to update. If you can't reconnect, the server is down, oh well, try again next timer interval. Has the advantage of keeping no connections open. Connections go into the pool so they re-open quickly anyhow.
I don't think theres any way to connect to a different db to do the update, but you could try. But then you have the problem of the re-sync of the backup with the master.