Batch process gets undefined function (nz) error 1

[mdthornton]

I have a public sub ("compileReportData&quot defined in one of my database modules. compileReportData does all of the applications core data manipulation and generates results for reports etc. I have a button on my switchboard which calls compileReportData and this works fine.

I'm trying now to develop a batch process which will run overnight so that users in London can receive a feed of the data without having to wait for us to get into the office.
I'm experimenting with starting the database up with a
command parameter, eg:

msaccess.exe mydb.mdb /command batch

and in the open event for the main form just do something like:

Private Sub Form_Open(Cancel As Integer)
InitDb
If Command() = "batch" Then
compileReportData
Application.Quit
End If
End Sub

This seems to be on the right lines and the first few lines of compileReportData execute but I get an "undefined function (nz) in expression" error when I try to execute a query which references the nz function. I've seen other threads in this forum where the solution is that the DAO library is not in the references table but this is not the
case here. I've tried moving the code above to different events eg onload, oncurrent but it makes no difference. Whats more curious is that if, when the error occurs, I halt the code, switch the form to design view and then reopen it everything works fine, ie compileReportData executes fully and the application exits.

I'm clutching at straws but is there a way to preload the referenced libraries? Anyone got any ideas or seen anything similar?

Thanks in advance,
Mike T

 

[MangroBongacello]

It's weird but I had a similar problem and solved it by first telling from which object to use the function, in your case

application.nz

Kind of a straw though...

 

[Mossoft]

Try forcing the code to use NZ and hence loading the necessary libs. e.g.
Private Sub Form_Open(Cancel As Integer)

Dim strDummy as string

InitDb
strDummy = NZ("Test","&quot
If Command() = "batch" Then
compileReportData
Application.Quit
End If
End Sub

I had a similar problem with the dbEngine not picking up the systemDB from the command line and had to put in a line: "strSystemDB = DBEngine.SystemDB" at the beginnning of the first form load event.

HTH

M

 

[mdthornton]

Thanks for the suggestions but unfortunately I still have the problem.

 

[ErikZ]

How about running the code from a macro instead? Access does support running a macro from the command line. Then just have the macro call your code module.

 

[mdthornton]

Hi ErikZ

Thanks for the suggestion - this actually works!
I created an "autoexec" macro which just runs a function
which in turn decides whether to run in batch or, if not, open the main switchboard. For whatever reason, this
extra layer fixes my "undefined function" problem.

There's obviously something going on behind the scenes which is completed via the macro route but not the way
I was trying. Just another Access quirk to deal with.

Thanks again,
Mike T

 

[ErikZ]

So where's my star?