Bulk update Microsoft Access database with VBA

Sometimes I want to perform a simple bulk update of records in a Microsoft Access database using plain SQL text as shown below.

UPDATE STORES SET BUILDING='EMPIRE STATE BUILDING' WHERE ID=10001;
UPDATE STORES SET BUILDING='BIG BEN' WHERE ID=100560;
UPDATE STORES SET BUILDING='EIFFEL TOWER' WHERE ID=103891;
...etc..

Previously, I wrote a Perl script that uses ODBC to do the job as described in my previous post. However, , I found myself in a situation recently without Perl and the DBI-ODBC module on my working computer. So I decided to explore bulk updating using Microsoft Access's built-in VBA engine. The following describes my method.

Create a public function in a VBA Module
First, create a function that executes the SQL statements in a series or in a loop.

1. Start up Microsoft Access and open up a database to be updated.
2. Press ALT+F11.
   
   The Microsoft Visual Basic editor appears. By default, a new module - Module1 is created.
   
3. In the editor, create a new public function e.g. BulkUpdate by typing in the following VBA code:
   
   
4. Using any text editor e.g. Notepad, enclose the bulk update SQL statements with double quotes and prefix with the statements with db.Execute as shown below.
   
   
   
   
   db.Execute "UPDATE STORES SET BUILDING='EMPIRE STATE BUILDING' WHERE ID=10001;"
   db.Execute "UPDATE STORES SET BUILDING='BIG BEN' WHERE ID=100560;"
   db.Execute "UPDATE STORES SET BUILDING='EIFFEL TOWER' WHERE ID=103891;"
   ...etc..
5. In the VBA Editor, copy and paste the bulk update sql text into the public BulkUpdate function.
   
   
6. Select File | Save.
   
   The Save As dialog box appears.
   
7. Optional. In the Module Name field, type in a name e.g. Module1. Click OK.
   
   The module is saved.
8. Select File | Exit to close the VBA editor. 

Create a Macro

Next, create a Microsoft Access macro to call the previously created public function.

1. Click New as shown in the figure below.
   
   
   
   The Macros dialog box appears.
   
2. Click the Action drop down list. Choose RunCode.
   
   The Function Name field appears.
   
3. In the Function Name field, click the Build button.
   
   The Expression Builder dialog box appears.
   
4. Double click Functions.
   
   The available VBA module names appears.
   
5. Choose the current database e.g. test. Choose the previously create module e.g. Module1. Double click the previously created public function e.g. BulkUpdate.
   
   The selected function appears in the expression field.
   
6. Click OK.
   
   
7. Close the Macro dialog box.
   
   The prompt appears.
   
8. Click Yes.
   
   The Save As dialog box appears.
   
9. Type in a macro name e.g. Macro1. Click OK.
   
   The macro is saved.

Run the bulk update

1. In the Database dialog, choose Macros. Choose the previously created macro e.g. Macro1.
   
   
2. Click Run.
   
   The selected macro executes the BulkUpdate function, which performs the bulk updating of records.

The example VBA code above is quite simple; there is no error handling. If you have the inclination, it can be made more sophisticated to handle errors in the bulk update statements. But for my needs, it was sufficient.