ODBC Linked Tables Losses Connection

[gtung99]

Hello all,

I'm a new member hoping to get some help. I just took over a new project to revive Access reporting and there is a weird error i'm seeing.

Our master database has linked tables with ODBC to our SQL database and linked tables to another access database. On the computer i'm running this from a System DSN data source has been created to connect to our SQL database.

The problem I'm having is when I run Macros to kick off Queries, there seems to be some kind of timeout issue and its not consistant. Sometimes in Access after a hour or two, the ODBC links somehow just fails. I have to go up to the menu and go to 'Get External Data' and then 'Link Tables' and relink the ODBC DSN connection. I make sure to hit save password and the end but that doesn't seem to do much. This also happens sometimes when I close and reopen the database.

But it seems erratic, sometimes it can stay up for hours but sometimes just a few mins. Sometimes I can close the Access database and reopen without losing connection. Being so erratic, this makes it very hard for me to run batch files nightly to kick off Macros.

I need help! Is there some kind of inactivty timer for DSNs? Or maybe with the SQL database itself? Or is the way i'm restablishing the ODBC connection wrong ?

 

[mdlueck]

I use this solution to put the passwords into Linked Table objects:

Linked Table Saved Password ADOX code
http://www.access-programmers.co.uk/forums/showthread.php?t=226468#post1155700

I must call this solution when applications start up, and the Linked Table objects retain the credentials for the entire time the application is running.

Now, if you opened one of those Linked Table objects for a long period of time, and then suddenly the connection drops... that I can not speak to. Each time the Linked Table object is opened, it uses the supplied credentials to authenticate the connection. So in this case, if a Linked Table object times out, simply close it and reopen it, and that should work.

 

[Pat Hartman]

Welcome Aboard
The problem is with your network. Wireless networks are much more prone to blips than wired networks and Access should not be used to link to Jet/ACE databases on a wireless network although SQL Server (and other RDBMS) is less of a problem but only because the disconnection is unlikely to corrupt the RDBMS where Jet and ACE are much more prone to corruption caused by these network disruptions.

Add some logging code into the application so it logs start and end times for queries and also logs errors. Once you have a body of proof of the disconnections, you can take the information to your network group and have them investigate. Being able to document actual instances will allow them to review their own logs to look for errors.

 

[gtung99]

I'm sorry, where did you get that I was using a wireless network? I am not, everything is wired.

And in terms of verifying the ODBC connection breaking, I get an error message saying "ODBC - connection to ' <database> ' failed." After relinking the link tables thru the file menu, this message goes away.