AEU Meeting Date 1 Nov 2023 starting at 18:00 UK Time (UTC) Page Last Updated 2 Nov 2023
Meeting Details:
This month's Access Europe meeting was on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM)
Please note that the UK and Europe reverted to winter time on Sunday 29 Oct, whilst the USA remains on Daylight Savings until mid November.
The start time for this meeting is equivalent to 19:00 (7PM) in Central Europe and (for this month only) 11AM in Seattle / PST
For all local times, please check World Time Buddy
We use Zoom as our meeting application. Please click this link to Get Zoom if you don’t already have it.
When the time comes for the meeting, please use the following link: Join Zoom Meeting
If you are asked, use: Meeting ID: 924 3129 5683 ; Passcode: 661210
For more connection options, please see the AccessUserGroups.org web page for this event
All sessions are recorded and later uploaded to the Access User Groups channel on YouTube
If you do not wish to be part of the recording, please ensure your webcam and microphone are both switched off during the meeting.
This session was led by John Heaser who is an experienced Access developer based in the U.K.
John founded his own company, Heaser Business Consulting (HBC) in 2003.
HBC works with SMEs to help them get the best from packaged software and to provide bespoke programs where packages don't provide the whole solution.
The company website is https://heaser.co.uk
Topic outline:
SQL Server has a Geography data type that 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
We will look at how these SQL skills can be used to bring the benefits of Geography data types to Access.
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.
NOTE:
Lat/Long locations often have to be calculated from Grid References or Eastings/Northings and you have to be mindful of the World Geodetic System being used - e.g. WGS84 otherwise errors in location will be introduced.
There is a lot of data freely available from https://geoportal.statistics.gov.uk/ but some of it will require conversion before it can be used in SQL Server.
We will touch on that process . . .
NOTE:
See the official Access Europe web page for additional information about both this and all other Access Europe events
Download:
Click to download a PDF of the presentation used in this session together with the ACCDB FE database and example SQL scripts:
AEU_2023_11 Approx 1.6 MB (zipped)
NOTE:
The SQL Azure backend database is available to download from Google Drive Approx 172 MB (zipped)
Video:
The meeting was recorded and the video is already available on the Access Europe YouTube channel at https://youtu.be/UMbe0SsqrfM or you can click below:
Many thanks to my colleague Alessandro Grimaldi for editing the video so promptly.
Colin Riddington Mendip Data Systems Last Updated 2 Nov 2023
|
Return to Access Europe Page
|
Return to Top
|