Append record if exist or Update if not exist (1 Viewer)

Mist

Registered User
Joined
Mar 5, 2012
Messages
66
My question is this: :confused: 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.
 
Last edited:

sparks80

Physicist
Joined
Mar 31, 2012
Messages
223
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

Physicist
Joined
Mar 31, 2012
Messages
223
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]

Code:
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

Registered User
Joined
Mar 5, 2012
Messages
66
Many thanks Sparks80, you seem to have it exactly, much appreciated. I'm gonna setle down and try it now! :)
 

Mist

Registered User
Joined
Mar 5, 2012
Messages
66
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]

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

Attachments

Galaxiom

Super Moderator
Staff member
Joined
Jan 20, 2009
Messages
11,828
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

Access Lover
Joined
Mar 27, 2013
Messages
315
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 adnansafd[email protected] or reply here to so i will send you a file or disscuss further :banghead:
 

CoffeeGuru

Registered User
Joined
Jun 20, 2013
Messages
121
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

Registered User
Joined
Jan 21, 2009
Messages
1,623
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

Code:
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

error reading drive A:
Joined
May 7, 2009
Messages
8,629
janr, you only need to join on pk/fk keys, no need for each field and it is not LEFT JOIN, its RIGHT JOIN.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom