I'm working thru migrating VBA code to VB.net. I've hit this module which uses an ADODB connection to an access 2000.mdb. The ADODB is marked as an error and the visual studio suggestion was to change to an Odbc connection. Is that the way to go or should an OLEDB connection be used. Is the general logic in the sub connectAdo() going to work with either connection or do workspace factories and such need to be setup. New to arcobject & vs10 coding.
Thanks for any hints........Scott
Module UpDateXY
' Public Sub UpDateXY()
Dim m_pMouseCursor As IMouseCursor
Public Function getX(strNumber) As Double
' Function getX(strNumber) As Double
Dim pMxDoc As IMxDocument
pMxDoc = My.ArcMap.Document
Dim pMap As IMap = Nothing
pMap = pMxDoc.FocusMap
Dim pLayer As ILayer
'This allows the layer "surveys" to be in any position in the legend
Dim pSurveyLayer As IFeatureSelection = Nothing
Dim i As Integer
For i = 0 To pMap.LayerCount - 1 'the For statement must be from one value to another
pLayer = pMap.Layer(i)
If pLayer.Name = "copy" Then
pSurveyLayer = pLayer
Exit For
End If
Next
'create query filter to find the survey with the Number equal to strNumber
Dim strQuery As String
strQuery = "NUMBER = '" & strNumber & "'"
Dim pFilter As IQueryFilter
pFilter = New QueryFilter
pFilter.WhereClause = strQuery
Dim pFeatureCursor As IFeatureCursor = Nothing
'select the survey as a a new selection
pSurveyLayer.SelectFeatures(pFilter, esriSelectionResultEnum.esriSelectionResultNew, False)
pSurveyLayer.SelectionSet.Search(Nothing, False, pFeatureCursor)
If pSurveyLayer.SelectionSet.Count = "0" Then
getX = "0"
Else
Dim pFeature As IFeature
pFeature = pFeatureCursor.NextFeature
getX = pFeature.Value(pFeature.Fields.FindField("X_COORD"))
'pSurveyLayer.Clear
End If
End Function
Public Function getY(strNumber) As Double
Dim pMxDoc As IMxDocument
pMxDoc = My.ArcMap.Document
Dim pMap As IMap
pMap = pMxDoc.FocusMap
Dim pLayer As ILayer
'This allows the layer "Surveys" to be in any position in the legend
Dim pSurveyLayer As IFeatureSelection = Nothing
Dim i As Integer
For i = 0 To pMap.LayerCount - 1 'the For statement must be from one value to another
pLayer = pMap.Layer(i)
If pLayer.Name = "copy" Then
pSurveyLayer = pLayer
Exit For
End If
Next
'create query filter to find the survey with the NUMBER equal to strNumber
Dim strQuery As String
strQuery = "NUMBER = '" & strNumber & "'"
Dim pFilter As IQueryFilter
pFilter = New QueryFilter
pFilter.WhereClause = strQuery
Dim pFeatureCursor As IFeatureCursor = Nothing
'select the survey as a a new selection
pSurveyLayer.SelectFeatures(pFilter, esriSelectionResultEnum.esriSelectionResultNew, False)
pSurveyLayer.SelectionSet.Search(Nothing, False, pFeatureCursor)
If pSurveyLayer.SelectionSet.Count = "0" Then
getY = "0"
Else
Dim pFeature As IFeature
pFeature = pFeatureCursor.NextFeature
getY = pFeature.Value(pFeature.Fields.FindField("Y_COORD"))
'pSurveyLayer.Clear
End If
End Function
Sub connectAdo()
'set connection
Call BusyMouse(True)
Dim cnn As ADODB.Connection
Dim strNumber As String
cnn = New ADODB.Connection
Dim strPath As String
strPath = "C:\Survey_Editor\LINNDT2000.mdb"
'strPath = "C:\Survey_Editor\LINNDT2000.accdb"
Dim strCn As String
strCn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath & ";Persist Security Info=False"
'open connection
cnn.Open(strCn)
'set Survey_Main as a recordset
Dim rs As ADODB.RecordSet
rs = New ADODB.RecordSet
rs.Open("Select * from Survey_Main", cnn, adOpenKeyset, adLockOptimistic)
rs.MoveFirst()
'loop through records, setting x and y coords from the shapefile. This may take an hour or more.
Do While Not rs.EOF
If rs.Fields.Item("x_coord") = 0 Then
strNumber = rs.Fields.Item("Number")
rs.Fields.Item("x_coord") = getX(strNumber)
rs.Fields.Item("y_coord") = getY(strNumber)
End If
rs.Update()
rs.MoveNext()
Loop
rs.Close()
Call BusyMouse(False)
End Sub