-
Hi
I am checking a table every couple of hours in an access database, new records are getting added to the end of the table but if the record exists all ready then I would like to overwrite the existing record with the new record which has more upto date information held in the fields. How do I overwrite the existing record !
Please help !
heres my code in case it helps any:
Do Until rsreelstore.EOF
Set rsTemp = reeldata_db.OpenRecordset _
("SELECT COUNT(*) As RecCount " _
& "FROM reeldata WHERE Number = '" _
& rsreelstore!Number & "'")
rsTemp.MoveLast
If rsTemp!RecCount = 0 Then
Dim StrSQL As String
StrSQL = "INSERT INTO reeldata SELECT reelstore.* FROM reelstore " & _
"WHERE Number = '" & rsreelstore!Number & " '"
reeldata_db.Execute StrSQL
End If
rsreelstore.MoveNext
Loop
what I am doing here is saying if there is no existing record then insert the new record from the reelstore table into the reeldata table, but now I need to say if there is an existing record for this number then overwrite the record with the new one. How do I code this, I think I need an "else" after the reeldata_db.execute strsql. so it would be else if rstemp = 1 ( there is a match ) then overwrite.
Does this make sense ? I hope so as I am under the kosh now and need to get this project finished.
Thanx
locutus
-
It's another SQL statement, the UPDATE statement.
Something like:
UPDATE * FROM rs WHERE blah blah blah VALUES (blah blah blah)
If you can see what I mean...
-
This should work. Good luck.
Dim Db As Database
Dim Rs As Recordset
Dim MySQL As String
MySQL = "SELECT * FROM Table WHERE Number = MyNumber"
Set Db = OpenDatabase("MyDb.mdb")
Set Rs = Db.OpenRecordset(MySQL, dbOpenDynaset)
If Rs.RecordCount <> 0 Then
Rs.MoveFirst
Rs.Edit
Rs!Field1 = Field1
Rs!Field2 = Field2
Else
Rs.AddNew
Rs!Field1 = Field1
Rs!Field2 = Field2
End If
Rs.Update
Set Rs = Nothing
Set Db = Nothing
-
Oh sorry wrong UPDATE syntax (think that's INSERT syntax). Here's the correct syntax:
UPDATE reeldata
SET var1='var1val', var2='var2val'
WHERE
Number = theNumber
For a full description check out Microsoft's SQL site UPDATE documentation
