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. 

Google Gadget for showing UTM Grid Zones of the world

The Universal Transverse Mercator (UTM) coordinate system is a grid-based system for specifying locations on the earth's surface. UTM consists of 60 zones, each with its own local Transverse Mercator projection. This gadget will allow you to display UTM grid zones of the world with labels on Google Maps.

Creating buffers in Global Mapper

Buffer zones can be easily created in Global Mapper. The buffers can be merged and/or zoned depending on the parameters you choose during the creation.

The following steps can be used to create buffers using Global Mapper.

1. Start up Global Mapper and open up a data file or create some line work to create buffers.
   
   The figure below shows two line geometries.
   
   
   
   
2. Press ALT+D to activate the Digitizer Tool. Drag a box to select the geometries for buffering.
   
   The geometries are selected.
   
   
   
   
   
3. Press a right click on the mouse.
   
   A pop up menu appears.
   
   
   
   
4.  Choose Advanced Feature Creation Option | Create Buffers Around Selected Features.
   
   The Setup Buffer Area Creation dialog box appears.
   
   
5. In the Number of Buffer Zones for Each Feature field, type in a value e.g. 1.
   
6. In the Buffer Distance field, type in a value e.g. 100,
   
7. Toggle on or off Combine Overlapping Buffer Areas.
   
8. Click OK.
   
   The buffer(s) is created. By default the buffer(s) is created selected.
   
   
    

An example of a single, combined buffer is shown in the figure below.

An uncombined single buffers are shown in the figure below.

The figure below shows an example of combined buffers with multiple zones.

Create range thematics with Global Mapper

 It is not very obvious from the graphical user interface design and arrangement but it is quite possible to create thematic maps with Global Mapper; but the function is not as convenient as GeoMedia's or MapInfo's. I tried to use Global Mapper to create a similar range thematic map of average temperatures in the US as the sample GeoMedia dataset (shown below).

To create thematic maps in Global Mapper, do the following:

1. Start up Global Mapper and display a polygon geometry data file e.g. States.shp.
   
2. Press ALT+C.
   
   The Overlay Control Center dialog box appears.
   
   
3. Select the layer in the list box and click Options.
   
   The Vector Options dialog box appears.
   
   
4. Click the Area Styles tab.
   
   
   
5. Toggle on Apply Styling Based on Attribute/Name Values.
   
   The Attribute/Name to Base Style On drop down list is activated.
   
6. In the Attribute/Name to Base Style On field, choose a field e.g. AVETEMP.
   
   
   
   Note: In this example, we want to create a range thematic map based on the average temperature values in Fahrenheit from 20 degrees to 100 degrees. Each range is 10 degrees e.g. 60 ~ 69.99.
   
7. Click New Value.
   
   The Select Area Style dialog box appears.
   
   
8. In the Fill Pattern field, choose a value e.g. Solid Fill. Click the Fill Pattern Color.
   
   The Color dialog box appears.
   
   
9. Choose a color. Click OK.
   
   The Enter Value for Style dialog box appears.
   
   
10. Enter the value e.g. 60. Click OK.
    
11. Repeat the previous steps 7 to 10 to create the ending range value e.g. 69.99.
    
12. Repeat the previous steps 7 to 11 to create the next starting and ending range values e.g. 70 to 79.99.
    
13. Toggle Interpolate Colors Between Numeric Values on.
    
    The Vector Options dialog box may look like this.
    
    
    Note: It is much easier to define thematic ranges in GeoMedia as it has a function to automatically classify the ranges based on the actual attribute values.
    
14. Click OK.
    
    The range thematic map is created. Compare this with the sample GeoMedia thematic map above.
    
    

GDM2000 - BRSO (GRS80) Coordinates Conversion Calculator

Note: A WebApp version of this Google Gadget is available here http://dominoc925-pages.appspot.com/webapp/gdm2000brso/default.html.

GDM2000 or Geodetic Datum of Malaysia 2000 and BRSO or Borneo Rectified Skew Orthomorphic are coordinate systems commonly used in East Malaysia. This Google Gadget will allow you to convert between one or more pairs of BRSO easting, northing coordinates and geographical GDM2000 latitude, longitude (GRS80) coordinates, all on GRS80 ellipsoid.

Using Excel to calculate the RMSE for LiDAR vertical ground control points

The height accuracy of the collected LiDAR data can be verified by comparing with independently surveyed ground control points on hard, flat, open surfaces. It is essentially just calculating the height differences for all the control points and then determining the height root mean squared error (RMSE) or differences. Most LiDAR processing software have the reporting function built-in. However, plain Microsoft Excel can also do the job (except for extracting the elevation from the LiDAR data.

Assuming that you are able to calculate the height differences for all the control points and place in a spreadsheet as shown in the figure below. I have a column of delta Z values in column A.

Then to calculate the RMS value for the elevation differences, I can do the following.

1. In a cell, type in the formula:
   
   = SQRT(SUMSQ(A2:A18)/COUNTA(A2:A18))
   
   where A2:A18 are the values from cell A2 to A18 in the spreadsheet. Simply replace these with the actual locations on your spreadsheet.
   
   
2. Press RETURN.
   
   The RMSE value is calculated.
   

GDM2000 - MRSO(Peninsular) on GRS80 Ellipsoid Coordinates Converter

Note: A WebApp version of this Google Gadget is available here http://dominoc925-pages.appspot.com/webapp/gdm2000mrso/default.html.

GDM2000 or Geodetic Datum of Malaysia 2000 and MRSO(Peninsular) or Malayan Rectified Skew Orthomorphic are coordinate systems commonly used in Peninsular or West Malaysia. Thie Google Gadget will allow you to convert between one or more pairs of MRSO(Peninsular) easting, northing coordinates and geographical GDM2000 latitude, longitude (GRS80) coordinates, all on GRS80 ellipsoid.