Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Modules _ Autoexec V. Manual Code Execution

Posted by: SerranoG Oct 14 2019, 01:54 PM

I have automated some databases to fire off using Windows scheduled tasks for when I go on vacation, some reports can be generated by my computer running on "auto-pilot." Recently, I've been encountering some odd behavior.

If I open the ACCDB without firing off Autoexec (SHIFT + Double-Click the ACCDB), manually find the module, manually run the module: it creates three PDFs based on three reports and puts the PDFs in the correct shared directory. The database closes. It works great.

If I let the autoexec run (by double-clicking the ACCDB; that is, mimic a scheduled task), the same module is called via a function in a "Run Code" statement, the module creates 1 of 3 reports, fails to create the other reports, fails to put any PDFs to the shared directory. Access thinks it's successful, and it closes. No error messages whatsoever.

Why are all three reports generated when I do everything manually, but fails (with no error messages) when Autoexec tries to do the same thing? The frustrating part is that this ability to do it on autopilot has been working flawlessly for over a year. Now all of a sudden it's doing this odd behavior.





Posted by: theDBguy Oct 14 2019, 01:57 PM

Hi Greg. Sadly, when something used to work but suddenly doesn't, I tend to blame any configuration changes on the machine, such as a recent update. Do you know if anything has changed with the environment recently?

Posted by: cheekybuddha Oct 15 2019, 03:59 AM

Just thinking out loud, might the scheduled task run as a user with lower/different privileges than you have?

Posted by: jleach Oct 15 2019, 08:01 PM

Running Access databases via the scheduler has always been somewhat of a crapshoot to me, I tend to avoid it.

That said, without being able to put a breakpoint into things on account of you letting it run by itself, perhaps you can put a few print statements in select portions of the code and see which ones are being hit. Maybe throw one at the start of each major function called: when the process is done and appears complete, crack open the VBE and look at the immediate window to see which debug statements "made it", which will allow you to narrow in on potential problem areas.

If that doesn't work (difficulty viewing in immediate window, no apparent issues, etc), I'd try switching the print statements to a file log, and check that way, which is a bit more "hands off"

If I had to take a complete shot in the dark, I'd guess at some potential file IO issue: is Access closing automatically when the function is done but prior to when the file outputs are written to disk (as one example)?

Posted by: SerranoG Oct 22 2019, 03:05 PM

Our IT dept. is notorious for pushing out Windows and Office updates to our computers only to have those updates break previous fixes or causing unusual behaviors, so it's very possible.

So right now, if the task scheduler runs the dB or I double-click it off the File Manager, the AutoExec fails at my RunCode command with error 2001. If I hold the SHIFT key and open the database via File Manager (thereby preventing AutoExec from firing) and then manually run AutoExec, everything runs perfectly.





Posted by: dmhzx Oct 22 2019, 06:17 PM

Personally I would never use autoexc to do that.

I would set up a macro with the runcode, give the macro a name, and then use the scheduler with the /X in the command line. - You could rename the autoexc macro and use /X

I've done that for years with no problems at all.

It leaves you in complete control, and you can test the process in exactly the same way that that access will from the scheduler.

Your problem could be to do with the fact that autoexec is not running exactly the same as what you do manually. - Something to with the 'different' ways you open the database.

Or it could be that your database need to be decompiled. and/or compacted/repaired: This is often the cause when something stops working without warning.

Posted by: SerranoG Oct 24 2019, 06:47 AM

QUOTE
I would set up a macro with the runcode, give the macro a name, and then use the scheduler with the /X in the command line. - You could rename the autoexc macro and use /X


I have not really used DOS commands with switches much so I looked up the /X switch. It says "uninstalls a product." That makes no sense to me in this context, so I obviously do not understand to what the /X applies to here. Can you elaborate? Thanks.



Posted by: cheekybuddha Oct 24 2019, 07:13 AM

Greg, it's an Access command line switch here.

See this http://www.UtterAccess.com/wiki/index.php?title=Command-Line_Switches&diff=2032&oldid=2028 (Scroll down)

So, the command for your scheduled task would look like:

CODE
C:\FullPathTo\MSACCESS.EXE C:\FullPathTo\Database.accdb /x NameOfMacro

Posted by: dmhzx Oct 24 2019, 11:08 AM

As Cheeky Buddha says, the /X is an access command line parameter, where you specify what Macro you want to run on start up.

I have has much success with scheduling that, and , as opposed to autoexec, you can test it completely.
OPen up the database without any pre-amble, and kick of the macro to see exactly what it will do if the windows task scheduler runs it

I hope this isn't a 'granny/eggs point, but the
Macro would need a 'runcode " line
The actual code must be a "Function" even if it doesn't' return anything
Put the 'quit' command in the macro, NOT the code.

Hope that helps.
I normally give the name of the macro a prefix of "sched" (no prizes for guessing why wink.gif )


Posted by: SerranoG Oct 25 2019, 08:24 AM

