"NZ" instruction periodically fails
 

I have a large code library in an XLA that is stored on a network share. Many
of the functions inside make use of the "NZ" function. Periodically, about
once a week, calls to NZ returns "The Remote Machine is not accessable", and
the scripts fail. None of the other hundreds of calls fail.

Has anyone seen this behavior before? Any suggestions on how to avoid it?
It's a long-running process (15 minutes) so when this happens everything has
to be re-started. Adding to the frustration, the dialog indicating a problem
appears _behind_ the application and does not cause the taskbar icon to
flash, so it's all too common to simply miss this problem.

Maury

"NZ" instruction periodically fails
 

Maury,
This is an Access function, no ?
How are you calling it ?

However, the "The Remote Machine is not accessible" error sounds like a
networking problem, albeit intermittent.

NickHK

"Maury Markowitz" wrote in
message ...
I have a large code library in an XLA that is stored on a network share.
Many
of the functions inside make use of the "NZ" function. Periodically, about
once a week, calls to NZ returns "The Remote Machine is not accessable",
and
the scripts fail. None of the other hundreds of calls fail.

Has anyone seen this behavior before? Any suggestions on how to avoid it?
It's a long-running process (15 minutes) so when this happens everything
has
to be re-started. Adding to the frustration, the dialog indicating a
problem
appears _behind_ the application and does not cause the taskbar icon to
flash, so it's all too common to simply miss this problem.

Maury

"NZ" instruction periodically fails
 

"NickHK" wrote:
This is an Access function, no ?
How are you calling it ?

thingy = Nz(rstThingy!aField, "")

Nothing special.

However, the "The Remote Machine is not accessible" error sounds like a
networking problem, albeit intermittent.

I think this is the case, but why would only the Nz function fail? Why not,
say rstThingy.open, for instance?

Maury

"NZ" instruction periodically fails
 

NZ() is not an Excel function so I think you would get a compile error
unless you had checked a reference to the Access Type Library.

rstThingy.open is an ADO/DAO method, so you must also have ADO/DAO checked
as a reference.

Maybe the ADO/DAO type library and dlls are local to the PC (which I would
expect since they basically come with Windows/office these days), but for
some reason the Access type library is on a remote server?

Charles
______________________
Decision Models
FastExcel 2.3 now available
Name Manager 4.0 now available
www.DecisionModels.com

"Maury Markowitz" wrote in
message ...
"NickHK" wrote:
This is an Access function, no ?
How are you calling it ?

thingy = Nz(rstThingy!aField, "")

Nothing special.

However, the "The Remote Machine is not accessible" error sounds like a
networking problem, albeit intermittent.

I think this is the case, but why would only the Nz function fail? Why
not,
say rstThingy.open, for instance?

Maury

"NZ" instruction periodically fails
 

"Charles Williams" wrote:

NZ() is not an Excel function so I think you would get a compile error
unless you had checked a reference to the Access Type Library.

Ok, this does sound interesting. What library would NZ be a part of?

Maury

"NZ" instruction periodically fails
 

The Microsoft Access Object Library

in the VBE look at Tools--References for this project and select the
Microsoft Access Object Library reference: you should then be able to see
its location at the bottom of the References window.


--
Charles
______________________
Decision Models
FastExcel 2.3 now available
Name Manager 4.0 now available
www.DecisionModels.com

"Maury Markowitz" wrote in
message ...
"Charles Williams" wrote:

NZ() is not an Excel function so I think you would get a compile error
unless you had checked a reference to the Access Type Library.

Ok, this does sound interesting. What library would NZ be a part of?

Maury

"NZ" instruction periodically fails
 

"Charles Williams" wrote:

The Microsoft Access Object Library

Well that's confusing then, because Access IS installed on everyone's
machine. In fact, most of the actions that eventually call the code using the
Nz's is fired up from an Access app!

Maury

"NZ" instruction periodically fails
 

"...most..."

any chance that you're getting the error only when a non-Access app is
triggering the code?




"Maury Markowitz" wrote in
message ...
"Charles Williams" wrote:

The Microsoft Access Object Library

Well that's confusing then, because Access IS installed on everyone's
machine. In fact, most of the actions that eventually call the code using
the
Nz's is fired up from an Access app!

Maury

"NZ" instruction periodically fails
 

"George Nicholson" wrote:
any chance that you're getting the error only when a non-Access app is
triggering the code?

Maybe, but the code itself is inside Excel and Access is linked.

Maybe the best solution here is to re-write Nz into VBA code in the excel
lib? Any suggestions along that line?

Maury