Append record if exist or Update if not exist

[Mist]

My question is this: How do I go about creating a procedure that will interrogate each table (on completion of the 'common_fields' form) and APPEND the record if the ID isn't found in the destination table, or UPDATE the destination table's record if the ID is found?

Scenario background:
I have an Access 2007 database comprising two tables at present (probably 5 by the time I'm finished). In certain situations the input into some fields in one table will need to be replicated in a similar set of fields in the other table. I cannot join the tables as it will result in more than 255 fields.

Hence, I have created a third table containing the fields common to both these tables and want the user to fill in the common information in the third table's form. I then propose to APPEND this data to each of the two tables, if the form's ID doesn't exist in the destination table, or UPDATE the existing data in the destination table if it already exists - before presenting the user with a partially completed form for each of these two tables with only the blank fields to complete and possibly edit some fields before saving.

 

[sparks80]

First of all I would suggest you create the append and update queries that would save the common data into the other two tables. You will probably need to include a parameter for the record ID that you wish to append/update.

Then the vba code can use the dcount function to test if the record exists in the destination table. If it does exist then perform update query, if not perform the append query. I'll write some sample code and get back to you.

 

[sparks80]

OK here is some sample code, although I haven't had time to test it.
You will need to go through and enter the names of the actual objects - tables, queries and fields.
The code below will pass a parameter called "FieldID" to the query. You can use this to restrict the update query to the relevant record:

So at the top of the SQL code you will need to include:
PARAMETERS FieldID Long; .........

And in the where condition:
WHERE Your_RecordID_FieldName = [FieldID]

Sub UpdateOrAppend(lngFieldID As Long)
    Dim cn As New ADODB.Connection
    Dim cmd As Command
    Dim p As ADODB.Parameter
    
    Set cn = CurrentProject.Connection
    
    cmd.CommandType = adCmdStoredProc
    
    [COLOR=Green]' Check if record with specified ID exists in Table1[/COLOR]
    If Nz(DCount("*", "[COLOR=Red]Table1[/COLOR]", "[COLOR=Red]ID_Field_Name[/COLOR]=" & lngFieldID), 0) > 0 Then
[COLOR=Green]        ' If record found then perform update query
[/COLOR]        cmd.CommandText = "[COLOR=Red]Table1_UpdateQueryName[/COLOR]"
    Else
        [COLOR=Green]' If record not found then perform append query[/COLOR]
        cmd.CommandText = "[COLOR=Red]Table1_AppendQueryName[/COLOR]"
    End If
    
    [COLOR=Green]' Create parameter to pass to Update or Append query[/COLOR]
    Set p = cmd.CreateParameter("[COLOR=Red]FieldID[/COLOR]", adBigInt, adParamInput, 0, 0)
    cmd.Parameters.Append p
    cmd.Parameters("[COLOR=Red]FieldID[/COLOR]") = lngFieldID
    
    cmd.Execute
    
    [COLOR=Green]' Now you will need to repeat the same code for table 2 ....[/COLOR]
    



   [COLOR=Green] ' Finally release objects[/COLOR]
    Set cmd = Nothing
    Set p = Nothing
    Set cn = Nothing
    
End Sub

To call the code:

UpdateOrAppend(12345)
where 12345 is the record ID in the "common fields" table

 

[Mist]

Many thanks Sparks80, you seem to have it exactly, much appreciated. I'm gonna setle down and try it now!

 

[Mist]

OK here is some sample code, although I haven't had time to test it.
You will need to go through and enter the names of the actual objects - tables, queries and fields.
The code below will pass a parameter called "FieldID" to the query. You can use this to restrict the update query to the relevant record:

So at the top of the SQL code you will need to include:
PARAMETERS FieldID Long; .........

And in the where condition:
WHERE Your_RecordID_FieldName = [FieldID]

Sub UpdateOrAppend(lngFieldID As Long)
    Dim cn As New ADODB.Connection
    Dim cmd As Command
    Dim p As ADODB.Parameter
 
    Set cn = CurrentProject.Connection
 
    cmd.CommandType = adCmdStoredProc
 
    [COLOR=green]' Check if record with specified ID exists in Table1[/COLOR]
    If Nz(DCount("*", "[COLOR=red]Table1[/COLOR]", "[COLOR=red]ID_Field_Name[/COLOR]=" & lngFieldID), 0) > 0 Then
[COLOR=green]       ' If record found then perform update query[/COLOR]
        cmd.CommandText = "[COLOR=red]Table1_UpdateQueryName[/COLOR]"
    Else
        [COLOR=green]' If record not found then perform append query[/COLOR]
        cmd.CommandText = "[COLOR=red]Table1_AppendQueryName[/COLOR]"
    End If
 
    [COLOR=green]' Create parameter to pass to Update or Append query[/COLOR]
    Set p = cmd.CreateParameter("[COLOR=red]FieldID[/COLOR]", adBigInt, adParamInput, 0, 0)
    cmd.Parameters.Append p
    cmd.Parameters("[COLOR=red]FieldID[/COLOR]") = lngFieldID
 
    cmd.Execute
 
    [COLOR=green]' Now you will need to repeat the same code for table 2 ....[/COLOR]
 
 
 
 
   [COLOR=green]' Finally release objects[/COLOR]
    Set cmd = Nothing
    Set p = Nothing
    Set cn = Nothing
 
End Sub

To call the code:

UpdateOrAppend(12345)
where 12345 is the record ID in the "common fields" table

----------------------------------------------------------------
In applying the above (thanks Sparks) I get a "runtime error 91" (see attached) and I'm also not certain what you mean by: "So at the top of the SQL code you will need to include: PARAMETERS FieldID Long; .........

I have been teaching myself as much as possible re- Access & VBA and my Append and Update Queries work just fine - I'm using Access 2007. Thanks for your inputm it's been very helpful i.t.o. my understanding. I will appreciate your comments on the above.

 

[Galaxiom]

There is no need for deciding between append and update queries.

Simply use an Update query with an OUTER JOIN from the source to destination table on the common fields.

Unmatched records will be appended and matching records will be updated.

 

[syedadnan]

First of all I would suggest you create the append and update queries that would save the common data into the other two tables. You will probably need to include a parameter for the record ID that you wish to append/update.

Then the vba code can use the dcount function to test if the record exists in the destination table. If it does exist then perform update query, if not perform the append query. I'll write some sample code and get back to you.

Regards,

Mr Spark Rare you on board,

I am using this way to be in touch with you as i have seen that you are having marvellous expertise in access , i am hiting head from last few days as me is a newbie at access 2007.. just looking to get help in producing automatic fee voucher at month start which consist of all student in student table .. this i need badly.. please contact at adnansafdar1979@yahoo.com or reply here to so i will send you a file or disscuss further :banghead:

 

[CoffeeGuru]

There is no need for deciding between append and update queries.

Simply use an Update query with an OUTER JOIN from the source to destination table on the common fields.

Unmatched records will be appended and matching records will be updated.

Eh..How does that work I just get an error..

UPDATE <Destination table> A
INNER JOIN <Source table> B ON A.aaa = B.aaa AND A.bbb = B.bbb AND A.ccc = B.ccc AND A.ddd = B.ddd
SET A.aaa = B.aaa AND A.bbb = B.bbb AND A.ccc = B.ccc AND A.ddd = B.ddd

This works as an INNER JOIN but if I Just change it to OUTER JOIN I get Syntax error in UPDATE Statement.

 

[JANR]

Access do not support a full outer join. Change til to either Right Join or Left Join.

In your case I think you need Left Join

Also use your real table names marked in blue

UPDATE [COLOR="Blue"]<Destination table>[/COLOR] As A
[COLOR="Red"]LEFT JOIN[/COLOR] [COLOR="blue"]<Source table>[/COLOR] As B ON A.aaa = B.aaa AND A.bbb = B.bbb AND A.ccc = B.ccc AND A.ddd = B.ddd
SET A.aaa = B.aaa AND A.bbb = B.bbb AND A.ccc = B.ccc AND A.ddd = B.ddd

JR

 

[arnelgp]

janr, you only need to join on pk/fk keys, no need for each field and it is not LEFT JOIN, its RIGHT JOIN.