Solved

MS Access 2010 - Scheduling a table for automatic update every day

Posted on 2012-03-24
11
825 Views
Last Modified: 2012-03-28
I have a table that I need to update (append records) daily from a delimited text file.  I know how to do this with a form button that runs VBA code, but I would like to schedule this task to run automatically every day.  I am not a programmer or advanced user, so I could use some help.    Any ideas?
0
Comment
Question by:WallyTee
  • 6
  • 4
11 Comments
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 400 total points
ID: 37761122
The issue of doing anything "Daily/automatically" is fraught with "gotchas"

What if the machine is turned off (or sleep, or hibernating)
What if the append fails? (Got "rollbacks" and "Iron-clad" error handling?)
What if the code is run twice (or not at all) by accident because of a date/time reset or glitch (ex.: daylight savings time)

In any event... you would create a macro that runs this procedure ("mcrAppend")
Then you would create a task in the Windows task scheduler.
...and have something like this as the command:

"C:\Program Files\Microsoft Office\Office12\MSAccess.exe" "c:\YourFolder\Yourdatabase.accdb" /x mcrAppend

Then set your scheduling as needed

JeffCoachman
0
 
LVL 57
ID: 37761146
<<What if the machine is turned off (or sleep, or hibernating)
What if the append fails? (Got "rollbacks" and "Iron-clad" error handling?)
What if the code is run twice (or not at all) by accident because of a date/time reset or glitch (ex.: daylight savings time)
>>

 As Jeff has said, you need to think about a few things.  Best setup I've found is to use windows Task scheduler, using the macro switch or /cmd switch to pass arguments to the app to control it's processing.

 You can of course just write a small DB that does nothing but run the import.  Call your function from the autoexec macro, which will run automatically if it exists and you do nothing at the command line when opening the DB (or put the call in a startup form).

Jim
0
 

Author Closing Comment

by:WallyTee
ID: 37761153
Got it Jeff.  Thanks for the solution AND the warning about possible glitches.  Fortunately, this little process is not really critical.  Nevertheless, we'll be as careful and alert as we can.

Many thanks for all your help.  You've been great, and I've learned a lot.  You certainly motivated me to learn how to use VBA.  (Most of the DB work I have done up to this point has been very rudimentary.)    WallyTee
0
 

Author Comment

by:WallyTee
ID: 37761157
Hi Jim ---- thanks to you too.  I really appreciate it.   WallyTee
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37761241
Yes,
Jim is great at "Rounding Out" other Experts comments...
(especially mine.)
....and adding other helpful hints.
The bit about a dedicated import DB with an autoexec macro is also worth investigating...
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37761263
<You certainly motivated me to learn how to use VBA.  (Most of the DB work I have done up to this point has been very rudimentary.)>

To be clear, my (and the other expert's) suggestion about learning VBA comes *AFTER* you fully understand the basics of database design:
Normalization
Relationships
...etc
Then query design
Then Form/Report design

VBA just provides "automation"

;-)

Jeff
0
 

Author Comment

by:WallyTee
ID: 37761279
Got it Jeff.  I understand, and I appreciate your and Jeff's advice very much..  

FYI, although I'm not a developer per se, I understand tables, relationships, referential integrity, queries, forms, switchboards and macros pretty well.  It's just that the DBs I have built over the years have been relatively simple.  Almost all were for my own use, and I didn't have much need for 'automation' beyond simple menus with macros to open queries and reports.  

Thanks again.   WallyTee
0
 

Author Comment

by:WallyTee
ID: 37761289
And Jim's too.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37761336
With the advent of Access 2010, "Web Databases", enhanced security, Improved macro functionality, ...and MS making Macros the default programming method for 2007-2010 databases, ...macro questions are becoming more popular here.

There are many discussions here and on the web about macros versus vba.

Then bottom line is (AFAIC) if your needs are simple, *and they will remain simple*, or you are developing a "web" database, ...then using macros is fine.

But almost inevitably, your apps become more complex and you will find yourself limited by macros.
Then you will have to move to VBA anyway.
...and even more, ...since you don't really want a mix of technologies in your db, you will really want to convert all your existing macros to VBA anyway.

JeffCoachman
0
 

Author Comment

by:WallyTee
ID: 37761507
Good counsel Jeff.   Thanks again.   WallyTee
0
 

Author Comment

by:WallyTee
ID: 37776063
From WallyTee --- I finally found a solution on my own.  Instead of using the macro action titled "ImportExportText,' I did the following:

1.  Used the standard Import External Data wizard
2.  While still in the wizard, I saved and named the "Import Process."
3.  Created a macro using the "RunSavedImport" action
4.  Entered the name of my saved "Import Process."
5.  Saved and named the macro.
6.  Ran the macro and it worked.  

I then set up and tested a 'Scheduled Task' to run the macro at a certain time.  When the time came, the macro automatically imported the data correctly.  

As a 'low intermediate' user, I sure learned a lot.    Thanks to all the experts who contributed.  WallyTee
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

746 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

9 Experts available now in Live!

Get 1:1 Help Now