Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update some fields (Access 2000)

    I possess an excellent function provided by Hans from the present Forum.I use this function by calling as follows
    UpdateTable "Products1", "Products", "ProductID"
    In this way i am updating the table Products with the data from the table Products1.
    It works great.However,i am trying now to update not all the fields, but some fileds
    in both tables.For example, i want to update only the fields range1,range2,range3,
    range4 etc and leave all the other fields intact in the table Products.
    How can i do that ?



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




    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 some fields (Access 2000)

    Are the fields to be updated fixed, or should they be specified each time you run the function?

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update some fields (Access 2000)

    The fields to be specified are fixed and are the same for both tables.They donot need to be specified each time i run the function.
    The fields are virtually the same for both tables.
    What i aim is the following.Sometimes i have new rows with products, the products names might change etc.Therefore i want to preserve
    the data in the original table products, but to update only the contents of some fields

    regards

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

    Re: Update some fields (Access 2000)

    Since everything is fixed, I wouldn't use code, but simply create an update query in design view. It is much easier to create a query that way than to assemble the SQL in code. If necessary, you can always run the query from a VBA function by using DoCmd.OpenQuery "qryUpdate", where qryUpdate is the name of the query.

    If you would still like to do this in code, it could look like this:

    Public Function UpdateTable(SourceTable As String, TargetTable As String, _
    LinkField As String)
    Dim strSQL As String

    On Error GoTo ErrHandler

    strSQL = "UPDATE [" & TargetTable & "] INNER JOIN [" & SourceTable & "] " & _
    "ON [" & TargetTable & "].[" & LinkField & " ]=[" & _
    SourceTable & "].[" & LinkField & "] SET " & _
    "[" & TargetTable & "].[Range1]=[" & SourceTable & "].[Range1], " & _
    "[" & TargetTable & "].[Range2]=[" & SourceTable & "].[Range2], " & _
    "[" & TargetTable & "].[Range3]=[" & SourceTable & "].[Range3], " & _
    "[" & TargetTable & "].[Range4]=[" & SourceTable & "].[Range4]"

    ' Execute update query
    CurrentDb.Execute strSQL, dbFailOnError
    Exit Function

    ErrHandler:
    ' Report error
    MsgBox Err.Description, vbExclamation
    End Function

Posting Permissions

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