Editing Geodatabase attributes with ESRI OLE DB provider

Description:

You can edit Geodatabase, shapefile and coverage attributes directly by using Microsoft®'s ActiveX® Data Objects (ADO) and the ESRI OLE DB provider. The following examples illustrate how to perform edits in a Microsoft Visual Basic® environment.

How to use:
  1. Creating a new folder--OLEDBTest for a new Visual Basic project.
  2. Start Visual Basic 6.0 and create a new standard EXE project.
  3. Change the project name, form name and form caption settings to whatever is appropriate for your data/application.
  4. Choose Project | Components from the main menu to display the Components dialog.
  5. Navigate the controls list box until you find the Microsoft Hierarchical FlexGrid Control 6.0 item. Add this control to the project by clicking the check box to the left of the item label.
  6. Click OK to close the Components dialog and commit your changes to the project.
  7. Double-click MSHFlexGrid icon from toolbox to add this grid control to the form and adjust the boundary of this control if necessary.
  8. Double-click CommandButton icon from the toolbox to add a command button to the form, adjust the location of the command button and change the caption property to "Query". Add another CommandButton to the form and set it's caption to "Update".
  9. Choose Project | References from the main menu to display the References dialog.
  10. Navigate the Available References list box until you find the Microsoft ActiveX Data Objects 2.5 Library. Add this reference to the project by clicking the check box to the left of the item label.
  11. Click OK to close the References dialog and commit your changes to the project. Referencing ADO in your project allows you access to all ADO methods and properties.
  12. Paste the following code into the general declarations section of your form.

    NOTE: You will have to change the VB constant, 'sAccessWksFact', to point to your own data source and you may wish to create a back-up copy of your data prior to editing.

    Example 1# - Updating recordset column values.
    Option Explicit
    Dim pAdoCon As ADODB.Connection
    Dim pAdoRs As ADODB.Recordset
    Dim strSQL As String, AdoConStr As String
    Const sAccessWksFact = "C:\ArcGIS\arcexe83\ArcObjects Developer Kit\Samples\Data\Usa\usa.mdb"
    
    Private Sub Command1_Click()
    Me.MousePointer = vbHourglass
    
    '++ Create ADO connection and recordset objects
    Set pAdoCon = New ADODB.Connection
    Set pAdoRs = New ADODB.Recordset
    
    '++ specify ESRI OLE DB as the provider
    AdoConStr = "Provider=ESRI.GeoDB.OLEDB.1;" & _
        "Data Source=" & sAccessWksFact & ";" & _ 
        "Extended Properties=workspacetype=esriCore.AccessWorkspaceFactory.1;Geometry=WKB"
    
    '++ Open the connection to the data source
    pAdoCon.Open AdoConStr
    
    '++ sql string for recordset
    strSQL = "Select * from states where Sub_Region = 'N Eng'"
    
    '++ Open the recordset
    pAdoRs.Open strSQL, pAdoCon, adOpenForwardOnly, adLockOptimistic
    
    '++ Populate the MSHflexgrid control with the recordset
    Set MSHFlexGrid1.Recordset = pAdoRs
    Me.MousePointer = vbNormal
    End Sub
    
    Private Sub Command2_Click()
    
    Dim newstr As String
    newstr = InputBox("Input new State_name value")
    
    Me.MousePointer = vbHourglass
    
    '++ Begin an edit transaction
    pAdoCon.BeginTrans
    
    '++ Update recordset with the new state name
    pAdoRs.MoveFirst
    Do While Not pAdoRs.EOF
        pAdoRs!STATE_NAME = Trim(newstr)
        pAdoRs.Update
        pAdoRs.MoveNext
    Loop
    
    '++ Commit the changes
    pAdoCon.CommitTrans
    
    pAdoRs.Requery
    
    '++ Re-apply the recodset to the grid control
    MSHFlexGrid1.Clear
    Set MSHFlexGrid1.Recordset = pAdoRs
    
    Me.MousePointer = vbNormal
    End Sub
    
  13. Save the project by choosing File | Save Project.
  14. Build the project by choosing File | Make OLEDBEdit_Test.exe.
  15. Run OLEDBTest.exe. Note:

    For ESRI Shapefiles, Coverages, and Geodatabase connection strings, please refer to the "Browsing a Geodatabase with ESRI OLE DB Provider" document.

    Example 2# - Updating individual field values in an ADO recordset.

    For this example you will need to create a new VB project in the same manner as described in example 1#. Add an additional CommandButton (set the caption to "restore") and a textbox called "txtcell" (remove any text from the textbox). The new restore option will refresh the recordset following a failed update attempt or if you opt not to proceed with the text edit. The textbox will be used as a "text mask" for applying edits to the recordset as the MS Hierarchical FlexGrid control is a read-only data grid. Set the background colour of the textbox to be something other than the same colour as the MSHFlexGrid background control, to make it stand out against the grid. Click on the field you wish to change and enter the new value over the text mask.

    Paste the following code into the general declarations section of your form.

    NOTE: You will have to change the VB constant 'sAccessWksFact' to point to your own data source and again, you may wish to make a back-up copy of your data prior to editing.

    Option Explicit
    Dim pAdoCon As ADODB.Connection
    Dim pAdoRS As ADODB.Recordset
    Dim m_lCurRow As Long
    Dim m_lCurCol As Long
    Dim m_lOrgColor As Long
    Dim m_FieldChanged As Boolean
    Dim SelStart As Long
    
    Const CHANGED_CELL = vbCyan
    Const UPDATE_PASS = vbGreen
    Const UPDATE_FAIL = vbRed
    Const sAccessWksFact = "C:\ArcGIS\arcexe83\ArcObjects Developer Kit\Samples\Data\Usa\usa.mdb"
    
    
    Private Sub Command1_Click()
    Dim AdoConStr As String
    Dim strSQL As String
      
      Me.MousePointer = vbHourglass
      On Error Resume Next
      MSHFlexGrid1.Clear
      
      '++ Create ADO connection and recordset objects
      Set pAdoCon = New ADODB.Connection
      Set pAdoRS = New ADODB.Recordset
    
      '++ specify ESRI OLEDB as provider
      AdoConStr = "Provider=ESRI.GeoDB.OLEDB.1;" & _ 
      "Data Source=" & sAccessWksFact & ";" & _
      "Extended Properties=workspacetype=esriCore.AccessWorkspaceFactory.1"
       
      '++ Open the connection
      pAdoCon.Open AdoConStr
    
      '++ sql string for recordset
      strSQL = "Select * from states"
    
      '++ Open the recordset
      pAdoRS.Open strSQL, pAdoCon, adOpenForwardOnly, adLockOptimistic
      
      '++ Populate the grid object
      Set MSHFlexGrid1.Recordset = pAdoRS
      
      Me.MousePointer = vbNormal
      
    End Sub
    
    Private Sub Command2_Click()
    Dim sFieldNm As String
    Dim sFieldData As Variant
    Dim i As Integer
    
      sFieldNm = pAdoRS.Fields.Item(m_lCurCol).Name
      sFieldData = MSHFlexGrid1.TextMatrix(m_lCurRow, m_lCurCol)
     
      '++ Position the recordset cursor at the current, user selected record
      pAdoRS.MoveFirst
      For i = 1 To MSHFlexGrid1.Row - 1
        pAdoRS.MoveNext
      Next
        
      '++ Individual cell update for recordset
      pAdoRS.Update sFieldNm, sFieldData
     
      m_FieldChanged = False
      Command2.Enabled = False
      
      '++ Set the cell back to original colour and reset font
      With MSHFlexGrid1
          .Row = m_lCurRow
          .Col = m_lCurCol
          .CellBackColor = m_lOrgColor
          .CellFontBold = False
      End With
      
      MSHFlexGrid1.Enabled = True
      '++ Refresh the recordset
      pAdoRS.Requery
      Set MSHFlexGrid1.Recordset = pAdoRS
      pAdoRS.MoveFirst
      
      Exit Sub
      
    Error_Handle:
      MsgBox "Error Number " & Err.Number & vbNewLine & Err.Description
      m_FieldChanged = False
      '++ Highlight the failed cell edit in red
      With MSHFlexGrid1
          .Row = m_lCurRow
          .Col = m_lCurCol
          .CellBackColor = UPDATE_FAIL
          .CellFontBold = False
          .Enabled = True
          .TextMatrix(m_lCurRow, m_lCurCol) = pAdoRs.Fields(sFieldNm).Value
      End With
      Command3.Enabled = True
      Command2.Enabled = False
        
    End Sub
    
    
    Private Sub Form_Load()
      '++ Hide the text editing mask at start-up
      TxtCell.Visible = False 
    End Sub
    
    Private Sub MSHFlexGrid1_Click()
      If MSHFlexGrid1.RowSel = 0 Then
       Exit Sub
      End If
      
     '++ Capture the currently selected MSHFlexGrid cell 
      m_lCurRow = MSHFlexGrid1.Row
      m_lCurCol = MSHFlexGrid1.Col
     
     '++ Set the cell 'mask' to match the size and pos of the current cell
     '++ Set the text for the cell 'mask' to match the text of the current cell
    
      With TxtCell
        .Top = MSHFlexGrid1.CellTop + MSHFlexGrid1.Top
        .Left = MSHFlexGrid1.CellLeft + MSHFlexGrid1.Left
        .Height = MSHFlexGrid1.CellHeight
        .Width = MSHFlexGrid1.CellWidth
        .Visible = True
        .Text = MSHFlexGrid1.TextMatrix(m_lCurRow, m_lCurCol)
        .SetFocus
        .SelStart = 0
        .SelLength = Len(TxtCell.Text)
      End With
     
    End Sub
    
    Private Sub Command3_Click()
     '++ Requery the recordset and refresh the MSHFlexGrid object
     MSHFlexGrid1.Clear
     pAdoRS.Requery
     Set MSHFlexGrid1.Recordset = pAdoRS
     Command2.Enabled = True
     MSHFlexGrid1.Enabled = True
    End Sub
    
    Private Sub txtCell_KeyPress(KeyAscii As Integer)
     '++ Record the update event
      m_FieldChanged = True
    End Sub
    
    Private Sub txtCell_LostFocus()
      If m_FieldChanged Then
       Command2.Enabled = True
        '++ Set the MSHFlexGrid text to match the cell 'mask' text
        With MSHFlexGrid1
           .TextMatrix(m_lCurRow, m_lCurCol) = TxtCell.Text
            m_lOrgColor = .CellBackColor
           .Row = m_lCurRow
           .Col = m_lCurCol
           .CellBackColor = CHANGED_CELL
           .CellFontBold = True
        End With
      
        TxtCell.Visible = False
        MSHFlexGrid1.Enabled = False
      Else
        TxtCell.Visible = False
      End If
    
    End Sub
    

Application:


Requires: Arc 8 DeskTop, Visual Basic 6.0 and a personal Geodatabase (Access) .mdb file

Difficulty: Intermediate