update insert msaccess

Question

i wish to update or insert if missing into an msaccess database using asp.

i was trying something like:

IF EXISTS (SELECT * FROM Table1 WHERE Column1='SomeValue')
    UPDATE Table1 SET (...) WHERE Column1='SomeValue'
ELSE
    INSERT INTO Table1 VALUES (...)

and

UPDATE Table1 SET (...) WHERE Column1='SomeValue'
IF @@ROWCOUNT=0
    INSERT INTO Table1 VALUES (...)

INSERT or UPDATE by themselves work fine. but when i use the both with one of the methods it fails.

Answer 1

You cannot run two sql statements at once in Access. You must update and insert in two separate operations.

Answer 2

If you're using classic ASP, you can open an ADO recordset with "SELECT COUNT(*) AS num_rows FROM Table1 WHERE Column1='SomeValue'" as the recordset source. Then:

If rs!num_rows > 0 Then
    'UPDATE '
Else
    'INSERT '
End If

Answer 3

Set db = CreateObject("ADODB.Connection")
set rs = Server.CreateObject("ADODB.Recordset")
        db.Open "DSN=name"
        rs.CursorLocation = 3
        rs.Open "SELECT * FROM Table WHERE Field="&Variable, db, 3, 3
        if rs.EOF then
        rs.AddNew
        end if
            rs("fieldName1") = Variable1
            rs("fieldName2") = Variable2
            rs("fieldName3") = Variable3
        rs.Update
        rs.Close

if the SELECT returns nothing, it adds record. after adding the cursor is on the added record. if the SELECT returns a record (since Field is unique) the cursor is on the selected record.

and than it updates the record where cursor is :)