Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Using Windows Task Scheduler to append data to an Access table daily

Posted on 2012-03-28
7
Medium Priority
?
700 Views
Last Modified: 2012-04-02
On a recurring daily basis (e.g. 1:00 AM), I need to import a comma delimited text file into an Access 2010 database. The objective is to append the file’s data to a table in the database.  I plan to use Windows 'Scheduled Tasks' feature to control the process.  I would prefer to have the Scheduler trigger an Access macro that runs a "Saved Import," but I believe I could work with VBA code (a module??) if necessary.

To support the task, I can either leave the database program running all the time, or have the Scheduler turn it on and immediately run the macro or VBA code.  I understand the latter is not too good an idea, because (I believe) a manual turn-on would cause the ‘import’ to run --- in which case it could duplicate records entered by the scheduled run.    

From my research so far, it doesn’t appear that there is an easy way to set this up without extensive code writing, but I hope I’m wrong.

Does anyone have any ideas, suggestions or thoughts on this subject?   Many thanks.

P.S. I am reasonably familiar with macros, I have a raw beginner’s knowledge of VBA, and I am reasonably familiar with the Windows “Scheduled Tasks” feature. But I am not a programmer or a highly advanced user.
0
Comment
Question by:WallyTee
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 63

Accepted Solution

by:
SysExpert earned 1500 total points
ID: 37779642
You need to build a small test environment to check your options.

See what works and what doesn't.

I hope this helps !
0
 

Author Comment

by:WallyTee
ID: 37779719
Good guidance.  I've already built the test environment --- at least for the database.  I have everything working except the scheduling.  I have the "Saved Import" available and tested, the macro is built and worked, etc.  I just can't figure out how to use the Scheduler the trigger the macro.  

In the Task Scheduler, I notice that the pull-down to select an action only has three items: Run a Program, Send a Message, and Display a message.  I'm not really interested in any of these three.  I want to run a macro (mcrAppend) in a DB program that is already running.  Since I didn't see exactly what I wanted, I set the Action to "Run a Program."  I then entered the following as the path to the macro:

"C:\Program Files\Microsoft Office\Office14\MSAccess.exe" "F:\Logfile-Access Project_Mar-April 2012\ Weblog Analysis Mar-Apr 2012 -3-28 1pm.accdb" /x mcrAppend

All I get is an error message saying the Scheduler can't find that path.  

Any other ideas will be greatly appreciated.  Many thanks.  WallyTee
0
 
LVL 79

Expert Comment

by:arnold
ID: 37779796
Can you update the data while the other app is accessing. I.e. did you design the database with locking that can be accessed updated by multiple sources using locks?
This way the scheduled import can run without the need to shutoff, restart the process.
Alternatively, if the app upon startup imports a specific file, all the scheduled task would have to do is place the new file in correct location, and then restart the process.
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

Author Comment

by:WallyTee
ID: 37779902
Hi Arnold.  Thanks for the response.

To answer your question about using 'locks.' No, I did not.  I'm not a programmer, per se, and am not familiar with them.

My overall task is very simple.  It involves a logging app that I do not control.  That app overwrites an existing comma delimited text file once per day. It contains the previous day's data and is located where I can get to it.  My job is to automatically import that file (once per day) and append its records onto a very simple Access 2010 database table that contains earlier records.  

Since I asked my question on this Experts Exchange site a couple of hours ago, I have made some progress -- I think.  I did a bit of tweaking in the Scheduler to get the Action statements (arguments ???) right, and I changed the security authorization to myself. I also had to correct the path to the Access macro (changing it slightly from the one in my previous post).  The one I'm using "successfully" now is:

"C:\Program Files (x86)\Microsoft Office\Office14\MSAccess.exe" "F:\Logfile-Access Project_Mar-April 2012\Weblog Analysis Mar-Apr 2012 -3-28 1pm.accdb" /x mcrAppend

After these changes, the task ran properly on  a one-time basis.   I'll be running more tests to be absolutely sure, but I think I'm OK.

Do you see anything wrong or risky with this method?  ---

Thanks again.   WallyTee
0
 
LVL 79

Expert Comment

by:arnold
ID: 37780068
Not sufficiently familiar with whether there is something else using it.
The only possibility is at the scheduled event som other process is accessing the database preventing the import.

If the need is to provide access to the data such that the import the data while something else is accessing the data, I'd suggest to use SQL server express with the access as a linked resource.
0
 

Author Comment

by:WallyTee
ID: 37781214
Thanks Arnold.  I think I'm OK now.  Still testing to be sure.  WallyTee
0
 

Author Closing Comment

by:WallyTee
ID: 37796429
Thanks to Arnold and the Experts Site !!!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

When asking a question in a forum or creating documentation, screenshots are vital tools that can convey a lot more information and save you and your reader a lot of time
This article shows how to use a free utility called 'Parkdale' to easily test the performance and benchmark any Hard Drive(s) installed in your computer. We also look at RAM Disks and their speed comparisons.
The viewer will learn how to successfully create a multiboot device using the SARDU utility on Windows 7. Start the SARDU utility: Change the image directory to wherever you store your ISOs, this will prevent you from having 2 copies of an ISO wit…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…

730 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