UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Autoexec V. Manual Code Execution, Access 2016    
 
   
SerranoG
post Oct 14 2019, 01:54 PM
Post#1


UtterAccess VIP
Posts: 2,230
Joined: 1-December 03
From: Lansing, MI USA


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.





--------------------
Greg Serrano
Michigan Dept. of Environment, Great Lakes, and Energy
Air Quality Division
Go to the top of the page
 
theDBguy
post Oct 14 2019, 01:57 PM
Post#2


UA Moderator
Posts: 76,824
Joined: 19-June 07
From: SunnySandyEggo


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?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
cheekybuddha
post Oct 15 2019, 03:59 AM
Post#3


UtterAccess Moderator
Posts: 11,885
Joined: 6-December 03
From: Telegraph Hill


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

--------------------


Regards,

David Marten
Go to the top of the page
 
jleach
post Oct 15 2019, 08:01 PM
Post#4


UtterAccess Administrator
Posts: 10,274
Joined: 7-December 09
From: St. Augustine, FL


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)?

--------------------
Jack D. Leach
Founder & CEO
Dymeng Services Inc.
Business Software Solutions
Go to the top of the page
 
SerranoG
post Oct 22 2019, 03:05 PM
Post#5


UtterAccess VIP
Posts: 2,230
Joined: 1-December 03
From: Lansing, MI USA


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.





--------------------
Greg Serrano
Michigan Dept. of Environment, Great Lakes, and Energy
Air Quality Division
Go to the top of the page
 
dmhzx
post Oct 22 2019, 06:17 PM
Post#6



Posts: 7,115
Joined: 22-December 10
From: England


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.
This post has been edited by dmhzx: Oct 22 2019, 06:21 PM
Go to the top of the page
 
SerranoG
post Oct 24 2019, 06:47 AM
Post#7


UtterAccess VIP
Posts: 2,230
Joined: 1-December 03
From: Lansing, MI USA


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.



--------------------
Greg Serrano
Michigan Dept. of Environment, Great Lakes, and Energy
Air Quality Division
Go to the top of the page
 
cheekybuddha
post Oct 24 2019, 07:13 AM
Post#8


UtterAccess Moderator
Posts: 11,885
Joined: 6-December 03
From: Telegraph Hill


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

See this UA Wiki page (Scroll down)

So, the command for your scheduled task would look like:
CODE
C:\FullPathTo\MSACCESS.EXE C:\FullPathTo\Database.accdb /x NameOfMacro

--------------------


Regards,

David Marten
Go to the top of the page
 
dmhzx
post Oct 24 2019, 11:08 AM
Post#9



Posts: 7,115
Joined: 22-December 10
From: England


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 )

Go to the top of the page
 
SerranoG
post Oct 25 2019, 08:24 AM
Post#10


UtterAccess VIP
Posts: 2,230
Joined: 1-December 03
From: Lansing, MI USA


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.





--------------------
Greg Serrano
Michigan Dept. of Environment, Great Lakes, and Energy
Air Quality Division
Go to the top of the page
 
cheekybuddha
post Oct 25 2019, 08:28 AM
Post#11


UtterAccess Moderator
Posts: 11,885
Joined: 6-December 03
From: Telegraph Hill


Might any of the issues described here apply to your situation, Greg?

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Oct 25 2019, 08:31 AM
Post#12


UtterAccess Moderator
Posts: 11,885
Joined: 6-December 03
From: Telegraph Hill


Also, perhaps see if this situation is similar

--------------------


Regards,

David Marten
Go to the top of the page
 
FrankRuperto
post Oct 25 2019, 08:59 AM
Post#13



Posts: 337
Joined: 21-September 14
From: Tampa Bay, Florida, USA


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.

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix and Oracle DB's.
Go to the top of the page
 
dmhzx
post Oct 25 2019, 09:33 AM
Post#14



Posts: 7,115
Joined: 22-December 10
From: England


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.
Go to the top of the page
 
SerranoG
post Oct 25 2019, 09:34 AM
Post#15


UtterAccess VIP
Posts: 2,230
Joined: 1-December 03
From: Lansing, MI USA


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



--------------------
Greg Serrano
Michigan Dept. of Environment, Great Lakes, and Energy
Air Quality Division
Go to the top of the page
 
FrankRuperto
post Oct 25 2019, 09:48 AM
Post#16



Posts: 337
Joined: 21-September 14
From: Tampa Bay, Florida, USA


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?
This post has been edited by FrankRuperto: Oct 25 2019, 09:58 AM

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix and Oracle DB's.
Go to the top of the page
 
cheekybuddha
post Oct 25 2019, 09:48 AM
Post#17


UtterAccess Moderator
Posts: 11,885
Joined: 6-December 03
From: Telegraph Hill


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.

--------------------


Regards,

David Marten
Go to the top of the page
 
SerranoG
post Nov 1 2019, 08:32 AM
Post#18


UtterAccess VIP
Posts: 2,230
Joined: 1-December 03
From: Lansing, MI USA


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.

--------------------
Greg Serrano
Michigan Dept. of Environment, Great Lakes, and Energy
Air Quality Division
Go to the top of the page
 
cheekybuddha
post Nov 1 2019, 09:34 AM
Post#19


UtterAccess Moderator
Posts: 11,885
Joined: 6-December 03
From: Telegraph Hill


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?

--------------------


Regards,

David Marten
Go to the top of the page
 
AlbertKallal
post Nov 1 2019, 11:32 AM
Post#20


UtterAccess VIP
Posts: 2,902
Joined: 12-April 07
From: Edmonton, Alberta Canada


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

Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    8th December 2019 - 11:32 PM