Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2017
    Posts
    12

    Syntax error with SQL INSERT action


    Hello all.

    After much Googling and an above average amount of coffee, I am still completely stumped with this code. My issue is every time I step through, as soon as it gets to the line where the SQL is executed, I get:

    Run-time error '-2147217900 (80040e14): Syntax error (missing operator) in query expression 'Geography::Point(<actual value>, <another value>, 4326);.
    In the Locals window, the SQL statement is 100% correct, and runs fine in SSMS. I've tried with both the "CurrentProject.Connection.Execute" and also the "CurrentDb.Execute" commands, however, I get the same error. The DCount statement DOES work, as the loop is skipping the first two records in the recordset (which are already in the server). That tells me connection is there, and data is moving through. Can anyone tell my why the INSERT function is not working in my case?

    Code is:
    Code:
    Sub InsertGeolocate()
    Dim RSGeo As Object
    Dim SQLString As String
    Dim CustID As String
    Dim GeoLong As String
    Dim GeoLat As String
    Dim i As Integer
    
            Set RSGeo = CreateObject("ADODB.Recordset")
                With RSGeo
                    .Open "SELECT CustomerID, Latitude, Longitude FROM [0-TempGeo] ORDER BY CustomerID;", CurrentProject.Connection, adOpenStatic
                    If Not .BOF And Not .EOF Then
                        .MoveFirst
                        For i = 1 To .RecordCount
                            If DCount("*", "dbo_tblCustomersGeoLocation", "CustomerID = '" & .Fields("CustomerID") & "'") = 0 Then
                                    CustID = .Fields("CustomerID")
                                    GeoLong = .Fields("Longitude")
                                    GeoLat = .Fields("Latitude")
                                    SQLString = "INSERT INTO dbo_tblCustomersGeoLocation(CustomerID, GeoLocation) values ('" & CustID & "', Geography::Point(" & GeoLat & ", " & GeoLong & ", 4326));"
    '                                CurrentProject.Connection.Execute SQLString
    '                                CurrentDb.Execute SQLString
                                    .MoveNext
                                    Else
                                    .MoveNext
                            End If
                         Next i
                        End If
                    .Close
                End With
            Set RSGeo = Nothing
            Debug.Print "Ended."
    End Sub

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,470
    CustID and CustomerID are text fields?

    Why does function have colon characters? I've never seen this.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Join Date
    Mar 2017
    Posts
    12
    Quote Originally Posted by June7 View Post
    CustID and CustomerID are text fields?
    Yes they are both strings.
    Why does function have colon characters? I've never seen this.
    The SQL string inserts the CustomerID and GeoLocation into a table. Geography is entered differently to other fields as per the information found at https://stackoverflow.com/questions/...aphy-data-type

    Either way, if I copy/paste the SQL string straight from the Locals window to Microsoft SQL Management Studio, the query runs perfectly. For some reason, it's not executing in Access.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,470
    Well, Access does not have Geography data type so I very much doubt Access query engine can recognize this structure. Perhaps Access can run SQLServer passthrough query or stored procedure. I don't use SQLServer so can't really confirm this would resolve issue.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Join Date
    Mar 2017
    Posts
    12
    Quote Originally Posted by June7 View Post
    Well, Access does not have Geography data type so I very much doubt Access query engine can recognize this structure. Perhaps Access can run SQLServer passthrough query or stored procedure. I don't use SQLServer so can't really confirm this would resolve issue.
    Well that explains it!

    I did some Googling, but there was no information on Access not supporting the Geography data type.

  6. #6
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    7,114
    I went looking for this because I recalled Colin getting somewhere with it. Make sure you get as far as post 11.
    Not sure if it will help but there you go...
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,470
    There's no such data type option available when building an Access table which infers Access cannot support, supported by query failure in Access but success with SSMS.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Syntax Error In Insert Into
    By Eranka in forum Access
    Replies: 5
    Last Post: 11-05-2019, 02:43 AM
  2. Replies: 4
    Last Post: 03-26-2019, 02:40 PM
  3. syntax error in insert into
    By rameshjctr in forum Forms
    Replies: 5
    Last Post: 02-05-2016, 05:41 AM
  4. Replies: 3
    Last Post: 03-05-2013, 11:17 AM
  5. INSERT INTO Syntax Error
    By eww in forum Programming
    Replies: 1
    Last Post: 04-06-2011, 10:28 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums