MS Access: Geocoding and Distance Reporting

This is some code and controls that help you geocode addresses, and prepare a report of addresses sorted by distance from a point.

It's based on the Excel Geocoding Tool, but expands on it by adding a few features, including caching of calculated locations.

Addresses are stored in their own table, and are normalized a little bit, so that you don't end up geocoding the same address over. (For example, if you have 50 people at an office, that location should only be geocoded once.)

The code also shows how to change the sql datasource of a report in VBA code.

The code's incomplete, and you it's not a drop-in library. Integration will take some effort. There probably won't be any other "releases".

[I've been fixing up the code. This original code is a mess, and there are some weird things going on because I didn't understand VBA exception handling ( http://www.zipcon.net/~bobrosen/professional/exceptions.html ).]

AttachmentSize
GeocodingDistanceKit.zip56.59 KB

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

Hi, I am trying to implement

Hi,

I am trying to implement something similar and I downloaded you "app" but when I run it it chokes on Public Function DistanceFromSql(...) when it tries to Dim c As CachedAddress. The type is not defined. Where is this type defined? I am assuming this is in a Reference .dll but which one?

I know you are not supporting this but this ought to be easy. Thanks in advance.

Marcus

The CachedAddress object is

The CachedAddress object is one of the classes included with this.

If I can find some time, I'll try to make this simpler to use and include some example applications.

Do you have directions about

Do you have directions about how to use this? Thanks.

Sorry. Not really. It's

Sorry. Not really. It's intermediate level MS Access VBA programming.

The idea is that you import the tables and code, and integrate it with an existing system. This isn't a complete app. It was extracted from a db I develop at work.

One thing that's important, is to know that it maintains a cache of queried addresses in tblAddresses. You have to add a column to your tables that will reference tblAddresses.

Great job. I'm glad to see

Great job. I'm glad to see the geocoding tools used more widely.