Access Europe – John Heaser (Extending Access with SQL Server – including Geography data)

Please join us for our monthly Access Europe session on Wed 1 Nov 2023 at 18:00 (6:00PM) London time (UTC)

Please note that the UK and Europe revert 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

———————————————————————————————————————————————————-

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 –
    • eg find the Local Government Region for a location where you know the Lat/Lng (Lat/Lng can be found from PostCode)
  • Find the points that lie within a radius of a starting point and order them by distance –
    • Eg find the Stores that lie within 50 miles of a Customer and show them in ascending order of distance

This is very useful and SQL 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 and 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.

Lat/Long locations often have to be calculated from Grid References or Easting/Northings and you have to be mindful of the World Geodetic System being used – eg 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, we will touch on that process . . .

Do join us

Bio:

This session will be 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 package software and to provide bespoke programs where packages don’t provide the whole solution.

The company website is https://heaser.co.uk

======================================================================

IMPORTANT NOTE

All sessions will be recorded and later uploaded to the AccessUserGroups channel on YouTubeAccessUserGroups.org – YouTube.

If you do not wish to be part of the recording, please ensure your webcam and microphone are both switched off.

======================================================================

We use Zoom as our meeting application.

Please click the link below to download Zoom, if you don’t already have it.

Get Zoom

To join our meetings, please use the following link or phone number

Join Zoom Meeting

If you are asked:

Meeting ID: 924 3129 5683
Passcode: 661210

Dial by your location:
+1 669 900 9128 US (San Jose)
+1 253 215 8782 US (Tacoma)
+1 301 715 8592 US (Washington DC)
+1 312 626 6799 US (Chicago)
+1 346 248 7799 US (Houston)
+1 646 558 8656 US (New York)
+43 12 535 502 Austria
+32 1579 5132 Belgium
+359 3 257 1633 Bulgaria
+385 1300 0988 Croatia
+420 5 3889 0161 Czech Republic
+45 32 70 12 06 Denmark
+358 9 7252 2471 Finland
+33 1 7037 9729 France
+49 69 7104 9922 Germany
+30 211 198 4488 Greece
+36 1 779 9126 Hungary
+353 1 653 3898 Ireland
+39 069 480 6488 Italy
+352 2786 4277 Luxembourg
+356 2778 1288 Malta
+31 20 241 0288 Netherlands

+48 22 307 3488 Poland
+351 308 810 988 Portugal
+40 31 630 1088 Romania
+421 233 418 515 Slovakia
+386 1600 3102 Slovenia
+34 917 873 431 Spain
+41 43 210 71 08 Switzerland
+46 850 539 728 Sweden
+44 203 481 5240 United Kingdom
+44 208 080 6591 United Kingdom

Meeting ID: 924 3129 5683
Passcode: 661210
Find your local number: https://zoom.us/u/a4d0JdR88