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.

Expand|Select|Wrap|Line Numbers

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

Expand|Select|Wrap|Line Numbers

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

Expand|Select|Wrap|Line Numbers

 
rs.AddNew

rs.Fields("FieldName1") = SomeValue

rs.Fields("FieldName2") = SomeOtherValue

rs.Update

Or you can do...

Expand|Select|Wrap|Line Numbers

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

conn.Execute strSQL

Good Luck

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

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

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

Similar Visual Basic 4 / 5 / 6 bytes

/bytes/development

/bytes/it

/bytes/about