"NZ" instruction periodically fails

[Maury Markowitz]

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

 

[NickHK]

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]

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

 

[Charles Williams]

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]

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

 

[Charles Williams]

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]

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

 

[George Nicholson]

"...most..."

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

 

[Maury Markowitz]

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