Solved

Re-Establishing an ODBC Connection once it fails

Posted on 2008-10-05
11
550 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
  • 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
 

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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

762 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now