469,282 Members | 1,708 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,282 developers. It's quick & easy.

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 2767
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
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.