If Statement to Determine APPEND or UPDATE

[magmike]

How would I write an If Statement to first determine whether or not
the record already exists by ID field, and then UPDATE if so, or
APPEND if not? I know how to write the SQL, but not certain how to use
an IF Statement to determine if the record already exist or not.

thanks in advance!
magmike

 

[Dirk Goldgar]

How would I write an If Statement to first determine whether or not
the record already exists by ID field, and then UPDATE if so, or
APPEND if not? I know how to write the SQL, but not certain how to use
an IF Statement to determine if the record already exist or not.

There are a couple of different ways you might do this. I usually do it via
a recordset, like this:

'---- start of example code 1 ----
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset( _
"SELECT * FROM MyTable WHERE ID=" & lngID)

With rs
If .EOF Then
.AddNew
' New record, so set the ID field.
!ID = lngID
Else
.Edit
End If
!Field1 = strField1
!Field2 = dblField2
' ... and so on ...
.Update
.Close
End With
'---- end of example code 1 ----

You could also do it by using DLookup to check if the record exists, and
then choosing/building SQL statements accordingly; e.g.:

'---- start of example code 2 ----
Dim strSQL As String

If IsNull(DLookup("ID", "MyTable", "ID=" & lngID)) Then
strSQL = _
"INSERT INTO MyTable(ID, Field1, Field2) " & _
"VALUES(" & lngID & "," & Chr(34) & strField1 & Chr(34) & _
"," & dblField2 & ")"
Else
strSQL = _
"UPDATE MyTable SET Field1 = " & _
Chr(34) & strField1 & Chr(34) & _
", Field2 = " & dblField2 & _
" WHERE ID=" & lngID
End If

CurrentDb.Execute strSQL, dbFailOnError
'---- end of example code 2 ----

 

[Arvin Meyer [MVP]]

Build a recordset and use the SQL to branch if it finds a record. Something
like:

Function FastLookup(strFieldName As String, _
strTableName As String, _
strWhere As String) As Variant
'Arvin Meyer 4/9/1997
modified 6/23/2008

Dim strSQL As String
Dim rst As DAO.Recordset
Dim db As DAO.Database

Set db = CurrentDb

strSQL = "SELECT " & strFieldName & " FROM " & _
strTableName & " WHERE " & strWhere & ";"

Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)

If rst.RecordCount <> 0 Then
' update SQL
Else
' append SQL
End If

End Function

 

[magmike]

There are a couple of different ways you might do this.  I usually do it via
a recordset, like this:

'---- start of example code 1 ----
    Dim rs As DAO.Recordset

    Set rs = CurrentDb.OpenRecordset( _
        "SELECT * FROM MyTable WHERE ID=" & lngID)

    With rs
        If .EOF Then
            .AddNew
            ' New record, so set the ID field.
            !ID = lngID
        Else
            .Edit
        End If
            !Field1 = strField1
            !Field2 = dblField2
            ' ... and so on ...
        .Update
        .Close
    End  With
'---- end of example code 1 ----

You could also do it by using DLookup to check if the record exists, and
then choosing/building SQL statements accordingly;  e.g.:

'---- start of example code 2 ----
    Dim strSQL As String

    If IsNull(DLookup("ID", "MyTable", "ID=" & lngID)) Then
        strSQL = _
            "INSERT INTO MyTable(ID, Field1, Field2) " & _
            "VALUES(" & lngID & "," & Chr(34) & strField1 & Chr(34) & _
                    "," & dblField2 & ")"
    Else
        strSQL = _
            "UPDATE MyTable SET Field1 = " & _
                Chr(34) & strField1 & Chr(34) & _
                    ", Field2 = " & dblField2 & _
            " WHERE ID=" & lngID
    End If

    CurrentDb.Execute strSQL, dbFailOnError
'---- end of example code 2 ----

Thanks, both of you! Looks fairly easy.

 

[Ron2006]

Here is an alternate approach:
===========================
Make the following change to your "Append" Query.
In the design view. instead of haveing just the new data table there
add the table you are attempting to update and join the two via the
unique ID. However change the join logic to read ALL of the
new data and only those of the old data that match. (usually a left
join).


Change the query to an update query instead of an append query.


Be sure to update each of the fields with the corresponding
information.


This new query will update all that match and append any new ones.


Below is the sql for a tiny test set of tables that I created to
check
it out.


UPDATE Table4 LEFT JOIN Table4Add ON Table4.Key1 = Table4Add.Key1 SET
Table4Add.Key1 = [Table4]![Key1], Table4Add.data = [Table4]![data];

===================================

If you are using straight sql to do the update, then do the
equivalent .

This way you do not need to test - it will update existing or append
new record.

Ron

 

[Douglas J. Steele]

It's actually possible to write a SQL statement that will update an existing
row or insert a new row as required.

See my November, 2003 "Access Answers" column in Pinnacle Publication's
"Smart Access". You can download the column (and sample database) for free
from http://www.accessmvp.com/DJSteele/SmartAccess.html