Re: ADO Connection Timeout

Tech-Archive recommends: Fix windows errors by optimizing your registry

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"


.

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: 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)
  • Re: DateCreated
    ... > Access database. ... you haven't indicated what breaks or what error you get when you change the connection string and any code ...
    (microsoft.public.vb.database.ado)
  • Re: ADO Connection Timeout
    ... When the first test is run, the results are stored in the central database. ... to the central server, but you are willing to live with periods where it ... i.e. a local database or even a text file. ... to function until the connection can be restored to the server. ...
    (microsoft.public.data.ado)