1

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:

  1. [tbl Track] with metadata describing the track (e.g. distance, start date and time, duration, maximum elevation etc.).
  2. [tbl TrackGeo] with the tracks themselves in geographical terms. Attributes:

    ID (autonum, PK)

    TrackID (long, FK to [tbl Track])

    LatWGS84 (double)

    LonWGS84 (double)

Moving [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?

  • I believe the SQL Server Import and Export Wizard shall be enough for you to accomplish this task properly. Have you seen it? – Ronaldo May 2 at 12:37
  • Yes, I know it. But can such a move be done programmatically with for instance VBA? – SWP_IB May 2 at 13:25
  • 1
    Start with a straight lift-and-shift to SQL Server, perhaps using SSMA for Access. microsoft.com/en-us/download/details.aspx?id=54255 Then you can start using the SQL Server geography types and functions. Note if you're still using Access as a Front-End you'll need to use Views or Passthrough queries. – David Browne - Microsoft May 2 at 16:56
0

I would use VBA to construct & export your spatial data into the Well Known Text (WKT) format. SQL Server supports importing data from this format & as it is text based it is fairly easy syntactically.

| improve this answer | |
  • Thanks. Some of my tracks have more than 10000 points. Does SQL Server support that if I put such a track in one geography attribute, type linestring? – SWP_IB May 4 at 9:00
  • can't find any published limits but that is not a lot of nodes so don´t think there will be a problem – Mo64 May 5 at 7:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.