Microsoft Access e OpenStreetMap

Vorrei vedere dove si trovano i miei clienti utilizzando Microsoft Access e OpenStreetMap.

Ingredienti necessari:

1) una tabella con i dati dei clienti e le coordinate geografiche (latitudine e longitudine)

2) una maschera, che ho chiamato MappaClienti, con un controllo WebBrowser (che ho chiamato WebBrowser0)

3) un file HTML che sarà visualizzato all’interno del controllo WebBrowser.

Il file si chiama mappa.html e contiene il seguente codice:

<!DOCTYPE HTML>
<html lang="en">
 <head>
 <meta charset="utf-8" />
 <meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=no">
 <script src="http://cdn.leafletjs.com/leaflet/v0.7.7/leaflet.js"></script>
 <link rel="stylesheet" href="http://cdn.leafletjs.com/leaflet/v0.7.7/leaflet.css" />
 <style>
 html, body {
 height: 100%;
 padding: 0;
 margin: 0;
 overflow:hidden;
 }
 #map {
 /* configure the size of the map */
 width: 100%;
 height: 100%;
 }
 </style>
 </head>
 <body>
 <div id="map"></div>
 <script type ="text/javascript" src="mapSettings.js"></script>
 </body>
</html>

Nota che la pagina HTML ha un riferimento esterno al file mapSettings.js. Questo file sarà creato dinamicamente via maschera e conterrà le coordinate dei marker che vogliamo visualizzare sulla mappa OpenStreetMap.

Il file mappa.html deve essere salvato nella stessa cartella in cui si trova il database Access.

4) per caricare la mappa.html dentro il controllo WebBrowser usiamo questo codice nell’evento Su Caricamento:

Private Sub Form_Load()
Dim strURL As String

' creo i file necessari per visualizzare la mappa
sPrepareMap 
strURL = "https://www.openstreetmap.org/#map=18/45.47043/9.179279"
Me.WebBrowser0.ControlSource = "=""file://127.0.0.1/c$/" & Mid(CurrentProject.Path, 4) & "/mappa.html"""
End Sub

La procedura sPrepareMap sarà spiegata più avanti e serve per preparare i file necessari alla visualizzazione dei marker dei clienti sulla mappa.

La mappa viene centrata in base alle coordinate che sono “passate” qui:

https://www.openstreetmap.org/#map=18/45.47043/9.179279

Il valore 18 indica il livello di zoom a cui viene visualizzata la mappa.

Le coordinate inserite sopra sono quelle del Duomo di Milano.

Per caricare la pagina HTML nel WebBrowser impostiamo l’origine controllo della maschera al file mappa.html che si trova su disco:

Me.WebBrowser0.ControlSource = “=””file://127.0.0.1/c$/” & Mid(CurrentProject.Path, 4) & “/mappa.html”””

Nota bene. L’istruzione prevede che il nostro database sia salvato sul disco C.

5) Ora la parte più elaborata: generare dinamicamente il file mapSettings.js.

Il file va creato dinamicamente ogni volta, perché presumibilmente vogliamo visualizzare sulla mappa i marker dei clienti che abbiamo ricercato nel nostro database (per es. tutti i clienti di Milano o tutti i clienti che non hanno effettuato ordini nell’ultimo trimestre o altro ancora…) o di quello correntemente visualizzato.

a) aggiungiamo un modulo: basOpenStreetMap (dal Visual Basic Editor: Inserisci-Modulo) in cui creiamo una serie di procedure.

b) la procedura fondamentale è sPrepareMap:

Sub sPrepareMap()
Dim strFile As String
strFile = Application.CurrentProject.Path & "\mapSettings.js"
' creo il file vuoto
sCreateFile strFile
' scrivo le righe standard. qui non ci sono ancora indicazioni su dove saranno visualizzati i marker
sWriteLine strFile, "// initialize Leaflet"
' la mappa viene centrata su queste coordinate
sWriteLine strFile, "var map = L.map('map').setView({lon: 9.179279, lat: 45.47043}, 15);"
sWriteLine strFile, "// add the OpenStreetMap tiles"
sWriteLine strFile, "L.tileLayer('https://{s}.tile.openstreetmap.org/{z}/{x}/{y}.png', {"
sWriteLine strFile, "maxZoom: 19,"
sWriteLine strFile, "attribution: '&copy; <a href=https://openstreetmap.org/copyright>OpenStreetMap contributors</a>'"
sWriteLine strFile, "}).addTo(map);"
sWriteLine strFile, "// show the scale bar on the lower left corner"
sWriteLine strFile, "L.control.scale().addTo(map);"
' aggiungo i markers sulla mappa
sAddMarkers strFile
End Sub

La procedura sPrepareMap richiama tre procedure:

sCreateFile. La uso per creare il file mapSettings.js vuoto

Sub sCreateFile(strFile)
Dim fso, a
Set fso = CreateObject("Scripting.FileSystemObject")
Set a = fso.CreateTextFile(strFile, True)
a.Close
Set fso = Nothing
End Sub

sWriteLine. La uso per scrivere il contenuto del file mapSettings.js

Sub sWriteLine(strFile As String, strV As String)
Dim fso, a
Set fso = CreateObject("Scripting.FileSystemObject")
Set a = fso.OpenTextFile(strFile, 8) ' 8 è la costante per aggiungere dati a un file esistente
a.WriteLine strV
a.Close
Set fso = Nothing
End Sub

La procedura sWriteLine ha due argomenti: strFile, il file in cui deve scrivere, strV il valore da scrivere (che sarà una nuova riga nel file)

sAddMarkers. La uso per scrivere i dati relativi ai marker che voglio visualizzare sulla mappa

Sub sAddMarkers(strFile As String)
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT Cliente, Longitudine, Latitudine FROM tblClienti", , dbReadOnly)
Do While Not rs.EOF
If rs!Longitudine > 0 And rs!latitudine > 0 Then
' solo se esistono sia longitudine sia latitudine
sWriteLine strFile, "L.marker({lon:" & Replace(rs!Longitudine, ",", ".") & ", lat:" & Replace(rs!latitudine, ",", ".") & "}).bindPopup(""" & rs!Cliente & """).addTo(map);"
'bindPopup imposta l'etichetta da visualizzare quando si fa clic sul marker
End If
' vado al record successivo
rs.MoveNext
Loop
Set rs = Nothing
End Sub

9.1.2020: ho modificato il codice qui sopra per gestire i clienti che hanno un apostrofo nella ragione sociale. Grazie a Bruno Marton, che mi ha segnalato il bug.

I dati che carico nel recordset dipendono da questa istruzione:

Set rs = CurrentDb.OpenRecordset(“SELECT Cliente, Longitudine, Latitudine FROM tblClienti”, , dbReadOnly)

Si tratta di tutta la tabella dei miei clienti. E’ possibile naturalmente filtrare i dati in base all’esito di una ricerca o al record corrente ecc.

E’ possibile arricchire e modificare ulteriormente la mappa. Vedi Leaflet an open-source JavaScript library
for mobile-friendly interactive maps da cui ho tratto l’esempio di codice.

Alla fine del procedimento, dopo aver aperto la maschera MappaClienti ameno una volta, i file su disco saranno questi:

Ora abbiamo un punto di partenza per integrare Microsoft Access e OpenStreetMap. Buon lavoro!