Insert or update

C

CV

Hello,

I have a collection of data on a local access table and I need to update
data on a ORACLE table, but sometimes I need insert one row and another I
need update a row on the Oracle table.
How can I choose insert or update?

I have the idea of use always update on docmd.runsql and use a insert on
docmd.runsql when I update zero row, but I don't know how get the number of
rows.

I thank some help
Carlos Valente
 
K

Klatuu

Use either the FindFirst method or the DLookup function to determine whether
the record already exists, then either Update or Insert as nececcary. I
would also suggest you read up on the DAO Execute method. It is about 3
times faster the the RunSql
 
B

Brendan Reynolds

You need to determine whether the record already exists in the target table.
If it does, you need to execute an INSERT INTO SQL statement, if it doesn't,
you need to execute an UPDATE SQL statement. Here's an example of one
approach, using the SQL Server Customers table for demonstration purposes
....

Public Sub InsertOrUpdate(ByVal CustomerID As String, ByVal CompanyName As
String)

Dim strSQL As String
Dim rst As ADODB.Recordset
Dim lngCount As Long

strSQL = "SELECT Count(*) AS TheCount FROM dbo_Customers WHERE
CustomerID = '" & CustomerID & "'"
Set rst = New ADODB.Recordset
With rst
.ActiveConnection = CurrentProject.Connection
.Open strSQL
lngCount = .Fields("TheCount")
.Close
End With

If lngCount = 0 Then
strSQL = "INSERT INTO dbo_Customers(CustomerID, CompanyName)
VALUES('" & CustomerID & "', '" & CompanyName & "')"
Else
strSQL = "UPDATE dbo_Customers SET CompanyName = '" & CompanyName &
"' WHERE CustomerID = '" & CustomerID & "'"
End If
CurrentProject.Connection.Execute strSQL

End Sub

Examples of use, in the Immediate window ...

insertorupdate "ALFKI", "New Name for ALFKI"
insertorupdate "NEWID", "New Customer"

The first line above will cause the UPDATE statement to be executed, because
'ALFKI' is the CustomerID of an existing record. The second line will cause
the INSERT INTO statement to be executed, because there is no existing
record with the value 'NEWID' in the CustomerID field.
 
Top