Others

How to Calculate Straight-Line Distance Between Addresses in Microsoft Access

Steve KSteve K Nov 2, 2026 870 views

Question details

The user needs to calculate the straight-line distance between two street addresses in Microsoft Access using a free geocoding service.

How to Calculate Straight-Line Distance Between Addresses in Microsoft Access
Product
Microsoft Access 2019
Device & OS
not provided
Scenario
Calculating geographic distance between two locations based on street addresses without relying on postal codes.
Observed behavior
Needs to convert raw street addresses into latitude and longitude coordinates and apply a mathematical formula to find the exact distance.
Before you start

Before proceeding, select a geocoding service (such as OpenStreetMap, Bing Maps, or Google Maps) and ensure you have reviewed their API key requirements and free tier limits.

Solution 1Recommended

Geocode Addresses and Apply the Haversine Formula via VBA

Use an external API to convert street addresses to coordinates, then calculate the distance using a custom VBA function.

Microsoft Access cannot natively determine the geographic location of a street address. To solve this, you must first use a geocoding service to retrieve the latitude and longitude. Once you have these coordinates, you can use mathematical models like the Haversine or Vincenty formula to calculate the straight-line distance between the two points.

1
Choose a Geocoding Provider

Select an API provider such as OpenStreetMap Nominatim (which is free and does not require an API key for low-volume requests), Bing Maps, or Google Maps.

2
Create a Geocoding VBA Function

In Access, open the VBA Editor (Alt + F11). Create a new module and write a function using 'MSXML2.XMLHTTP' to send a request to your chosen API with the address string. Parse the returned JSON or XML response to extract the 'lat' and 'lon' values.

3
Implement the Distance Formula

Create a second VBA function that accepts four parameters: Lat1, Lon1, Lat2, and Lon2. Inside this function, apply the Haversine formula to convert the coordinate differences into radians and calculate the Earth's spherical distance in miles or kilometers.

4
Run the Calculation in a Query

Open your Access database query design view. Create a new calculated field that calls your geocoding function for both addresses, then passes those results into your Haversine VBA function to output the final straight-line distance.

Geocode Addresses and Apply the Haversine Formula via VBA
Monitor API Rate Limits: Always verify the terms of service for your geocoding provider. Automating requests for a large database of addresses can quickly exceed free daily quotas and result in your API key being blocked or charged.
Free Microsoft Office alternative

Need a Lightweight Alternative for Everyday Data Management?

While Microsoft Access is built for complex relational databases and VBA geographic calculations, everyday data tracking and address management can often be handled more easily in a spreadsheet. WPS Office provides a free, highly compatible alternative to Microsoft Office for your document and spreadsheet needs.

  1. 1. Download WPS Office: Visit the official WPS website and download the free installer for your operating system.
  2. 2. Install the Software: Run the installer and follow the quick setup instructions to configure WPS Office on your device.
  3. 3. Open Your Data Files: Launch WPS Spreadsheets and open your existing .xlsx or .csv address lists to manage your data effortlessly.
Highly compatible with Microsoft Excel (.xlsx) formats for data listsLightweight installation and incredibly fast performanceFree to use for everyday office tasks without subscription feesFamiliar user interface makes transitioning from MS Office seamless
microsoft office alternative - wps office

Frequently Asked Questions

Can I calculate driving distance instead of straight-line distance in Access?

Yes, but this requires a routing API (like the Google Maps Distance Matrix API or Bing Maps Routes API) instead of a simple mathematical formula. You must send an API request containing both addresses to receive the driving distance and estimated travel time.

Do I have to pay for a geocoding API to use in MS Access?

Not necessarily. Services like OpenStreetMap Nominatim offer free geocoding for low-volume, non-automated requests. Commercial APIs like Bing Maps and Google Maps also offer free daily tiers, but you must strictly monitor your usage to avoid exceeding the free quota limits.

What is the difference between the Haversine and Vincenty formulas?

The Haversine formula assumes the Earth is a perfect sphere, making it computationally simpler and faster but slightly less accurate. The Vincenty formula models the Earth as an oblate spheroid, providing highly accurate distance calculations down to fractions of a millimeter, though the VBA code required is significantly more complex.