How To: Export Data From MS Access Database, Automatic, No Mouse Clicks!
MS Windows products and MS Access Database in particular are some of the most annoying and horrible applications that stalk that Earth. The documentation is 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 and into a database or some other format will be another adventure 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 must pay me huge royalties. Anyone who has a thought about mouse-free processing 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. Copywrite Steven Hauser 2000-2005.
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. 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.
