Solved

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

Posted on 2012-03-28
7
681 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 78

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
Create the perfect environment for any meeting

You might have a modern environment with all sorts of high-tech equipment, but what makes it worthwhile is how you seamlessly bring together the presentation with audio, video and lighting. The ATEN Control System provides integrated control and system automation.

 

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 78

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

Easy, flexible multimedia distribution & control

Coming soon!  Ideal for large-scale A/V applications, ATEN's VM3200 Modular Matrix Switch is an all-in-one solution that simplifies video wall integration. Easily customize display layouts to see what you want, how you want it in 4k.

Question has a verified solution.

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

No single Antivirus application (despite claims by manufacturers) will catch or protect you from all Virus / Malware or Spyware threats. That doesn't stop you from further protecting yourself however - and this article is to show you how.
The Windows functions GetTickCount and timeGetTime retrieve the number of milliseconds since the system was started. However, the value is stored in a DWORD, which means that it wraps around to zero every 49.7 days. This article shows how to solve t…
This Micro Tutorial will give you a introduction in two parts how to utilize Windows Live Movie Maker to its maximum capability. This will be demonstrated using Windows Live Movie Maker on Windows 7 operating system.
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…

735 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