Solved

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

Posted on 2012-03-28
7
692 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 500 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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

A small collection of useful tips and tricks for Windows 10 users that I decided to write as a result of recent questions that were asked and answered at Experts Exchange. Two short video tutorials included. Enjoy..
Windows 10 Creator Update has just been released and I have it working very well on my laptop. Read below for issues, fixes and ideas.
This Micro Tutorial will give you a basic overview of Windows DVD Burner through its features and interface. This will be demonstrated using Windows 7 operating system.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Suggested Courses

617 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