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

    Append problem (Access 2000)

    How can I append selected rows? I have 2 tables called Clients and clients1. The Source table Clients1 contains rows with diferent numbers of the field afid, while the target table contains ony one number of the field afid. I want to modify my append function in such a way, as to append from the source table only those rows which are equalt to the afid in the target table.
    For example the target table contain the number of the afid = 4. The target table contains rows with different afid number, ,e.g. 1,2,3,4,5,6. How can I make the function append only those rows that contain the afid = 4 ?
    Here is the code i use :
    AppendToTable "tblClients1", "tblClients", "Clientid", "Clientid"

    Function AppendToTable(SourceTable As String, TargetTable As String, ParamArray LinkFields())
    ' AppendToTable "tblSource", "tblTarget", "FieldName1", "FieldName2"
    Dim StrSQL As String
    Dim i As Integer
    On Error GoTo ErrHandler
    StrSQL = "INSERT INTO [" & TargetTable & "] " & _
    "SELECT * FROM [" & SourceTable & "] " & _
    "WHERE Not Exists " & _
    "(SELECT * FROM [" & TargetTable & "] As T " & _
    "WHERE "
    For i = LBound(LinkFields) To UBound(LinkFields)
    StrSQL = StrSQL & "(T.[" & LinkFields(i) & "]=[" & _
    SourceTable & "].[" & LinkFields(i) & "]) AND "
    Next i
    ' Get rid of last " AND "
    StrSQL = Left(StrSQL, Len(StrSQL) - 5)
    StrSQL = StrSQL & ")"
    ' Execute append query
    CurrentDb.Execute StrSQL, dbFailOnError
    ExitHandler:
    Exit Function
    ErrHandler:
    MsgBox "xxxx, 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: Append problem (Access 2000)

    This function (of which I wrote the original version for your colleague or alter ego <!profile=freelance>freelance<!/profile> in <post:=370,349>post 370,349</post:>) will append records from tblClients1 to tblClients for which there is *no* match on the field ClientID (which only needs to be specified once, not twice). You want something quite different.

    Try the following in a procedure:

    Dim strSQL As String
    strSQL = "INSERT INTO Clients SELECT * FROM Clients1 WHERE afid In (SELECT afid FROM Clients)"
    CurrentDb.Execute strSQL, dbFailOnError

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

    Re: Append problem (Access 2000)

    Thank you for thje wonderful suggestion. It is great !

Posting Permissions

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