Communication link failure

[Julie]

We are having some ODBC errors on a database that is Access 2000 FE and SQL
2005 BE. The only PC's that are consistently having an issue are ones that
connect to the LAN via wireless. The most common error is [Microsoft][ODBC
SQL Server Driver]Communication link failure (#0) . Short of a reboot of the
PC or a relaunch of the Access database, is there a way to recover from this?
I do have a retry count of 10 in place in a few locations, but even after
the retry we are getting the errors. I am doing a dynamic dns-less relink at
the launch of the app.

 

[Julie]

I thought you might be interested in an update. I have been testing losing
network connectivity. It does appear that shutting down the Access database
and relaunching does work. So, my users shouldn't need to go to the extreme
of rebooting unless they're still having issues with the wireless. However,
I'm still looking to see if it is possible to prevent application shut down.
Here's what I've come up with so far.

I came across some documentation in a book that we have that talks about
ODBC settings in the Registry. It seems that there is a key
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\ODBC\ConnectionTimeout
that is set at a default of 600 seconds (10 minutes). On an activity in my
database that uses ADO, if I wait the prescribed 10 minutes following loss of
network connectivity I am able to perform the action. I could bump this
down. However, this doesn't seem to resolve all of my problems.

If I try my dynamic relink routine before the 10 minutes has elapsed I get
the following error (3011 The Microsoft Jet database engine could not fine
the object 'MSysAccounts'. Make sure the object exists and that you spell
its name and the path name correctly.). BTW... This is a secured database
and the *.mdw file does reside in a network folder. After the 10 minutes has
elapsed, I can successfully run the dynamic relink which drops the tables and
tries to recreate the links. However, running the relink doesn't bring the
db back fully and seems to not change anything. If I try to access any of
the linked tables I get the following error (Reserved error (-1104); there is
no message for this error.). I suppose if my application were fully unbound,
the registry setting would suffice. However this is not the case. I have
also tried using CurrentProject.CloseConnection, etc. with no luck.
Apparently, this can not be used while the database is open. I'm still
digging.

 

[Jeff Boyce]

Julie

If you are working with wireless and Access, I suspect the issue has less to
do with timeout and more to do with, let's call it "latency" -- the amount
of time between when Access "asks" and the back-end responds.

You can search on-line and find several discussions (and strong
recommendations against) using an Access Front-End/Back-End design over a
wireless network.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I thought you might be interested in an update. I have been testing losing
network connectivity. It does appear that shutting down the Access
database
and relaunching does work. So, my users shouldn't need to go to the
extreme
of rebooting unless they're still having issues with the wireless.
However,
I'm still looking to see if it is possible to prevent application shut
down.
Here's what I've come up with so far.

I came across some documentation in a book that we have that talks about
ODBC settings in the Registry. It seems that there is a key
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\ODBC\ConnectionTimeout
that is set at a default of 600 seconds (10 minutes). On an activity in
my
database that uses ADO, if I wait the prescribed 10 minutes following loss
of
network connectivity I am able to perform the action. I could bump this
down. However, this doesn't seem to resolve all of my problems.

If I try my dynamic relink routine before the 10 minutes has elapsed I get
the following error (3011 The Microsoft Jet database engine could not fine
the object 'MSysAccounts'. Make sure the object exists and that you spell
its name and the path name correctly.). BTW... This is a secured database
and the *.mdw file does reside in a network folder. After the 10 minutes
has
elapsed, I can successfully run the dynamic relink which drops the tables
and
tries to recreate the links. However, running the relink doesn't bring
the
db back fully and seems to not change anything. If I try to access any of
the linked tables I get the following error (Reserved error (-1104); there
is
no message for this error.). I suppose if my application were fully
unbound,
the registry setting would suffice. However this is not the case. I have
also tried using CurrentProject.CloseConnection, etc. with no luck.
Apparently, this can not be used while the database is open. I'm still
digging.

We are having some ODBC errors on a database that is Access 2000 FE and
SQL
2005 BE. The only PC's that are consistently having an issue are ones
that
connect to the LAN via wireless. The most common error is
[Microsoft][ODBC
SQL Server Driver]Communication link failure (#0) . Short of a reboot of
the
PC or a relaunch of the Access database, is there a way to recover from
this?
I do have a retry count of 10 in place in a few locations, but even
after
the retry we are getting the errors. I am doing a dynamic dns-less
relink at
the launch of the app.

 

[Tony Toews [MVP]]

If you are working with wireless and Access, I suspect the issue has less to
do with timeout and more to do with, let's call it "latency" -- the amount
of time between when Access "asks" and the back-end responds.

You can search on-line and find several discussions (and strong
recommendations against) using an Access Front-End/Back-End design over a
wireless network.

Jeff

She specifically states SQL Server and ODBC. An Access back end is
not involved in this case.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Jeff Boyce]

My bad...

I thought the issue cropped up when Access (FE) tried to talk over the
airwaves...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Julie]

It does, but the backend is SQL 2005. I agree that wireless if probably not
the best choice for this architecture, but that was not my call. Plus in our
environment it happens to be the most practical.

 

[Tony Toews [MVP]]

I thought the issue cropped up when Access (FE) tried to talk over the
airwaves...

