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

    updating tables (Access 2000)

    I have a global function for updating tables suggested by Hans,a moderator of the present Forum.
    It works with all the tables except the tables with a composite key.My table "order details"
    has 2 keys, orderid and productid. Can apply the function in this case ?

    I have tried to put the 2 keys , ProductID and OrderID together but i failed.:


    UpdateTable "order details1", "order details", "orderID,productid"



    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: updating tables (Access 2000)

    Attached is a revised function. It takes a variable number of link fields. Call it like this:

    UpdateTable "order details1", "order details", "orderID", "productid"

    Note that the link fields are enclosed in quotes individually, and separated by commas. If you had 3 link fields, it would be like this:

    UpdateTable "order details1", "order details", "orderID", "productid", "otherID"
    Attached Files Attached Files

  3. #3
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: updating tables (Access 2000)

    Thank you very much indeed for your reply .I copied your new functioin but when executing it i got
    the error " too few parameters. Expected 2."
    I do not know what 2 means. Is it becasue both arguments are primary keys?
    I send in the attachment my tables

    Best regards
    Attached Files Attached Files

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

    Re: updating tables (Access 2000)

    It works ok for me. I have created a few dummy records in the tables to test.
    The attached version contains a module with the function from my previous reply, and a procedure TestTheFunction that executes the function. Click in this procedure and press F5 to run it. Do you get errors?
    Attached Files Attached Files

  5. #5
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: updating tables (Access 2000)

    Dear Hans,

    Just a quick note to let you know that it works.

    Please accept my highest appreciation

    Best regards

Posting Permissions

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