Hey awesome people!
I'm working in a database of GIS data (a large "big data"-database), currently trying to determine why our front-end Access database used by the Coders is experiencing ODBC call failures when accessing data from an SQL back-end database housing the data.
One possible reason for the ODBC call failures is that there is malformed data in one of the tables, the SQL server can handle the data formats since it doesn't care, but Access is touchy..
We had new computers as of a month ago, and an upgraded fiber-optic connection network-wide so I am hoping it is not a physical issue of the data not being throughput.
Anyways- assuming malformed data in a table, we found the most likely suspect as one table which is always being called. I started looking for the prime candidate (from past experience), which is malformed latitude longitudes in the lat/long field. I.e. the data should be in the format like "44.2716497,-78.1696004" aka "##?#####??##?######" or:
2 numbers, a period, more numbers, a comma a dash and 2 numbers, a period, more numbers.
So, with some help from you amazing people months ago, I cobbled together this code which looks for latlong fields that contain anything other than numbers/commmas/dots/dashes:
Code:
Function numcommadot2(sIn As String) As String
Dim c As String, i As Integer
On Error GoTo numcommadot_Error
sIn = Trim(sIn)
Dim sHold As String
For i = 1 To Len(sIn)
c = Mid(sIn, i, 1)
If InStr("0123456789-,.", c) <> 0 Then
sHold = sHold & c
Else
End If
Next
On Error GoTo 0
numcommadot2 = sHold
Exit Function
numcommadot_Error:
MsgBox "Error " & Err.Number & " in line " & Erl & " (" & Err.Description & ") in procedure numcommadot of Module AWF_Related"
End Function
Public Sub DelNonPrintable()
Dim r As DAO.Recordset
Dim sSQL As String
Dim sTmp As String
sSQL = "SELECT DestLat FROM tbl_2016_XXX_Trips"
Set r = CurrentDb.OpenRecordset(sSQL)
If Not r.BOF And Not r.EOF Then
r.MoveLast
r.MoveFirst
Do While Not r.EOF
sTmp = vbNullString
sTmp = r("DestLat") & ""
sTmp = numcommadot2(sTmp)
r.Edit
r("DestLat") = sTmp
r.Update
r.MoveNext
Loop
End If
End Sub
I am getting a data type conversion error at the red text - I have NO idea why. It is a string already defined as such..
So- does anyone know why this is failing??
Also, is there a better way to do this?? To make a table of all the latlong fields with invalid characters?
Thanks in advance, you guys are always great!