Results 1 to 2 of 2
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update function (Access 2000)

    Update function


    I use an excellent function suggested by the moderator Hans, for updating the tables.
    My question is can i somehow exclude from updating some fields, called branch1, branch2, etc ?
    I do not want to update them but i do not know is it possible

    Private Function UpdateTable(SourceTable As String, TargetTable As String, LinkField As String)

    ' Call this function in the following manner:

    ' UpdateTable "products1", "products", "ProductID"




    Dim strSQL As String
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field

    On Error GoTo ErrHandler

    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs(SourceTable)

    strSQL = "UPDATE [" & TargetTable & "] INNER JOIN [" & SourceTable & "] " & _
    "ON [" & TargetTable & "].[" & LinkField & " ]=[" & SourceTable & "].[" & LinkField & "] " & _
    "SET "

    For Each fld In tdf.Fields
    If Not (fld.Name = LinkField) Then
    strSQL = strSQL & "[" & TargetTable & "].[" & fld.Name & "]=" & _
    "[" & SourceTable & "].[" & fld.Name & "], "
    End If
    Next fld

    ' Get rid of last ", "
    strSQL = Left(strSQL, Len(strSQL) - 2)
    ' Execute update query
    dbs.Execute strSQL, dbFailOnError

    ExitHandler:
    ' Clean up
    Set fld = Nothing
    Set tdf = Nothing
    Set dbs = Nothing
    Exit Function

    ErrHandler:
    ' Report error, then go to cleaning up section
    MsgBox Err.description, vbExclamation
    Resume ExitHandler
    End Function

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update function (Access 2000)

    Here you go. The function now has an optional ParamArray argument Excluded(). This means that you can add a variable number of arguments; these will be excluded from the list of fields to copy.

    Example: update Products from Products1, linked on ProductID, and exclude Branch1 and Branch2.

    UpdateTable "Products1", "Products", "ProductID", "Branch1", "Branch2"

    <img src=/w3timages/blueline.gif width=33% height=2>

    Public Function UpdateTable(SourceTable As String, TargetTable As String, LinkField As String, ParamArray Excluded())
    Dim strSQL As String
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim i As Integer

    On Error GoTo ErrHandler

    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs(SourceTable)

    strSQL = "UPDATE [" & TargetTable & "] INNER JOIN [" & SourceTable & "] " & _
    "ON [" & TargetTable & "].[" & LinkField & " ]=[" & SourceTable & "].[" & LinkField & "] " & _
    "SET "

    For Each fld In tdf.Fields
    For i = LBound(Excluded) To UBound(Excluded)
    If fld.Name = Excluded(i) Then GoTo NextFld
    Next i
    If Not (fld.Name = LinkField) Then
    strSQL = strSQL & "[" & TargetTable & "].[" & fld.Name & "]=" & _
    "[" & SourceTable & "].[" & fld.Name & "], "
    End If
    NextFld:
    Next fld

    ' Get rid of last ", "
    strSQL = Left(strSQL, Len(strSQL) - 2)
    ' Execute update query
    dbs.Execute strSQL, dbFailOnError

    ExitHandler:
    ' Clean up
    Set fld = Nothing
    Set tdf = Nothing
    Set dbs = Nothing
    Exit Function

    ErrHandler:
    ' Report error, then go to cleaning up section
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Function

    <img src=/w3timages/blueline.gif width=33% height=2>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •