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

    Simplify update function (Access 2000)

    I have a function for updating 2 tables and i have to enumerate all the fileds in the function.Is there any way to update all in the table
    and thus simplify my function.I want to use this function for other similar cases and it will be very conventient just to give the command
    for updating without bothering about the fileds.

    Mu function is :
    Public Function UpdateCallsClients()
    Dim sql As String
    sql = " UPDATE CallsClients1 INNER JOIN CallsClients ON [CallsClients1].[CallID]=[CallsClients].[CallID] SET" & _
    " CallsClients.FirstName = [CallsClients1].[FirstName] " & _
    " , CallsClients.LastName = [CallsClients1].[LastName] " & _
    " , CallsClients.Address= [CallsClients1].[Address] " & _
    " , CallsClients.city = [CallsClients1].[city] " & _
    " , CallsClients.CompanyName = [CallsClients1].[CompanyName] " & _
    " , CallsClients.title = [CallsClients1].[title] " & _
    " , CallsClients.WorkPhone = [CallsClients1].[WorkPhone] " & _
    " , CallsClients.WorkExtension = [CallsClients1].[WorkExtension] " & _
    " , CallsClients.MobilePhone = [CallsClients1].[Mobile] " & _
    " , CallsClients.EmailName = [CallsClients1].[EmailName] " & _
    " , CallsClients.LastMeetingDate = CallsClients1.LastMeetingDate " & _
    " , CallsClients.ContactTypeID = CallsClients1.ContactTypeID " & _
    " , CallsClients.ReferredBy = CallsClients1.ReferredBy " & _
    " , CallsClients.Notes = CallsClients1.Notes " & _
    " , CallsClients.CallDate = CallsClients1.CallDate " & _
    " , CallsClients.CallTime = CallsClients1.CallTime " & _
    " , CallsClients.subject = CallsClients1.subject " & _
    " , CallsClients.clientID = CallsClients1.ClientID "

    CurrentDb.Execute sql
    End Function

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

    Re: Simplify update function (Access 2000)

    It's not quite true that you update ALL fields - you don't update the field CallID on which you join the tables. Do you want to update all fields except the join field? And can you be sure that the source table and target table contain the same fields?

  3. #3
    2 Star Lounger
    Join Date
    Nov 2001
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simplify update function (Access 2000)

    Thank you so much for your kind reply.It is true that the field CallId is not updated.I The two tables are absolutely identical in a sense that
    the source table and the target table contain the same fields.Having this in mind, that the Callid is not updated, and that the two
    tables contain the same fields, can you recommend a formula for updating the fields, or may be this is not possible ?

    Best regards

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

    Re: Simplify update function (Access 2000)

    The following general code uses DAO, so you must set a reference to the Microsoft DAO 3.6 Object Library in Tools | References... in the Visual Basic Editor.

    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 Sub

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

    Call this function in the following manner:

    UpdateTable SourceTable:="CallClients1", TargetTable:="CallClients", LinkField:="CallID"

    or simply

    UpdateTable "CallClients1", "CallClients", "CallID"

  5. #5
    2 Star Lounger
    Join Date
    Nov 2001
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simplify update function (Access 2000)

    Thank you so much indeed !!!!!!!!!!!!!!!!

Posting Permissions

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