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

    Append All function (Access 2000)

    Append everything if a new customer exists

    I have a function that appends only those customers that do not exist.It works,but i want to improve it,since i have to enumerate
    all the fields,which sometimes are numerous.I want to have a common function to simplify the matter.
    For a similar case, for updating i have received from the Moderator Hans in the present forum, an excellent function called Public Function UpdateTable(SourceTable As String, TargetTable As String, LinkField As String). I use this functioin with success but i cannot redesignit for the append function.Can somebody help me?

    The append function i use is :

    Dim StrAppendCustomer As String

    StrAppendCustomer = "INSERT INTO customers (CustomerID,CompanyName) " & _
    "SELECT o1.customerid, o1.companyname " & _
    " FROM customers1 As o1 WHERE NOT EXISTS (SELECT * FROM Customers WHERE CustomerID=o1.CustomerID) "

    CurrentDb.Execute StrAppendCustomer
    End Sub


    In the above function i have mentioned only 2 fields,CustomerID and CompanyName, but actually the tablke contains a lot of fields
    and that is why i want to avoid their enumeration.

    Just for information the function received from Hans for updating
    is the following:

    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"

    Regards,
    Hans

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

    Re: Append All function (Access 2000)

    This is actually a lot simpler that your previous question, because you want to insert all fields here, instead of all but one.

    Function AppendToTable(SourceTable As String, TargetTable As String, LinkField As String)
    Dim strSQL As String

    On Error GoTo ErrHandler

    strSQL = "INSERT INTO [" & TargetTable & "] " & _
    "SELECT * FROM [" & SourceTable & "] "& _
    "WHERE Not Exists " & _
    "(SELECT * FROM [" & SourceTable & "] As T " & _
    "WHERE T.[" & LinkField & "]=[" & SourceTable & _
    "] .[" & LinkField & "])"

    ' Execute append query
    CurrentDb.Execute strSQL, dbFailOnError

    ExitHandler:
    Exit Function

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Function

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

    Re: Append All function (Access 2000)

    Thank you very much for your reply.I receive the following message

    invalid use of '.','!, or '(), in query expression

    'T.[customerid]=customers1].[customerid]'.

    I cal the function in the following way :

    AppendToTable "customers1", "customers", "customerID"


    May be the wau i call the function is not right ?

    Thank you in advance for your reply

    Best regards

  4. #4
    Lounger
    Join Date
    Aug 2002
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append All function (Access 2000)

    Thank you very much.It works perfectly now! With this approach i have quite a modern approach and i can spare a lot
    of work. Just to think how powerful Access is.

    I wish you a pleasant day

    Best regards

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

    Re: Append All function (Access 2000)

    Hi Aral,

    In the first place, there is an error in my function; the version I posted shouldn't cause an error, but it will not append any records either, since the EXISTS part only looks at the source table. Please accept my apologies. See below for the corrected code.

    The error message you get means that you must have omitted a left bracket [ when placing the function in your module. There should have been an opening bracket before customers1 in T.[customerid]=customers1].[customerid].

    Did you know that you can select code in a post with the mouse, then copy it to the clipboard with Ctrl+C, and paste it into a module with Ctrl+V?

    Here is the corrected code, I verified that it works in my test database:

    Function AppendToTable(SourceTable As String, TargetTable As String, LinkField As String)
    Dim strSQL As String

    On Error GoTo ErrHandler

    strSQL = "INSERT INTO [" & TargetTable & "] " & _
    "SELECT * FROM [" & SourceTable & "] " & _
    "WHERE Not Exists " & _
    "(SELECT * FROM [" & TargetTable & "] As T " & _
    "WHERE T.[" & LinkField & "]=[" & SourceTable & _
    "].[" & LinkField & "])"

    ' Execute append query
    ' Uncomment the following line if you want to inspect the SQL.
    'MsgBox strSQL
    CurrentDb.Execute strSQL, dbFailOnError
    ExitHandler:
    Exit Function

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    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
  •