Append table with composite key (Access 2000)

[freelance]

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

[HansV]

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"

[freelance]

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 ?

[patt]

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

[HansV]

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.

[freelance]

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

[HansV]

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.

[freelance]

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

[HansV]

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.

[freelance]

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

[HansV]

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.

[freelance]

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

[HansV]

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.

[freelance]

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

[HansV]

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.