I did everything Cheeky and you suggested. I ran the command with the /x switch, the database fired off, it started the macro (now named something else, not AutoExec), and I got the same error code: 2001. It's very strange. I have other databases that fire off in a similar manner that run other reports in the same fashion and they all work just fine. It's just this one that has decided to act up. Other things I have done include decompiling the database + compact & repair AND create a new database & copy everything over. Results? Same... error. It will ONLY run if I run it manually. Any attempt to make it go on autopilot fails.





Posted by: cheekybuddha Oct 25 2019, 08:28 AM

Might any of the https://social.msdn.microsoft.com/Forums/en-US/a11fc864-b74f-4329-8e79-c74e9a145d98/runtime-error-2001 apply to your situation, Greg?

Posted by: cheekybuddha Oct 25 2019, 08:31 AM

Also, perhaps https://stackoverflow.com/questions/47457648/macro-error-2001-autoexec-access-macro

Posted by: FrankRuperto Oct 25 2019, 08:59 AM

Is the database located in a trusted location?
Sometimes you have to add the location as a trusted location under Access options.
Is there some other form that is set up as a default to open?
Itís really hard to say without seeing the db.

Posted by: dmhzx Oct 25 2019, 09:33 AM

When you say it works if you run it manually, we are talking about the Macro itself aren't we.

You open Access normally, and run the Macro and it works, but it doesn't work using the /X switch.

Right now I'm at a loss to explain that.

Posted by: SerranoG Oct 25 2019, 09:34 AM

Cheeky & Frank,

Yes, my Trust Center is set to trust its location. It's really odd. This database has been running perfectly for over a year. This phenomenon has only happened since the last time our IT dept. updated my MS Office about two weeks ago.

Greg



Posted by: FrankRuperto Oct 25 2019, 09:48 AM

Then perhaps it might be that Microsoft made some security changes to Windows 10 or the CTR (Click_To_Run) versions of Office. I heard MS has been tightening security in the CTR virutal container. Are you running the app with admin priviliges?

Posted by: cheekybuddha Oct 25 2019, 09:48 AM

Frank's suggestion about a default form may be worth investigating.

Otherwise, it looks as if DLookup/DCount functions in your code my cause an issue.

Posted by: SerranoG Nov 1 2019, 08:32 AM

I don't have admin rights on my work computer. Our IT dept. is extremely strict about allowing that for staff.

The VBA code where the database stops (when on autopilot, but works fine when I run it manually) is:

CODE
DoCmd.OutputTo acOutputReport, strReport, acFormatPDF, strFile, False, , , acExportQualityPrint


The variable strReport is the name of a report in the database; and strFile is the PDF (including path) where I want the report to go. There is nothing in the report creating an error. As I said, when I run this database manually OR if I just pop open the report, it's fine. No issues. I also don't have any DLOOKUP / DCOUNT calls in the VBA code.

Posted by: cheekybuddha Nov 1 2019, 09:34 AM

Hi Greg,

Have you tried finding any reference to your task in the Event Viewer?

Have you tried running the task manually from the Scheduled Tasks window? (Select your task, click 'Run' in the panel on right)

Can you export the task (also in panel on right) and post the xml file here?

Posted by: AlbertKallal Nov 1 2019, 11:32 AM

The approach I used in the past is this one:

http://www.kallal.ca/BatchJobs/Index.html


The above is kind of nice, since then you not limited to running a particular macro on startup. The batch file (actually a windows script) thus allows you to call all of the routines.

One of the possible issues is that perhaps the report or routine is not 100% finished, and the you issue a quit application. I can't say this really has anything to do with your actual problem, since you had success doing what you doing for some time.

Anyway, take a quick read of that article - it might be worth a try. I would also note that my article is quite old, and you find these days if you just click on a windows VBS script it tends to launch as x64, and thus the whole easy part of that article tends to break down (you have to use cscript.exe to launch the script, and you have to make sure you choose the x32 bit of cscript.exe for the above ideas and article to work.

But, the article is simple in concept.
Regards,

Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada


Posted by: FrankRuperto Nov 1 2019, 03:54 PM

@Albert

That's a good alternative, unless Greg's IT department has disabled running vb scripts in group policy settings.
What's curious is everything was working until about 3 weeks ago and no mods were made by Greg, so did a Windows, Office, or IT Dept update break it?
Could it be that you are linking to a password-protected backend and the password expired?
Have you run compact/repair on your FE?

The Access error message doesnt seem to relate at all to the problem.
Error 2001 usually occur when Access cannot make sense of part of an embedded SQL statement, or when elements within a Domain Aggregate Function are incorrectly specified.
Perhaps turning on trace and recording it in a log file could reveal, or looking at the Windows and Office Event logs?
You could spend hours trying troubleshoot the problem, only to find out there's nothing you can do about it with the existing setup, so a workaround like Albert's, if allowed, is a good solution.

 

Posted by: FrankRuperto Nov 9 2019, 07:32 PM

@SerranoG

So it occurs to me that since your IT Dept restricts admin privs on your box that they're also auto logging you out of the box, or locking the account which requires a password to unlock, after X amount of inactive time?

If that's true, the task scheduler cannot launch your Access app if no user is logged in.