Help needed to update the MS ACCESS database using the VB script

[susinthaa]

Hi,

we are having some text values in the variable of the VB form and we have to insert these datas into the MS Access database.

We have opened the database and only one record is inserted in the DB and the latest records are over wiritng the old one.

Below is the codings for updating the database. Please help me how to insert the fields in the next row in the DB.

Public Function fopendb(prjname)
Dim con As New Connection
    
    Dim rs As New Recordset
    Dim constr
    Source = "d:\documents and settings\sdora\Desktop\GTE tracker\PRODUCTIVITY METRICS.mdb"
    constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Source
    con.Open constr
    rs.Open "Select * from tbl_ProductivityMetrics", con, 1, 2
    rs.MoveFirst
    While Not rs.EOF
   rs!Project_Name = prjname
     rs.MoveNext
    Wend
End Function

[Dököll]

Is this all of the code? Looks like you are retrieving from the db... Anyhoo, if the rest of your code contains an Update statement, that could be your problem, if you are updatig, you are by definition overwriting data. Does this make sense?

Please stay tuned for a better answer if this is not it.

In a bit!

[vb5prgrmr]

Okay, the code you have will update every field in the table with the same value, which can be done like this also...

strSQL = "UPDATE tablename SET fieldname=" & newvalue
conn.Execute strSQL

For inserting a new record through the ado recordset object like you are doing with your update you would do something like...

rs.AddNew
rs.Fields("FieldName1") = SomeValue
rs.Fields("FieldName2") = SomeOtherValue
rs.Update

Or you can do...

strSQL = "INSERT INTO tablename(field1name, field2name) VALUES('" & somestringvalue & "'," & somenumericvalue & ")"
conn.Execute strSQL

Good Luck