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

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.
WallyTeeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SysExpertCommented:
You need to build a small test environment to check your options.

See what works and what doesn't.

I hope this helps !
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
WallyTeeAuthor Commented:
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
arnoldCommented:
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
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

WallyTeeAuthor Commented:
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
arnoldCommented:
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
WallyTeeAuthor Commented:
Thanks Arnold.  I think I'm OK now.  Still testing to be sure.  WallyTee
0
WallyTeeAuthor Commented:
Thanks to Arnold and the Experts Site !!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Windows 7

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.