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
Solved

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

Posted on 2012-03-24
11
830 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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
 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

840 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