472,129 Members | 1,790 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,129 software developers and data experts.

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


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
  1. Public Function fopendb(prjname)
  2. Dim con As New Connection
  4.     Dim rs As New Recordset
  5.     Dim constr
  6.     Source = "d:\documents and settings\sdora\Desktop\GTE tracker\PRODUCTIVITY METRICS.mdb"
  7.     constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Source
  8.     con.Open constr
  9.     rs.Open "Select * from tbl_ProductivityMetrics", con, 1, 2
  10.     rs.MoveFirst
  11.     While Not rs.EOF
  12.    rs!Project_Name = prjname
  13.      rs.MoveNext
  14.     Wend
  15. End Function
Oct 21 '09 #1
2 2850
2,364 Expert 2GB
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!
Nov 9 '09 #2
305 Expert 100+
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
  1. strSQL = "UPDATE tablename SET fieldname=" & newvalue
  2. 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
  1. rs.AddNew
  2. rs.Fields("FieldName1") = SomeValue
  3. rs.Fields("FieldName2") = SomeOtherValue
  4. rs.Update
Or you can do...
Expand|Select|Wrap|Line Numbers
  1. strSQL = "INSERT INTO tablename(field1name, field2name) VALUES('" & somestringvalue & "'," & somenumericvalue & ")"
  2. conn.Execute strSQL

Good Luck
Nov 9 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

27 posts views Thread by VK | last post: by
6 posts views Thread by Takeadoe | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.