how do you update a record in a database table

[locutus]

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

[HarryW]

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

[Markus H]

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

[HarryW]

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