Bulk Update Microsoft Access Database

Introduction

Microsoft Access is one of the easiest to use database application software with a lot of wizards and graphical utilities to guide the users along. For these reasons, I like to use it for prototyping database designs and processing small sets of data. One of the annoyances with Microsoft Access is that the Query tool does not allow me to run bulk SQL statements at one time as shown in the figure below.

This post shows a simple method to perform a bulk update of records in a Microsoft Access database from SQL statements in a text file using Perl and ODBC. I will also provide a link to download the Perl program

Software Prerequisites

1. Download and install ActiveState Perl.
2. Download and install the ODBC Driver for Perl DBI.

Notes: To download and install the ODBC Driver, select Start > All Programs > ActiveState Perl 5.x.x Build xxx > Perl Package Manager (see the figure below). Look for the package DBI-ODBC and mark it for install. Finally, run the installation.


Define ODBC Data Source

If you know how to use the ODBC Data Source Administrator to define a data source name, then you can skip this section.

1. Select Start > Control Panel.
   The Control Panel dialog appears.
   
2. Click the following: Performance and Maintenance > Administrative Tools > Data Sources (ODBC).
   The ODBC Data Source Administrator appears.
   
   
3. Click Add.
   The Create New Data Source dialog appears.
   
   
4. Choose Microsoft Access Driver (*.mdb) and click Finish.
   The ODBC Microsoft Access Setup dialog appears.
   
   
5. Type in your Data Source Name and click Select and choose your Microsoft Access database file.
   
6. Click OK to close all the dialog boxes.

Create Bulk SQL Text File
You can use any tool or text editor to create your text file containing the bulk SQL statements. An example file is shown below.

UPDATE STORES SET BUILDINGNAME='Post Centre' WHERE ID=34;
UPDATE STORES SET BUILDINGNAME='Empire State Building' WHERE ID=3;
UPDATE STORES SET BUILDINGNAME='Twin Towers' WHERE ID=1;

Using the BulkSQL Program

1. Download and the BulkSQL.pl program from this link and uncompress the file into a folder of your choice.
2. Open a Command Prompt window.
3. At the prompt, type in the following commands to run the program.
   
   Note: Replace mydatabase with the actual name of your ODBC data source name to your Access database file. Replace mybulksql.txt with the actual name of your bulk SQL text file.
   
   C:\> perl bulksql.pl mydatabase mybulksql.txt
   
   A sample run is shown in the figure below.
   
   

Notes:

• The program will print out the number of records affected by a single SQL statement.
• The program will print out the SQL statement if it did not affect any records.