473,545 Members | 937 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

30 New Member

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 2907
2,364 Recognized Expert Top Contributor
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 Recognized Expert Contributor
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

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

Similar topics

by: baustin75 | last post by:
Posted: Mon Oct 03, 2005 1:41 pm Post subject: cannot mail() in ie only when debugging in php designer 2005 -------------------------------------------------------------------------------- Hello, I have a very simple problem but cannot seem to figure it out. I have a very simple php script that sends a test email to myself. When I...
by: VK | last post by:
<http://www.jibbering.com/faq/#FAQ3_2> The parts where update, replacement or add-on is needed are in <update> tag. 3.2 What online resources are available? Javascript FAQ sites, please check these first:- <http://developer.irt.org/script/script.htm>
by: Shaun | last post by:
Ok here is the situation… Have an access 20002 application that I'm converting to have a SQL Server backend (2000), the application has been in use with an access backend for years, no real problem, just getting large so needed to migrate to SQL. I've converted the tables in SQL tables (I've used SQL quite a lot) and link the tables when the...
by: Aaron | last post by:
Complete code follows. I am new to .NET programming (and programming in general) and I am having a difficult time understanding how to fill a variable in one sub, and then access it from another. I have tried declaring them as shared, public, friend, etc and I always get an error stating that something is not valid on a local variable...
by: Hexman | last post by:
I've come up with an error which the solution eludes me. I get the error: >An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in HRTest.exe > >Additional information: Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype. It occurs when I...
by: Takeadoe | last post by:
Dear NG, Can someone assist me with writing the little code that is needed to run an update table query each time the database is opened? From what I've been able to glean from this group, the Autoexec Macro looks like the way to go. Could someone please assist? Thank you very much! Mike
by: gunimpi | last post by:
http://www.vbforums.com/showthread.php?p=2745431#post2745431 ******************************************************** VB6 OR VBA & Webbrowser DOM Tiny $50 Mini Project Programmer help wanted ******************************************************** For this teeny job, please refer to: http://feeds.reddit.com/feed/8fu/?o=25
by: hotflash | last post by:
Hi Master CroCrew, I found a good PURE ASP that will allow you to upload 10MB file to the server and the file contents such as Network, Author, Title, etc... will insert to MS Access at the same time. Below is a working script that I used. Let's say after the file is uploaded to the server and a record created with the file contents above...
by: O | last post by:
I need to do some lookups and updates on some legacy Access 2.0 files (they've from another vendor and I'm not in a position to update them). I was successful using VB6, but I've moved on to VS 2005 and ADO and having some serious problems. Here's the scenario: 2 people have the same Access 2.0 databases running in the Access 2.0...
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.