Re: ADO Connection Timeout
- From: "frank" <frankjlinden@xxxxxxxxx>
- Date: 30 Dec 2006 12:09:04 -0800
'I'm not that familiar with vb6, having worked mostly with VB.NET.
'I've worked some with VBA and I hope this helps you.
'You can look at the connection state to determine if the connection is
intact before using it.
'The code below should do that and if the current connection is no
longer open,
'it tries the main connection(A) once more and changes to the other
connection(B) if A is still unavailable. It will continue to
'run until the next time the connection is broken. It will switch from
A to B or from B to A.
'I'm probably not using the SET command everywhere I should. That
always gets me in VB
'The syntax may not be exact but I hope it help points you in the right
direction
' I just typed this up quick and I haven't run it. Good Luck. I hope it
helps you more than it
'confuses you.
Public connActive as ADODB.connection
Public strConA as string = "String to connect to Database A"
Public strConB as string = "String to connect to Database B"
'Initial Connection at First Start :
connActive.connectionString = strConA
'Try Opening connection A while catching errors
On Error Resume Next
connActive.open()
Next
If ErrorOccurred Then
conActive = nothing
Error.clear
End If
If ConnActive is nothing then
'Connection A could not be opened so reset it
connActive = new ADODB.connection
'Switch the connection string to Connection B
connActive.connectionString = strConB
End If
'Try to open Connection B
On Error Resume Next
connActive.open()
IF connActive.connectionstate <> adconnectionstate.open Then
connActive = nothing
End IF
Next
If ErrorOccurred Then
conActive = nothing
Error.clear
End If
'If there is still no connection, give up.
If ConnActive is Nothing Then
IF connActive.connectionstate <> adconnectionstate.open
MsgBox("Neither Database is available")
Exit Sub
End If
End If
'Now you are connected to either A or B unless they both are down.
'Now we can leave the connection open.
'Before running SQL transactions, check the connection state, and
include error checking.
'Remember, at this point the connection should already be established.
'If the connection is broken, it will first try to connect to A and
then to B regardless of which one it was last connected to. If
'the connection is still open, the program ignores most of the IFs
'TRANSACTION EVENT:
If not ConnActive is Nothing Then
IF connActive.connectionstate <> adconnectionstate.open Then
'If the connection is not open, reset it
connActive = nothing
End IF
End If
' If the connection is nothing, create it and try connection A first
If ConnActive is nothing then
connActive = new ADODB.connection
connActive.connectionString = strConA
On Error Resume Next
connActive.open()
If not ConnActive is Nothing Then
IF connActive.connectionstate <> adconnectionstate.open
Then
connActive = nothing
End IF
End If
End If
Next
If ErrorOccurred Then
conActive = nothing
Error.clear
End If
If ConnActive is nothing then
'Connection A could not be opened so reset it
connActive = new ADODB.connection
'Switch the connection string to Connection B
connActive.connectionString = strConB
End If
'Try to open Connection B
On Error Resume Next
connActive.open()
IF connActive.connectionstate <> adconnectionstate.open Then
connActive = nothing
End IF
Next
If ErrorOccurred Then
conActive = nothing
Error.clear
End If
If ConnActive is Nothing Then
'If there is still no connection, give up.
IF connActive.connectionstate <> adconnectionstate.open then
MsgBox("Neither Database is available")
Exit Sub
End If
End If
'Finally, Run the SQL
If not ConnActive is Nothing Then
IF connActive.connectionstate = adconnectionstate.open Then
On Error Resume Next
' RUN SQL HERE
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Next
'ERROR HANDING FOR SQL HERE
End IF
End If
'Transaction complete. Leave connection Open for next transaction
Robert McCarter wrote:
Thanks for your reply Bob,
I am using VB6 SP6 with a reference to ADO 2.8. The program that I
currently have in production is checking for the connection to the database
outside of ADO land. I am using some ICMP code (found on
support.microsoft.com site) to ping the server to ensure that the connection
to the server is ok. I am also doing a DIR() statement to verify that my
database exists on the server. The problem I am trying to address is a
performance issue. During a test, I might read or write to the database
10-15 times. Each time I attempt to read or write to the database, I do the
following:
1. Verify Link To Database Is OK with ICMP Ping Test
2. Verify Database Exists On The Server
3. Open Connection To The Database
4. Perform Sql Execute or Recordset Read That Is Required
5. Close My Connection To The Database
I coded my program in this manner such that when I do have a connection
issue, I would not crash my program or even worse halt production. The
particular application is running on a line that loses $38 per second of
downtime. So if my program slows production by 10 seconds, I have cost the
customer $380.
What I am ultimately attempting to accomplish is this:
When the program loads, I want to verify I can connect to the database. I
then want to establish a connection and leave it open. But, I need to
monitor this connection to check whether my database link is severed. This
is why I would like to be able to programmatically capture any connection
errors and handle them accordingly. This whole issue is really a big WHAT IF
(which is the majority of what you have to write your code around), but I
would like to be able to control any problems that may arise.
The problem that I have with the way I am doing it is that Opening and
Closing a connection to a database takes time. Granted in most applications
1-2 seconds is not a lot of time, but in this application it is very critical.
Thank you for any insight you may be able to offer.
--
Thank you,
Robert
"Bob Barrows [MVP]" wrote:
Robert McCarter wrote:
Hello,
I am trying to configure ADO to give me a trappable error when the
connection to my Access database becomes unavailable. I created the
following variables in a module and made them public:
Is this a VB application? Or in an Access VBA module?
Public MSCn as New ADODB.Connection
Public MSCd as New ADODB.Command
Public MSRs as New ADODB.Recordset
In my startup form, I set all of these variables and connect to the
database etc. What I am trying to accomplish is this: When I have a
good connection the remote machine that holds the Access database
(normal operation) everything runs normally. However, if the link to
the remote database is severed (cable unplugged, database damaged, or
some other problem) I want to use a database local to the machine
running the program.
I have tried to set up a test program that connects to the remote
database and then physically unplugging the cable. I cannot get
around the default 30 second timeout. I would like a timeout of 5
seconds to try the main server and if I get an error, swap over to
the local database. When I am running my program, and I unplug the
cable it still takes 30 seconds to get an error. I have tried
ConnectionTimeout on the connection and CommandTimeout on the Command
and had no luck.
Furthermore, when I do get an error, it is one of those messagebox
(Debug, End) errors. I would like to make a global error trap
instead of having to code "on error..." stuff in each sub since I use
the aforementioned variables all over my program. I tried to use
WithEvents when dimensioning my connection and could not get the
InfoMessage Event to fire.
Again ... VB or VBA?
I would appreciate any ideas any of you could give me. This program
is to be run in a production environment on an assembly line that
indexes at 20 seconds per cycle. So, I do not have much time to wait
for timeouts etc. if I have problems.
There is no avoiding this delay if you use a Connection to test the database
existence.
However, if you use a FileSystem object, you should have better success. Set
a Reference to the Windows Scripting Host and try this:
Function DBExists(byval FilePath as String) As Boolean
Dim fso As New FileSystemObject
DBExists = fso.FileExists(FilePath)
End Function
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
.
- References:
- Re: ADO Connection Timeout
- From: Bob Barrows [MVP]
- Re: ADO Connection Timeout
- From: Robert McCarter
- Re: ADO Connection Timeout
- Prev by Date: Re: ADO Connection Timeout
- Previous by thread: Re: ADO Connection Timeout
- Index(es):
Relevant Pages
|
