For 20 years I've been working with MS Access, with much VBA involved. I have a lot of geographical data in Access and I'm preparing to move to SQL Server because it supports geographical data types, and because it has a good connection with QGIS, in which I want to show and query my geographical data efficiently in a map.
Basically, my geographical data in Access comprise my GPS tracks that I've collected in 20 years, altogether about 5000 tracks with an average 800 points per track. In Access I have two tables for them:
[tbl Track]with metadata describing the track (e.g. distance, start date and time, duration, maximum elevation etc.).
[tbl TrackGeo]with the tracks themselves in geographical terms. Attributes:
ID (autonum, PK)
TrackID (long, FK to [tbl Track])
[tbl Track] to SQL Server is easy, but it gets difficult for me (newbie in SQL Server and its geographical data types!) to move the geograhical data to SQL Server in a spatial data field.
My idea is:
Put the data of
[tbl TrackGeo] per track in a geography spatial data field (type linestring) in
[tbl Track] in SQL Server.
My question is: What is the best approach to accomplish this, preferably (if possible) using VBA from within MS Access?