connection error when network down

Question

I'm running an Access 2007 front-end for a SQL Server 2008 Express database.

I'm using mainly linked tables .

Both the front-end and the server run on the same machine .

When the network connection goes down or is interrupted I get an ODBC connection error.

Even after the network is up I still get the error. I need to close the front-end and open it back up.

1. why do I get this connectivity error when I'm running both on the same machine?
2. is there a way I can manage this connectivity issue? Should I use stored procedures instead of linked tables?

Thank you!

Answer 1

florin_voinescu wrote:

This doesn't make any sense to me as to why the network connection would matter if

both the FE and SQL Server are on the same system

It may be that the connection uses a domain/hostname which needs the network to be solved, even if it is finally solved to be  the same machine (but not 127.0.0.1)... Have some hard time to come with a real case, though.

To connect to the local SQL Server I'm using "(local)\INSTANCENAME" in the ODBC string.

Should I be using something else in order to make this independent from the network fluctuations?

No. (local) is what I'm using to test some things on my own system.  So you should be

quite immune to any network problems.  So I'm quite baffled here.

Tony

Answer 2

 

This doesn't make any sense to me as to why the network connection would matter if

both the FE and SQL Server are on the same system

It *may* be that the connection uses a domain/hostname which needs the network to be solved, even if it is finally solved to be  the same machine (but not 127.0.0.1)... Have some hard time to come with a real case, though.

To connect to the local SQL Server I'm using "(local)\INSTANCENAME" in the ODBC string.

Should I be using something else in order to make this independent from the network fluctuations?

Answer 3

 

This doesn't make any sense to me as to why the network connection would matter if

both the FE and SQL Server are on the same system

It *may* be that the connection uses a domain/hostname which needs the network to be solved, even if it is finally solved to be  the same machine (but not 127.0.0.1)... Have some hard time to come with a real case, though.

Answer 4

florin_voinescu wrote:

I'm running an Access 2007 front-end for a SQL Server 2008 Express database.

I'm using mainly linked tables .

Both the front-end and the server run on the same machine .

When the network connection goes down or is interrupted I get an ODBC connection error.

This doesn't make any sense to me as to why the network connection would matter if

both the FE and SQL Server are on the same system.

Even after the network is up I still get the error. I need to close the front-end and open it back up.

2. is there a way I can manage this connectivity issue? Should I use stored procedures instead of linked tables?

No, this is standard behavior.  Nothing can be done other than exiting Access and

starting it up again.

Tony

Answer 5

Stored procedures won't work any different in this scenario then linked tables.

As far as the way the connectivity works, I can only guess that it uses the network in some manner.

Once connectivity is broken, you have to restart.  At least, I have yet to find a way to re-establish the connectivity without closing the front end application and re-opening it.

---

John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County