Replies

I downloaded the sample Excel workbook on the link you recommended and tried it out. The code only works if I use Excel’s “Window” menu item and select a different Excel file from the list. Whenever I open a workbook by double-clicking its icon in Explorer, or if I just click somewhere else on my screen outside the Excel window, the deactivate event does not fire (nothing happens).

That’s why I thought the best approach would be to trap user click events using the API, but I would still need some sort of timer control to put onto my Excel workbook in order to run the code.

Any other ideas?

Thank you for your input. I’ve tried the approach you recommend, but other Excel files still open with my toolbar. Could this be because my code is in a custom template instead of just a workbook? Would my template become attached to any other Excel files opening when a workbook created with it is running? If so, how would I prevent this? Just to complicate things, the “book.xlt” template is not contained in my Microsoft Templates folder.

I’m mostly an Access programmer, so perhaps I need to understand better how Excel application development works.

Thanks again.

Thank you, Hans, but this approach doesn’t work, because the source workbook gets deactivated when the target workbook is being formatted, which does require the custom toolbar. In addition, when I was playing with it, I found that code to make the toolbar not visible or not enabled on the workbook or window deactivate event did not prevent having my custom toolbar display when a user opened a different Excel workbook by double-clicking its icon in Explorer.

Any other ideas?

Yes, it only tracks the import and update stuff. It then sends me an email with an attached report based on the log table before shutting down on completion. (I have set compact on close to true). The log table is cleared before the next run.

You’re probably right about using XML instead of a table, but I haven’t mastered that.

I haven’t used library databases before; thanks for the suggestion, I’ll look into it. Any heads up info I should know about them?

Also, your comment that Access is “not really DotNet compliant” raises a flag, because one of our production databases is in Access, and the vendor is planning to convert it to DotNet. Where can I find more information about any potential issues?

Thank you so much for your time. I appreciate your help!

Perhaps you could explain in a little more detail.

I would never allow a user to run my backend import and update code, since I need to supervise it., although I do have user security implemented. I also have a log table in the backend to which each function writes a line on completion or, if an error occurs, writes the function name, error number and description.

I’m thinking of leaving it as a front-end back end split design, but was wondering about the possibility of moving my import and update backend code into commponents.

Because the be modules import data into the backend from other production databases and then integrate it with other .mdb tables. I don’t want multiple users’ front-end apps running the backend import code!

Don’t worry, I know how to set up the users.

Can you tell me what the reasons are that “Access is not suitable for 3-tier design”?

Thanks!

Okay, I think we have solved it by installing SP1 for Office 2003. I have tested it on one PC so far, and no problems.

Thanks to all of you for your help.

Okay, I think we have solved it by installing SP1 for Office 2003. I have tested it on one PC so far, and no problems.

Thanks to all of you for your help.

(Edited by HansV to provide link to MSKB article – see Help 19)

I’m suspicious, because I found a Microsoft Knowledge Base Article 836030 that says, tantalyzingly, that the following bug in Access 2002 was fixed in SP3:

“The OpenArgs function only works one time in a VBA project

If your MS VBA code for a form makes a call to the OpenArgs function after you open the form in design mode, yhou may receive the following VBA error message:

Run-time error: 94
Invalid use of Null

No hyperlink to any more info, and my app is a regular .mdb, not a .mda, plus the problem is in a report, not a form, nor does an error message display.
Still, this is Microsoft . . .
Any suggestions as to how to find out more?

(Edited by HansV to provide link to MSKB article – see Help 19)

I’m suspicious, because I found a Microsoft Knowledge Base Article 836030 that says, tantalyzingly, that the following bug in Access 2002 was fixed in SP3:

“The OpenArgs function only works one time in a VBA project

If your MS VBA code for a form makes a call to the OpenArgs function after you open the form in design mode, yhou may receive the following VBA error message:

Run-time error: 94
Invalid use of Null

No hyperlink to any more info, and my app is a regular .mdb, not a .mda, plus the problem is in a report, not a form, nor does an error message display.
Still, this is Microsoft . . .
Any suggestions as to how to find out more?

Just for fun, I made a copy of the report that is reading the openargs string, called it by a different name, and tried opening it with the same docmd method.
Identical report, identical code module, just a different report name.
Got ISNULL for openargs.

Is this an Access 2003 bug?

Just for fun, I made a copy of the report that is reading the openargs string, called it by a different name, and tried opening it with the same docmd method.
Identical report, identical code module, just a different report name.
Got ISNULL for openargs.

Is this an Access 2003 bug?

Yes to both your questions.

The variable name was originally used to pass a querydef, but I found a more effective way to do that. Just never changed the variable name.

Yes to both your questions.

The variable name was originally used to pass a querydef, but I found a more effective way to do that. Just never changed the variable name.

Okay, thanks!