Beyond Excel: VBA and Database Manipulation

May 12 2010   7:47PM GMT

Building a Library of Routines for Updating – #5

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

You are here (click to enlarge)

You are here (click to enlarge)

We’re still building the routines necessary to update a database from entries in Excel.  The thumbnail at right is a simple data flow diagram that shows how these routines fit together.  Click on it to enlarge it.  We’ve been working from the bottom up and now we are on what this diagram has as step 8 – Update_Entry.  

Update_Entry updates a single record.  Its parent handles cycling through all Excel entries, passing them one by one to Update_Entry.  We will see that routine in the next post.  Here is the code for Update_Entry.

Public Function Update_Entry(sConnect As String, sWorksheet As String, _
                             sData As String, sFields As String, _
                             sPath As String, lRow As Long, _
                             iACD As Integer, iNotes As Integer, _
                             Optional cn As ADODB.Connection) As Boolean
'   Description:Update a single entry.  Entries MUST be verfied correct
'               before calling this routine.
'   Parameters: sConnect    ODBC Connection String. Leave blank if the
'                           Connection Object (see cn) is created outside
'                           this routine.
'               sWorksheet  Worksheet's name that contains the data
'               sData       Range's name containing the unique rows of data
'               sFields     Range's name containing field descriptions
'               sPath       Table with path (if needed) to update
'               lRow        XL row containing data to be updated/inserted
'               iACD        XL column for ACD entries (Add,Change,Delete)
'               iNotes      XL column to post error messages
'               cn          Persistent connection object.  Use for speedy
'                           updating of multiple records.
'   Example:    bResult = Update_Entry(sConnectionString, _
'                                      "Data", _
'                                      "Data", _
'                                      "Fields", _
'                                      sUpdateTable, _
'                                      lCurrentRow, _
'                                      FieldColumn("ACD","Data"),
'                                      FieldColumn("ERRORS","Data"),
'                                      cn)
'     Date   Init Modification
'   01/12/06 CWH  Initial Programming
    On Error GoTo ErrHandler
    Update_Entry = Failure      'Assume the worse
      
    Settings "Save"             'Save current application settings
    Settings "Disable"          'Disable events, calcs, screen updates
       
    Dim lCol As Long            'Current Column
    Dim sSQL As String          'SQL string
    Dim sCRTB As String         'Carriage Return and two Tabs
    Dim bOpen As Boolean        'Was Connection open when routine called?
           
    sCRTB = vbCr & vbTab & vbTab
   
    'If the Connection wasn't created by the calling routine, make one
    bOpen = Not cn Is Nothing
    If Not bOpen Then
        If SQLConnection(cn, sConnect) = Failure Then Exit Function
    End If
   
    With Range(sData)
              
        'Check Entries. Notes should be blank and ACD should not be "X"
        If .Cells(lRow, iNotes) > "" Then Exit Function
       
        'Update Entries
        sSQL = ""
        Dim sTable As String    'Table without Path
        Dim sSlash As String    'Path seperator used
        sSlash = IIf(InStr(1, "/", sPath) > 0, "\", "/")
        sTable = Right(sPath, Len(sPath) - _
                    Chars_Last_Position(sSlash, sPath))
        Select Case UCase(.Cells(lRow, iACD))
            Case Is = "A"
                sSQL = vbCr & _
                    "Insert Into " & sPath & " " & sCRTB & "(" & _
                        Build_SQL_Insert_Fields(sFields, sTable) & _
                            ") " & vbCr & _
                    "Values (" & _
                        Build_SQL_Insert_Values(sFields, sTable, _
                            sData, lRow - 1, True) & ") "
            Case Is = "C"
                sSQL = vbCr & _
                    "Update  " & sPath & " " & vbCr & _
                    "Set     " & _
                        Build_SQL_Update_Values(sFields, sTable, _
                            sData, lRow - 1, True) & " " & vbCr & _
                    "Where   " & _
                        Build_SQL_UpdDlt_Where_Clause(sFields, sTable, _
                            sData, lRow - 1, True)
            Case Is = "D"
               sSQL = vbCr & _
                    "Delete  From " & sPath & " " & vbCr & _
                    "Where   " & _
                        Build_SQL_UpdDlt_Where_Clause(sFields, sTable, _
                            sData, lRow - 1, True)
        End Select
   
        If sSQL > "" Then
            Debug.Print sSQL
            cn.Execute sSQL
            If cn.Errors.Count = 0 Then
                .Cells(lRow, iNotes) = "Updated!"
                Update_Entry = Success          'Successful finish
                If (.Cells(lRow, iACD)) <> "D" Then _
                    .Cells(lRow, iACD) = "X"
            Else
                .Cells(lRow, iNotes) = "Errors prevented updating. " & _
                    cn.Errors(0).Description
            End If
        End If
       
    End With
   
ErrHandler:
   
    If Err.Number <> 0 Then MsgBox _
        "Update_Entry - Error#" & Err.Number & vbCrLf & Err.Description, _
        vbCritical, "Error", Err.HelpFile, Err.HelpContext
    On Error Resume Next
    Range(sData).Cells(lRow, iNotes) = "Errors prevented updating. " & _
        cn.Errors(0).Description
    If Not bOpen Then cn.Close      'Close connection if opened here
    Settings "Restore"              'Restore application settings
    On Error GoTo 0
   
End Function

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: