Re: ADO Connection Timeout

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Robert wrote on Fri, 29 Dec 2006 12:16:01 -0800:

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:

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.

Set the ConnectionTimeout on the Connection object before using the Open
method to open the connection - this works fine for me here.

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.

Use On Error Goto ... in your startup Sub or Form, with an error handler at
the end of that sub, it will remain in use throughout the application and
you can test for the actual error code to determine if it's a Jet error or
not.

But I have to echo the other comments you had - this really needs to be
moved to SQL. Even SQL Server Express edition is much better than risking
Jet, and it's free. All of my old Jet applications have now been migrated to
SQL Server (I have a mix of versions 7 Standard, 2000 Standard, and 2005
Workgroup), and they are much much more reliable than they ever were using
Jet.

Dan


.

Relevant Pages

  • Re: ADO Connection Timeout
    ... so what happens when a connection failure forces one station to revert ... to a local database? ... Further, you *will* have contention issues, Jet does not support record ... to the central server, but you are willing to live with periods where it ...
    (microsoft.public.data.ado)
  • Re: trying to automate a process we perform manually
    ... loan files) that's about 220mb large. ... I then rename it to a text file and import it into an Access database. ... connection to your data source. ...
    (microsoft.public.sqlserver.dts)
  • Re: Closing Jet Database seems unreliable
    ... Could it be that JET has not finished writing the cache to the database? ... and the connection ... I want to do this so that my app can backup or restore the ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Syntax
    ... The connection string format is not valid. ... connection string with zero components is set on database connection manager. ... Access database connection string, and not the Expression issue you're ...
    (microsoft.public.sqlserver.dts)
  • ANN: Sequel 3.11.0 Released
    ... Sequel is a lightweight database access toolkit for Ruby. ... Sequel provides thread safety, connection pooling and a concise DSL ... for constructing database queries and table schemas. ...
    (comp.lang.ruby)