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