Solved

Re-Establishing an ODBC Connection once it fails

Posted on 2008-10-05
11
572 Views
Last Modified: 2009-05-29
I have an Access Front End with an SQL Server 2005 Backend. The tables are linked using DSN-less connections
IE:
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?
0
Comment
Question by:absoffthewake
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
11 Comments
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 22643909
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.

http://www.fmsinc.com/free/NewTips/Access/LinkedDatabase.asp
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 336 total points
ID: 22644620
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.
0
 

Author Comment

by:absoffthewake
ID: 22646845
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.
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:absoffthewake
ID: 22752486
Anybody??
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 22752548
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.
0
 

Author Comment

by:absoffthewake
ID: 22758549
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.
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 22758957
see this link and see if has any ideas that you might be able to expand on:
                 http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_DB/Q_23487981.html
0
 

Author Comment

by:absoffthewake
ID: 22760001
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:
ODBC;DRIVER=SQL Server;SERVER=10.10.1.20;UID=USER;PWD=PASSWORD;APP=Microsoft Office 2003;WSID=XTECHTABLET;DATABASE=DatabaseName
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 336 total points
ID: 22760056
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.

0
 
LVL 38

Assisted Solution

by:puppydogbuddy
puppydogbuddy earned 168 total points
ID: 22760787
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:
http://groups.google.com/group/microsoft.public.data.odbc/browse_thread/thread/2cbfdae50a1e29b0

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.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question