Update 7th April 2008: Macro Scheduler 10.1 now has native database functions built in so in most cases it is no longer necessary to use the ADODB object in VBScript as described below. See Using Macro Scheduler’s Database Functions

The best way to access (retrieve, modify, or insert) data from a database with Macro Scheduler is to use VBScript and the ADODB object. This lets you access any SQL data source which provides an ODBC driver. E.g. Microsoft Access, Oracle, Informix, Microsoft SQL Server, MySql, etc.

We have a tutorial which shows how to access and update data in Microsoft Access using ADO. It is on the VBScript Resources page, here.

Recently I created an example showing how to retrieve data from a CSV file and insert it into two Access tables. This was in response to a post on the forum. You can download a zip containing a sample CSV file, the Access database and the script file here. To make it work you just have to change the path in the script.

In reality, as I mentioned in my post on the forum, it would be just as simple to create some Access queries to do what this example does within Access itself, but there may be reasons why you’d need to do everything from within a Macro Scheduler script and the demo is valid for any database, not just Microsoft Access. You may want to insert data into a SQL Server database, or a MySQL database held on another server for example. And this may need to be part of a greater automation process.

The examples mentioned here work with Microsoft Access. The code is almost identical for any other type of database. To work with a different database provider you just need to change the “connection string” which is used in the MyDB.Open line. The connection string can be just a data source name if you have a system data source set up. If not it needs to identify which datasource driver to use, where the database is and, if necessary, a username and password to access the database. Depending on the type of database there may be other paramaters that can be used to specify how to access the database.

How do you find out what the connection string should be? Well, the documentation that comes with your database server software should tell you. But if you can’t figure it out here’s a really useful site I found which lists a whole bunch of different connection strings for different types of databases:
http://www.carlprothman.net/Default.aspx?tabid=81.

The only other thing that may need changing is the syntax of the SQL itself. SQL is fairly standard but there can be differences between database providers, and some proprietary statements and functions that may be specific to one particular database.

For more examples try searching the forums for the term ADODB.

This entry was posted on Monday, February 20th, 2006 at 1:08 pm and is filed under Scripting. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.