| Articles Home |
MS Windows products and the MS Access Database in particular are some of the most annoying and horrible applications that stalk the Earth. The documentation is fractured, not whole system oriented and mouse centrically lame, there seems to be no way to do anything without being there in person and "clicking" away on the damn mouse. Gah. Well, after reading lots of docs and thrashing around for a while I figured out the Secret Commands (tm) and Arcane Mumbojumbo (tm) to do it.
Of course, it takes hundreds of mouse clicks and disparate mysterious sets of software to set this up. It is still many times more complex than dumping data from Oracle, DB2, MySQL, Postgresql or Informix but never fear, it can be done. Getting the data automatically off the box is our adventure today, actually porting to another database we will leave for another time.
I have applied for a patent for a new concept I will call "batch processing" and also copywrite this "batch processing" thing so I can take Microsoft to the cleaners. Anyone using this concept of "batch processing" or "automatic processing" or "unattended work" or "timeshifted work" on a Microsoft system or using Microsoft software must pay me huge royalties. Anyone who has a thought about mouse-free processing using Microsoft must pay, too. Any work or writing of technical documentation describing these processes in one place so they are understandable or usable by a human must obtain my permission and pay massive license fees, I patent the idea of documenting a work process in one document. Not using mouse clicks and using a script or code instead is so foreign to Microsoft products I am sure that it is an unheard of concept and I have the Copywrite: Steven Hauser 2000-2015.
The process is thus:
- In MSAccess make a "macro" to dump the data from a table.
- Make sure a task scheduler is running and put the macro in it
I now pass the arcane detailed process on to the Secret Society of DBAs (by finding this web page you are now a member.) We are dedicated to porting data out of the MS Access Database and into freeware. Or at least get the data out of the Evil Bill Empire. FREE THE DATA!
MAKE A MACRO IN MS ACCESS:
Start->Programs->Microsoft->Access
open existing database
Macros->New
action----TransferText
transfer Type--Export Delimited
Specification Name--(leave this blank)
Table Name--(type in some TableName)
File Name--(some destination file name like C:\Tablex.txt) --NOTE!
.txt is NEEDED
or error occurs.
Has Field Names--no
HTML Table Name--(leave blank)
Code Page-- (leave blank)
actions--Quit
Options--SaveAll You are now done writing code that turns into some sort
of Visual Basic crud.
File->Save->Save As->Give the macro a name, like export_table1
MAKE A SHORTCUT FOR THE MACRO
select export_table1 macro->right button click with mouse->Create Shortcut (puts it on desktop)
AUTOMATE THE MACRO:
The first time you must set up the Scheduled Task service on the Windows system:
Start->Settings->Control Panel->Administrative Tools->Services->Task Scheduler->Start
On Windows find the Scheduled Tasks program:
Start->Programs->Accessories->System Tools->Scheduled Tasks
In Scheduled Tasks
Add Scheduled Tasks->Scheduled Task Wizard->do what it says
location of my Shortcut to be run using Wizard
(find your shortcut file location from its Properties)
C:\Documents and Settings\myusername\Desktop\export_table1
DONE
NEW
Make an "MSACCESS.EXE" shortcut. Add command line options
"command line startup command line" options for Access:
"/ro" for readonly databases,
"/x macroname",
"/nostartup" for no startup screen,
"/?" for getting startup option
help screen (not easy to find otherwise).
example: "msaccess.exe /ro /x /nostart"
Then schedule that shortcut in one of the job schedulers for MS Windows.
Now that was not too hard was it? Well, yes, it was a pain to find all the little bits of stupid mouse clicks and string it together. Now all you have to worry about is keeping the Scheduled Task service alive and hope that the MS OS stays up when you need the data. This seems to be a real problem I have noticed with MS products in general as the boxes freeze up every couple days if they are not rebooted.