Re: ADO Connection Timeout

Mark,

Thank you for your reply. The only reason I am using Jet/Access is because
it is the only means I have available at the current time. Trust me, I would
much rather write stored procedures and use server side cursors. But I do
not have a SQL server available at this time. I plan to propose adding a SQL
server for the job as soon as I get the installation phase completed.
However, that being said, I will still find myself up against corporate IT
policies etc. I have full control over the Jet/Access infrastructure. If I
have to play IT politics getting a SQL server installed, I will not even
worry about trying to implement that solution. Like Sean Connery said in
"The Untouchables" I won't be stupid enough to show up to a gunfight with a
knife.

At any rate thank you for your comments. I am trying to come up with the
best solution for the technology I have in place. In regards to using the
local database, it is vital that I get the information to a central server.
There are 5 different machines running this test program that need to read
the data from all tests. The only reason I have the local database in place
is to allow the program to continue to function until the connection can be
restored to the server. Once the connection is restored, the tables will be
immediately synchronized. In the interim, all sorts of bells and whistles
will be going off to alert someone that there is a problem.
--
Thank you,

Robert


"Mark McGinty" wrote:


"Robert McCarter" <RobertMcCarter@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:FC2DADD5-B384-470D-931E-DBB91E8CCDA8@xxxxxxxxxxxxxxxx
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.

Forgive my candor, but if that's the case, why in hell are you using
Jet/Access as the db engine?

In any case, this is obviously not an OLTP application, if a local db is
acceptable for interim periods, so you might want to re-think your
architecture -- like maybe always writing the data to a local db regardless,
and using a second process to transfer the data in chunks when the
connection to the central data store [I can't, in good conscience, to refer
to Jet as a "server"] is available.

That would give you the fastest and most reliable connection possible by
far, [and would be most free from contention issues as well] to service your
real-time data acquisition needs. The central data store will be as current
as the interval for the background process (plus any connection downtime, of
course), and the background process will be in a position to take its time
with the transfers, without costing anyone any time/money.

That you're ever able to consider use of a local database necessarily means
that getting the data to the central store is not important to the data
acquisition process, so off-loading that responsibility to another process
should give you the best of all worlds... Of course, whether or not that's
entirely practical, only you can say...

But whatever you do, my advise would be to *seriously* consider the wisdom
of basing the integrity of an important activity, on something that's widely
known to be flakey and replete with inadequacies, as is Jet. IMHO, using
Jet as the cornerstone for something that could cost someone $38/second
*when* [that's right, not "if" but "when"] it nuts-up due to its many
inherent flaws, is effectively *begging* for grief.

-Mark




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"






.

Relevant Pages

  • Complicated Connection Problems bewteen ADP and SQL Server
    ... This database ... expertise for getting the user workstations talking to the SQL Server. ... connection would fail and the adp wouldn't be able to talk to the server. ... might be in my ADO connection string. ...
    (microsoft.public.access.adp.sqlserver)
  • Complicated Connection Problem between ADP and SQL Server
    ... This database ... expertise for getting the user workstations talking to the SQL Server. ... connection would fail and the adp wouldn't be able to talk to the server. ... might be in my ADO connection string. ...
    (microsoft.public.sqlserver.connect)
  • 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: Database/WebAccess
    ... Regarding your synchronisation question of the local database with the ... remote internet database question. ... With SQL server you can define a Linked Server and write the necesssary ...
    (microsoft.public.backoffice.smallbiz2000)
  • 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)