Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    Nov 2001
    Thanked 0 Times in 0 Posts

    Update tables function (Access 2000)

    I have a very useful function, given to me be Hans, a moderator in the present Forum, which works excellent for all the tables except
    two, namely for the tables order details and the table customers.There i receive the mesage order details.orderid not updatable
    and customers.customerid not updatable.How can i solve this problem? I cannot explain myself why
    this happens with 2 tables and not with the others. i will be very grateful if i receive some advice.Otherwise the
    function suggested to me is very effective and easy to handle

    Public Function UpdateTables()
    UpdateTable "orders1", "orders", "orderid"
    UpdateTable "orderdetails1", "orderdetails", "orderid"
    UpdateTable "customers1", "customers", "customerid"
    UpdateTable "TblClients1", "TblClients", "ClientID"
    UpdateTable "CallsClients1", "CallsClients", "CallID"
    UpdateTable "CallsCustomers1", "CallsCustomers", "CallID"
    End Function

    Public Function UpdateTable(SourceTable As String, TargetTable

    As String, LinkField As String)

    ' Call this function in the following manner:
    'UpdateTable SourceTable:="CallClients1",
    'or simply
    'UpdateTable "CallsClients1", "CallsClients", "CallID"

    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

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

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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: Update tables function (Access 2000)

    In Access 2000 and up, a query involving more than one table usually requires a unique index on the link field on the "one" side of the join in order to be updateable.

    If the OrderDetails table is like the one in the Northwind sample database, it has a composite primary key, so OrderID by itself is not unique. That means that the function I provided will not work. You will have to create a new one for tables with a composite primary key.

    (Am I correct in assuming that you are a student? If so, it will be a useful exercise to try and create the modified function yourself; you will learn more that way than if you just copy it from a post)

    Check the Customers and Customers1 tables. If CustomerID is the primary key there, I don't understand why the function fails.

Posting Permissions

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