How to Calculate Straight-Line Distance Between Addresses in Microsoft Access
Question details
The user needs to calculate the straight-line distance between two street addresses in Microsoft Access using a free geocoding service.

- 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 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.
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.
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.
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.
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.
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.

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. Download WPS Office: Visit the official WPS website and download the free installer for your operating system.
- 2. Install the Software: Run the installer and follow the quick setup instructions to configure WPS Office on your device.
- 3. Open Your Data Files: Launch WPS Spreadsheets and open your existing .xlsx or .csv address lists to manage your data effortlessly.

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.




