AEU 21 - Using SQL Server to extend Access - including Geography data (John Heaser)
Outline:
This month's presenter was John Heaser who is an experienced Access developer based in the U.K. (https://heaser.co.uk)
John discussed various ways that SQL Server can extend the capabilities of Access with particular focus on the Georgaphy datatype.
which can represent points, lines and polygons on the Earth’s surface. This data can be used to:
• Find which polygons contain a point:
e.g. find the Local Government Region for a location where you know the Latitude/Longitude (Lat/Long can be found from the PostCode)
• Find the points that lie within a radius of a starting point and order them by distance:
e.g. find the Stores that lie within 50 miles of a customer and show them in ascending order of distance
This can be very useful for many purposes and SQL Server calculates the results very fast - provided that suitable Spatial Indexes are defined.
Unfortunately Access can’t work directly with Geography data types! However, you can make use of Geography data in Access by using:
• Persisted Computed Columns
• Scalar Functions
• Stored Procedures
• Triggers
The same skills can also be used to leverage the power of SQL Server to do other tasks that would be slow to perform or difficult to code, if done directly in Access.
Downloads:
You can download a PDF of the presentation, together with the Access FE database, example SQL scripts from https://isladogs.co.uk/aeu-21/
That web page also includes a link to download the Azure SQL backend database (170MB+) from Google Drive
Times:
00:00 Welcome by Colin and introduction to John Heaser
01:00 John - background to & outline of today's presentation
01:46 Map using Leaflet - Access DEMO
04:23 Use of persisted and non persisted computed columns in SQL Server
10:38 Scalar functions
18:20 Triggers
19:28 UK county geodata
25:34 Use of spatial indexes - significantly improve performance
30:51 Explanation of 'ceremonial' county data - based on snapshot in time (1986?) so subsequent boundary changes do not affect earlier data
34:10 Q&A: Discussion based on audience questions
43:56 Outline of next month's presentation by Mike Wolfe - Streamline Your Import/Export Spec Workflow with VBA Classes
48:20 Back to John Heaser
4 9:00 Meeting aftermath
49:35 Forthcoming Access Europe events up to May 2024
51:40 Conclusion