Solved

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

Posted on 2012-03-28
7
674 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
  • 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 77

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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

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 77

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Sometimes drives fill up and we don't know why.  If you don't understand the best way to use the tools available, you may end up being stumped as to why your drive says it's not full when you have no space left!  Here's how you can find out...
If you get continual lockouts after changing your Active Directory password, there are several possible reasons.  Two of the most common are using other devices to access your email and stored passwords in the credential manager of windows.
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…
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.

932 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

14 Experts available now in Live!

Get 1:1 Help Now