Batch scripting for MS Access / Excel databases

Hi,
recently I needed a tool to run SQL scripts against a Microsoft Access database. I found OleDbCmd on Sourceforge.net (http://sourceforge.net/projects/oledbcmd/ which did the trick.

However it had a number of shortcomings, so I extended it somewhat and have released my updates through sourceforge.net as version 1.0. These usage notes should explain the extent of the updated application:

----------------------------------------------------------------------------------------------------------------
Purpose:

This program allows you to execute SQL commands against an OleDB Database
- eg a Microsoft Access database, an Excel spreadsheet, etc. This can be a
powerful adjunct to LGX Report (see www.freereporting.com) as it allows
database creation to be scripted rather than manually creating a database
with Microsoft Access. Another useful free tool to load data into your
database is Microsoft's LogParser 2.2 - search their site for the latest
download location for this tool.

Usage:

OleDBCmd [/help] [/ver] [/connect= | /file=filename] ...
[/create] [/script= | /cmd="sql command" ...
[/quiet] [/CSV] /nohead] [/starComments]

Parameters:

/help Print the usage details and exit.

/ver Print the version and license details.

/connect Specifies the OleDB connection string for the database, eg
'Provider=Microsoft.Jet.OLEDB.4.0; Data Source=test.mdb'

/file Specifies a file name, the provider will be set to
Microsoft.Jet.OLEDB.4.0. Both .mdb and .xls files are
handled.

/create The program will attempt to create the database.

/script Specifies a script file for the SQL commands to be
read from. Note, commands may span multiple lines and
must be terminated with a semi-colon. Remark lines must
start with -- following any leading whitespace.

/cmd Specifies a single SQL command to execute.

Note, if neither the /script nor /cmd arguments are given
commands are read from the console - comment processing
is not performed.

/quiet Suppresses command echo and status messages. Only SQL
output is written to the console.

/CSV SQL results are output in CSV format rather than the default
TSV format.

/nohead Excludes the header row from the results.

/starComments Allows embedded and multi-line comments using /* ... */ format.

----------------------------------------------------------------------------------------------------------------

As noted above, I found this useful in conjuction with the free web reporting tool - LGX Report (http://www.freereporting.com/) as I can then design databases using Enterprise Architect from http://www.sparxsystems.com.au/ and generate the script to create the database and run it with OleDbCmd.

Hope this is useful to others out there looking to leverage free and low-cost tools to build solutions quickly.

Regards,
Ian.


Ian Hogan - Consultant
THINKronicity Pty Ltd
http://THINKronicity.com.au/