How do you retry connecting to database?

I have an application that connects to a SQL server all day long and retrieves information from it ever 30 seconds, but if that SQL server is ever shut down for any reason then my application crashes at the code below and I have to manually restart it.

Does anyone have any suggestions? I would like to see it go into a reestablish connection mode every 20 seconds until it reconnects, but do not know if this is possible.

Code:

---

Set wFrmRS = wFrmConn.Execute("SELECT Field1 FROM Table1 WHERE Field1 = MyNumericValue")

---

For me I would have the following -

VB Code:

On Error Resume Next
 
Set wFrmRS = wFrmConn.Execute("SELECT Field1 FROM Table1 WHERE Field1 = MyNumericValue") ' If there is no SQL Server available then this line will pass a code to the Error Collection
 
If Err.Number <> 0 Then 
MsgBox Err.Number & " - " & Err.Description ' View error code and description
' Set the timer to 20 Seconds
Else
' Set Timer To 30 Seconds
End If

Hope I understand what you mean and that this is ok.

Matt.

Okay, this gets me to the point of starting a timer.

I guess the idea is to have some code in the timer to restablish the connection?

I am trying this below but it is not working for me in the Timer area. In fact it is having problems with the wFrmConn.Open saying that the Connection is not available (b/c I have the SQL server off while testing this):

Code:

---


wFrmConn.Close
       
wFrmConn.ConnectionString = "Provider=MSDASQL.1;Password=XXXX;Persist Security Info=True;User ID=XXXX;Initial Catalog=XXXX;Data Source=XXXX"
wFrmConn.ConnectionTimeout = 0
wFrmConn.Open

        Do While wFrmConn.State = adStateConnecting
            DoEvents
        Loop

---

Ok I have attached an example, it is by no means perfect.

But I hope it is what you are looking for.

Regards,


Matt.

You will have to change the code to suit your information.

Quote:

---

Originally posted by MattJH
Ok I have attached an example, it is by no means perfect.

But I hope it is what you are looking for.

Regards,


Matt.

You will have to change the code to suit your information.

---

for every 30 sec if the app connect to the sql server do u think the performance will be there...

i think in form load connect and on error set the connection object to nothing and try to connect it again... if still the error then display a message...

My app was a 2 minute put together, and was just for example.

I agree with khalik that the performance would be undermined. Maybe it could check a couple of times and if no success then produce an error message, and stop checking

Maybe the retries could be an option in your program?