Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Append table with composite key (Access 2000)

    I had a problem with updating a table with a composite key.The table was called order details extended and i received an excellent solution by the moderator Hans.I am trying now to imitate the new suggested solution to rewrite the function for appending the tables but i didnt succeed.Maybe it is too dificult
    to rewrite it. May i ask for help ?The function want to rewrite in order to include 2 composite keys is the following

    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

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

    Re: Append table with composite key (Access 2000)

    You can combine this function with the method from <post#=369690>post 369690</post#>. See the function in the attached text file.

    Use it like this:

    AppendToTable "tblSource", "tblTarget", "FieldName1", "FieldName2"
    Attached Files Attached Files

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

    Re: Append table with composite key (Access 2000)

    i am really sorry i am sure i have some blunder again when copying your code, it cant work .Coulkd you please look t the
    attachment to see where i am wrong ?
    Attached Files Attached Files

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Append table with composite key (Access 2000)

    <P ID="edit" class=small>(Edited by patt on 09-May-04 07:39. Make a comment re PRoducts table)</P>In the database you sent, there is no data and you should give an example of the command you were trying to do.

    There is also not a Products in the database you sent. Both tblOfferDetails and tblOfferDetails1 both contain a reference to table Products.

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

    Re: Append table with composite key (Access 2000)

    I think we may expect of you that you at least *try* to find out what the code does and how it works.

    You copied the code correctly, but I forgot to make a last minute change to it before posting it. If you look at the code it should be very obvious what should be changed to make it work.

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

    Re: Append table with composite key (Access 2000)

    Thank you for your email.i tried very hard to find out the errors in your "homework" but unfortunatrely
    i did not reach much success.The only thing i discovered is that you have put T instead of
    TargetTable and i have corrected it, but still something escapes me.Could you help me ?
    I will be very happy to use your elegant solution.





    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 TargetTable " & _
    "WHERE "
    For i = LBound(LinkFields) To UBound(LinkFields)
    strSQL = strSQL & "(TargetTable[" & LinkFields(i) & "]=[" & _
    SourceTable & "].[" & LinkFields(i) & "]) AND "
    Next i
    ' Get rid of last " AND "
    strSQL = Left(strSQL, Len(strSQL) - 5)
    strSQL = strSQL & ")"

    ' 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

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

    Re: Append table with composite key (Access 2000)

    The T was not an error. There is a very simple reason why the function does nothing. Which line actually append the records? Look at it carefully.

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

    Re: Append table with composite key (Access 2000)

    Thank you for your reply.There was indeed an ' before the line CurrentDb.Execute strSQL, dbFailOnError

    I have removed it, but even then i get the error.



    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
    ' 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

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

    Re: Append table with composite key (Access 2000)

    A remark such as "even then I get the error" is very unhelpful. You don't say what the error message is and where it occurs. If you want us to help you, you must help us by providing sufficient and clear information.

    If I paste the function from your post into the database you attached earlier, it works correctly.

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

    Re: Append table with composite key (Access 2000)

    Thank you for your patience and your kind efforts to help.To my regret i cannot make the function work and i want very much
    to do so.I am sending to you as an attachment the code and the question.I am sure i didnt do something
    essential,as it often happens with me
    Attached Files Attached Files

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

    Re: Append table with composite key (Access 2000)

    I don't understand what you mean. The tables in the database you attached are empty, so there are no records to append. You can't expect the code to append records that don't exist. If you create a few records, the code does work. As an example, I have created 2 records in TblOfferDetails and 4 in TblOfferDetails1. You can see them by opening the tables. When you click the button on the form, 2 records will be appended from TblOfferDetails1 to TblOfferDetails. If you view TblOfferDetails after having clicked the button on the form, you will see that it has 4 records.
    Attached Files Attached Files

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

    Re: Append table with composite key (Access 2000)

    I have imported the full tables. I cant append them as you told me.Could you please see my attachment i am sure you will find where
    i have been wrong

    best regards
    Attached Files Attached Files

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

    Re: Append table with composite key (Access 2000)

    The function you requested will append records from the source table to the target table for which no match exists. The two tables in the database you attached are identical, so there are NO unmatched records. Therefore the function appends nothing.

    Perhaps you want the function to do something different than you have told us. If so, please explain.

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

    Re: Append table with composite key (Access 2000)

    Thank you. You have asked me to explain what i want.I want exactly this,to append when no match exists.In my table called table there are 4 records,,while in the target table there are 2 records.Also, i want if there is nothing to append, not to receive the error mesage.
    So there are unmatched records, but i get the error Too few parameters expected 2

    Could you please see my attachment? Why do i get these errors? Thank you in advance for everything

    Best regards
    Attached Files Attached Files

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

    Re: Append table with composite key (Access 2000)

    It might help if you checked your code before posting it. The On Click event procedure of the command button on the form contains a typing error.

Page 1 of 2 12 LastLast

Posting Permissions

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