Replacing ADODB connection when migrating VBA to VS10

[scottvalentine]

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

[DuncanHornby]

Scott,

Looking at your code I noticed you commented out an Access 2007 (accdb) database. Has your data migrated into this format?

If it has or it will then its worth reading this page.

Duncan