Correct. I'm also interested in this topic too as this may be useful
for some of my clients as well.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Julie]

Here's some other interesting tidbits I have come across. The affected
objects seem to be named objects including local tables. Meaning, I can run
a report where my recordsource is using report defined sql query or set the
recordsource in code. However, if I run a report where my recordsource is a
named query, I get the Reserved Error (-1104) error. Same thing with forms.
If I use unnamed queries for combo box record sources and have an unbound
form all is ok. I also get this error when DoCmd.RunSQL is used. However, I
do seem to have fairly reliable access to the data through code and sql
queries.

Also, if I try to access the security model through code to test whether
users have permissions to a resource, then I get the (3011 The Microsoft Jet
database engine could not find the object 'MSysAccounts'. Make sure the
object exists and that you spell its name and the path name correctly.)
error.

 

[Julie]

Ok. I think I have found the solution to the remainder of this issue. As I
mentioned before, this is a secured database. We were using a *.mdw file
that is located on a network drive. Apparently, Access 2000 doesn't refresh
that connection following a network hicup. I tested using a local copy of
the *.mdw file and was able to recover. It wasn't immediate, but it was
fairly quick. I don't know if later versionsof Access would recover from
this or not. I would be interested to know.

Here's some other interesting tidbits I have come across. The affected
objects seem to be named objects including local tables. Meaning, I can run
a report where my recordsource is using report defined sql query or set the
recordsource in code. However, if I run a report where my recordsource is a
named query, I get the Reserved Error (-1104) error. Same thing with forms.
If I use unnamed queries for combo box record sources and have an unbound
form all is ok. I also get this error when DoCmd.RunSQL is used. However, I
do seem to have fairly reliable access to the data through code and sql
queries.

Also, if I try to access the security model through code to test whether
users have permissions to a resource, then I get the (3011 The Microsoft Jet
database engine could not find the object 'MSysAccounts'. Make sure the
object exists and that you spell its name and the path name correctly.)
error.

We are having some ODBC errors on a database that is Access 2000 FE and SQL
2005 BE. The only PC's that are consistently having an issue are ones that
connect to the LAN via wireless. The most common error is [Microsoft][ODBC
SQL Server Driver]Communication link failure (#0) . Short of a reboot of the
PC or a relaunch of the Access database, is there a way to recover from this?
I do have a retry count of 10 in place in a few locations, but even after
the retry we are getting the errors. I am doing a dynamic dns-less relink at
the launch of the app.

 

[Tony Toews [MVP]]

I also get this error when DoCmd.RunSQL is used.

The problem with DoCmd.RunSQ is that it ignores any errors. Either
of the following will display any error messages received by the
query. If using DAO, use Currentdb.Execute strSQL,dbfailonerror..
For ADO use CurrentProject.Connection.Execute strCommand,
lngRecordsAffected, adCmdText You can then remove the
docmd.setwarnings lines.

If you're going to use docmd.setwarnings make very sure you put the
True statement in any error handling code as well. Otherwise weird
things may happen later on especially while you are working on the
app. For example you will no longer get the "Do you wish to save your
changes" message if you close an object. This may mean that unwanted
changes, deletions or additions will be saved to your MDB.

Also performance can be significantly different between the two
methods. One posting stated currentdb.execute took two seconds while
docmd.runsql took eight seconds. As always YMMV.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

Ok. I think I have found the solution to the remainder of this issue. As I
mentioned before, this is a secured database. We were using a *.mdw file
that is located on a network drive. Apparently, Access 2000 doesn't refresh
that connection following a network hicup. I tested using a local copy of
the *.mdw file and was able to recover. It wasn't immediate, but it was
fairly quick. I don't know if later versionsof Access would recover from
this or not. I would be interested to know.

Excellent news. <sarcasm>Glad we could be of help</sarcasm> I'm
laughing at us here and not you with that comment.

When I have a moment I'll write a web page with this problem and
solution.

Now note that you may want to use the Auto FE Updater or your own
method to ensure the user pulls down the latest copy of the MDW if
there are any updates made to it.

See the free Auto FE Updater utility at
http://www.granite.ab.ca/access/autofe.htm at my website to keep the
FE on each PC up to date.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Julie]

I've already included a file copy of the *.mdw to the local machines in my
launch batch file and modified the ini file for Start.mdb to use the local
copy. Anyway, after giving volumous amounts of blood to this issue, I'm
relieved that a resolution is possible. Hey, you guys can't know it all.
After all of my experience, I still find I learn something new just about
every day. That's what keeps this line of work interesting.

 

[Tony Toews [MVP]]

Hey, you guys can't know it all.
After all of my experience, I still find I learn something new just about
every day. That's what keeps this line of work interesting.

Absolutely.

BTW please post back in a while with your experiences in use wireless
with SQL Server. I, for one, would like to hear about real world
experiences. And your postings and emails are coherent so you seem
like a reasonably competent person.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Julie]

Thanks for the compliment. I'll try to remember to keep the blog updated.

 

[David W. Fenton]

The problem with DoCmd.RunSQ is that it ignores any errors.
Either of the following will display any error messages received
by the query. If using DAO, use Currentdb.Execute
strSQL,dbfailonerror..

But be sure to have an error handler, or it will be a rather rude
result for your users.