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.

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.

"Julie" wrote:

> 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

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


"Julie" <(E-Mail Removed)> wrote in message
news512F055-02B4-4997-A5DD-(E-Mail Removed)...
>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.
>
> "Julie" wrote:
>
>> 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.

"Jeff Boyce" <(E-Mail Removed)> wrote:

>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/

My bad...

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

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Tony Toews [MVP]" <(E-Mail Removed)> wrote in message
news(E-Mail Removed)...
> "Jeff Boyce" <(E-Mail Removed)> wrote:
>
>>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/

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.

"Jeff Boyce" wrote:

> My bad...
>
> I thought the issue cropped up when Access (FE) tried to talk over the
> airwaves...
>
> Regards
>
> Jeff Boyce
> Microsoft Office/Access MVP
>
>
> "Tony Toews [MVP]" <(E-Mail Removed)> wrote in message
> news(E-Mail Removed)...
> > "Jeff Boyce" <(E-Mail Removed)> wrote:
> >
> >>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" <(E-Mail Removed)> wrote:

>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/

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" wrote:

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

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.

"Julie" wrote:

> 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" wrote:
>
> > 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 <(E-Mail Removed)> wrote:

>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/