What if the SQL server goes down - Trapping Errors?

[llefebure]

Greetings everyone.

My situation:
I am in the design stage of building an app that I plan to use both MS Access and SQL MSDE with. I would like to build it in the best way to prevent data loss. I plan to use a central SQL database to store all data. The clients will be running a VB app that can read and write data from the central SQL database. If by chance the network connection goes down or the server gets restarted, I would like the app to be able to keep running, just writing the data to an Access database on the local C: drive instead. The user just gets an alert that the connection to the SQL server has been lost. They can continue to work though. When the link comes back up, the data in the access database can be synchronized to the SQL server. This synchronization can be initiated by the user.


My questions:
1: Is it best to use ON ERROR RESUME NEXT and then trap on the error messages? Is there a better way to know if the data made it to the server?

2: Say the user is writing data to SQL and the network connection drops in the middle of it. Some data has already been sent to SQL, but not all of it. Does SQL write what it has already gotten or drop it all since the connection hasn't been completed? Can I trap this with an err.number?

3: Are there any other suggestions to make this simple, faster, or easier?

Thanks,
Lance

 

[cheerio]

You can create a pass through query in Access that is sent to the server and executed there. eg update payroll set salary =1.1*salary where firstname = 'Ken'. Because of the way SQL server works either everyone named ken gets a rise or the update fails and no one gets the rise.

You can use transactions. Here you have as a logical structure:
BEGIN TRANSACTION
various updates
if no errors arose COMMIT TRANSACTION else ROLLBACK TRANSACTION

Here if you lose connection with the server the COMMIT command is never given so the updates do not occur.

If you know that you have a poor network connection then you could always work in the local Access copy of the data and then update the server at set periods. That way you are only vulnerable during the short update period rather tham for the whole session.

Having said all this in the last 3 years I think I have lost a connection once or twice so you need to weigh up the cost of increased complexity in your code (with the risk of coding errors) against the risk of a broken connection.

Ken