Re-Establishing an ODBC Connection once it fails

I have an Access Front End with an SQL Server 2005 Backend. The tables are linked using DSN-less connections
Set Td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, DSNConnectionString)
CurrentDb.TableDefs.Append Td

My Program will be used in a tablet environment where the user will primarily use a wireless connection, but may connect their tablet to a LAN in case the wireless has a bad signal or is experiencing problems.

When the connection is switched from LAN to Wireless or Vise-Versa, the Access Linked Tables lose the ODBC connection, and error with ODBC Connection Failed or ODBC Call Failed. Nothing but closing Access and restarting it fixes it once the error shows. I've tried refreshing the TableDefs, Deleting & readding the TableDefs, Relinking Using the Linked Table Manager and nothing works. I've seen a couple promising posts including something about incrementing a DSN number, but I don't think it applies becuase I'm using DSN-less connections.

Is there any way to trap the error & re-establish the ODBC connection without restarting Access?
Who is Participating?
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
Have you tested this in your wireless environment? Reason I ask it that a linked tables architecture across a wireless network can be frustrating to the point of being useless. You're dealing with a server so your mileage may vary, but my testing of a small app over a wireless network quickly proved that I needed to move to fully unbound and connect as needed to handle data. That was about 2 years back, and technology is better but by their very nature wireless networks are prone to drops/reconnects ... this will wreak havoc on your links, at least in my experience.
Try and see if the following tip helps maintain a persistent connection to your linked tables.  If this does not help, then you might want to check if there is an upgraded odbc driver that will maintain a persistent connection.
absoffthewakeAuthor Commented:
As for puppydogbuddy's suggestion, thanks for the reply, but that article applys to linked tables with an access mbd backend. I already have the latest ODBC drivers, and none support switching interfaces.

As for LSMConsulting question, yes wireless is junk if you have the wrong equipment, but if you stick with Cisco, know what you are doing, and match the right antennaes with the terrain, wireless nowaday's is pretty reliable. I do need however to switch back and forth between wireless for reliabiliity, throughput and speed. I need the flexibility of wireless when the user (doctor's) are walking from room to room.

I was hoping to find a way to trap the error, notify the user of the lost connnection, and prompt the user to reconnect.
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

absoffthewakeAuthor Commented:
My suggestion still stands, if you want to try it.  If you read the article carefully, the author states that the Open Database method applies to multiple back end databases, not just access.  Access is frequently used as a front end db, but is rarely used as a back end db in a split FE/BE schema.
absoffthewakeAuthor Commented:
When Access loses the network connection, it seems to place the ODBC driver in some sort of error status, and NOTHING I've tried reestablishes it, short of restarting Access. Your suggestion is not relevant to ODBC. It is for JET, as Jet constantly closes and reopens the database connection if you do not maintain a persistent connection similar to what author suggested method.

I'll reiterate the question.

Is there any way to trap the error & re-establish the ODBC Network connection without restarting Access? It needs to work even if the Network Interace changes. IE: Switching from Wireless to LAN.
see this link and see if has any ideas that you might be able to expand on:
absoffthewakeAuthor Commented:
I saw that article, and it is exactly whats happening to me, but I can't find the DSN number he's talking about. He mentioned "DSN now includes an incrementing number", so I'm thinking he's saying that Access 2007 has that feature? I'm using Access 2003.

My DSN Connection strings are:
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
It doesn't matter what vendor you use for wireless - it's the simple concept of a wireless connect + linked tables + bound Access forms that cause you troubles ... when your wireless loses it's connection - even for a split second - the wireless connection will drop and then reconnect ... this is fine when you're loading a webpage, but not so good when you're querying a database or (even worse) writing to a database. Access simply is NOT reliable enough to use in a wireless network ... it's that simple.

Are you using bound forms? That is, do your form's have a Recordsource, and your Controls each have a Controlsource? If so, you might be able to use the Form's Error event to check this ... I'm not sure what error codes you'd have to trap, but I'm sure you could figure that out.

If you're using unbound, you'd just use non-persistent connections and only write when the user saves the data, only read when the user requests etc etc ... so you'd just fire or test your connection each time.

puppydogbuddyConnect With a Mentor Commented:
The author in the Experts Exchange article says to contact him for the code, so I presume it is application code driven.

Here is MS's response:

This is not a bad thing -- you don't want Access to persist the user
name and password, which it does in clear text, which is a giant
security hole waiting to be exploited by an attacker. Write a
procedure that relinks all of the tables on startup, supplying the
user name and password at runtime. This code can run in the startup
form. You can optionally run code to delete the linked tables when the
application shuts down. To do that, hide the startup form after
logging in and run the de-link code in the Close event.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.