ADP lost SQL DB connection

[clt1dpw]

I have an Access ADP project that is connected to a SQL DB in the back end.

I have quite a few cleints using the application(100+ to assorted DBs), but
every so often I have one client that the application locks up on to the
point of having to use Task Manager to close the application.

These few Clients that are having the lost connections problem, I have been
able to trace the problem down to lost packets on there networks.

Since the ADP maintains a constant connection, if a packet is lost, the
application freezes.

Now I have placed a connection tester in the application, so that it tests
the connection every XX seconds. The test involves a simple Select statement
to a small table in the DB. If the application cannot connect, I can capture
the error.

Now the problem is tring to re-establish the connection. I have tried closing
the connection with

Application.CurrentProject.CloseConnection
Application.CurrentProject.OpenConnection strConnection
strConnection is the connection string that connects to the database and
it works because this is the Application connection string that is used to
start the application and stored in memory.

When I try these procedures in either order or by themselves, I get
automation errors and cannot re-connect to the database unless I close the
application or use the File>Connection>Test Connection option(which the
client can not get to).

I would think this would be simple, but I have not found the solution yet.
Any one go any ideas.

Thank you in advance.

 

[Sylvain Lafontaine]

If you have connection problems on a LAN or over the WAN using a VPN, then
maybe you should take a look to using replication over local instance of
MSDE 2000 or SQL-Server Express 2005 if your application is not to big;
otherwise using Terminal Services or Thinsoft might be your ticket.

If these connections problems are over the WAN without using a VPN, then TS
or Thinsoft would be definitely the answer to your problem. If you are
using SQL-Server 2005 Enterprise Edition, using replication over a web
service could also be a possibility for you. Theoritically, it should be
possible to use replication with a SQL-Server 2000 over the web but this
path would be (probably) full of problems.

Finally, MS is in the processing of releasing a new syncing service based on
the .NET Framework, see:

http://www.microsoft.com/downloads/...6E-AAA4-4919-8B3C-1CE4EA1F6552&displaylang=en

 

[clt1dpw]

The connections are made thru TCP/IP. I have only a couple of clients that
this seems to affect. If I run a trace some days I get .1% loss other 1% loss
and still others I get 0% loss. Of couse the clients ISP says this is
acceptable, but as programmers, we know this can cause lots of problems.

Since the ADP has to have a constaint connection, any packet loss can cause
this lockup.

What I need is a way to reset the connection when I detect the break in
communications.

The Idea of a temperary local storage sounds good, but since we are currently
moving to a .Net platform, I think that can be accomplished before a re-write
of the current platform to do the local storage.

Thank you for your response.

Any other Ideas?

If you have connection problems on a LAN or over the WAN using a VPN, then
maybe you should take a look to using replication over local instance of
MSDE 2000 or SQL-Server Express 2005 if your application is not to big;
otherwise using Terminal Services or Thinsoft might be your ticket.

If these connections problems are over the WAN without using a VPN, then TS
or Thinsoft would be definitely the answer to your problem. If you are
using SQL-Server 2005 Enterprise Edition, using replication over a web
service could also be a possibility for you. Theoritically, it should be
possible to use replication with a SQL-Server 2000 over the web but this
path would be (probably) full of problems.

Finally, MS is in the processing of releasing a new syncing service based on
the .NET Framework, see:

http://www.microsoft.com/downloads/...6E-AAA4-4919-8B3C-1CE4EA1F6552&displaylang=en

I have an Access ADP project that is connected to a SQL DB in the back end.

[quoted text clipped - 37 lines]

Thank you in advance.