Solved

Batch running of Access

Posted on 2014-03-19
9
344 Views
Last Modified: 2014-03-20
I have been having a lot of issues getting a windows scheduled process to work properly on a remote server.

The process worked for years. Effectively what it does is update one table in an Access database using a csv file; that table is linked to a Quick Books database table. Then it exports the contents of three Quick Books tables (all linked to MS Access) to csv files.

All this is initiated from a .bat file, with is command:

"C:\Program Files\Microsoft Office\Office11\MSACCESS.EXE" "C:\C_LSS\DB\Linked_to_LSS.mdb"  /x "LSS_MACR_Mo-1"

I realized today that in using GotoMyPC to access the remote computer, that I have been leaving Access OPEN in that computer overnight when the scheduled process runs.

When I look in the morning, Access is open & it is asking if I want to update a table, etc. Of course, I want all that to run transparently overnight.

Can I be causing issues by leaving Access "open" on the remote machine?

Thanks
0
Comment
Question by:Richard Korts
  • 4
  • 3
  • 2
9 Comments
 
LVL 28

Accepted Solution

by:
omgang earned 250 total points
ID: 39940383
In my experience launching an Access macro via Scheduled Task spawns a new Access process on the machine.  When you leave Access open are you leaving the same db open?  E.g. are you accessing Linked_to_LSS.mdb during your GoToMyPC sessions and leaving it open?
OM Gang
0
 
LVL 28

Expert Comment

by:omgang
ID: 39940391
...and do you have Warnings turned off in the Macro or in any procedures that are called from the Macro?
OM Gang
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 39940539
Your macro needs to explicitly close Access as the last step.  It also needs to turn warnings off so you don't get any notification messages.

Leaving Access open this way could interfere with the ability of other users to open objects in design view.  It could cause other update conflicts depending on what task Access is running.
0
 

Author Comment

by:Richard Korts
ID: 39943127
How do I turn warnings off in the Marco (& queries it runs)?

How do I close Access at the end?
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 28

Expert Comment

by:omgang
ID: 39943174
Access 2010?  In Macro design, click on the Show All Action ribbon option.
Now, you should see SetWarnings as an available option in the list of Actions.
SetWarnings No   <-- turns warnings off
SetWarnings Yes  <-- turns warning on

You'll also see an action named
QuitAccess

Make sure that's the last action in your macro as it will close the db and exit the Access application.

OM Gang
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
ID: 39943581
Even though this macro runs unattended I like to always include a line that sets the hourglass on whenever I set warnings off.  This is a visual clue to me should the macro stop for some reason that the warnings are still off.  I have two macros in every application (in most apps, they are the ONLY macros).  One to set warnings off and the hourglass on and the second to do the opposite.  This gives me an easy way to modify the settings.

I do this because leaving warnings off is deadly dangerous when you are developing.  If you close some object without explicitly saving it, Access will silently discard your changes.  Bye-bye 4 hours of work.  With warnings on, Access will prompt you when you close an object to remind you to save when you have changed it.
0
 

Author Comment

by:Richard Korts
ID: 39943674
I understand both of your inputs.

As I said in the posting, a slightly different version of this Macro has been running, unattended, every night, for about 6 years.

No problems.

So I don't know what to do.
0
 
LVL 28

Expert Comment

by:omgang
ID: 39943688
I don't think you answered my question from yesterday.

.....When you leave Access open are you leaving the same db open?  E.g. are you accessing Linked_to_LSS.mdb during your GoToMyPC sessions and leaving it open?

OM Gang
0
 

Author Comment

by:Richard Korts
ID: 39943728
I just discovered the original macro HAS SetWarnings No.

So I'm going with that on this one; see what happens tonight.

Thanks
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now