changing data in a database

I was wondering if it was possible to have a VB app loop through a sql database and change all values in a field from 1 to 2... If so how would I go about doing that.

Thanks-
yolishAsked:
Who is Participating?
 
samopalConnect With a Mentor Commented:
There is no Edit method in ADO...
This cod will work

Private Sub Form_Load()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
    cnn.Open "dsn=VendorRequest;uid=sa;pwd=majestic;"

rst.Open "UPDATE WEB_VENDOR SET Approval_Status=73 where Approval_Status=5", cnn
cnn.Close
End Sub


HTH    

0
 
jjmartinCommented:
DAO Method:

Dim DB as Database
Dim RS as Recordset

Set DB = opendatabase(app.path & "\mydb.mdb")

Set RS = DB.OpenRecordset("SELECT MyField FROM tblMyTable")

While NOT RS.EOF
    RS.Edit
    RS!MyField = 2
    RS.Update
    RS.MoveNext
Wend
0
 
jjmartinCommented:
DAO Method:

Dim DB as Database
Dim RS as Recordset

Set DB = opendatabase(app.path & "\mydb.mdb")

Set RS = DB.OpenRecordset("SELECT MyField FROM tblMyTable")

While NOT RS.EOF
    RS.Edit
    RS!MyField = 2
    RS.Update
    RS.MoveNext
Wend
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
jjmartinCommented:
ADO Method:



Dim cnn As New ADODB.Connection
Dim rst as New ADODB.Recordset
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyPath\MyDatabase.mdb;"

rst.Open "SELECT MyField FROM tblMyTable", cnn
   
While Not rst.EOF
rst.Edit
rst.Fields("MyField").Value = "New Name"
rst.Update
wend

rst.Close
cnn.Close

0
 
yolishAuthor Commented:
The code looks good here is what I did...

Private Sub Form_Load()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
    cnn.Open "dsn=VendorRequest;uid=sa;pwd=majestic;"

rst.Open "SELECT Approval_Status FROM WEB_VENDOR WHERE Approval_Status=5", cnn
   
While Not rst.EOF
rst.Edit
rst.Fields("Approval_Status").Value = 73
rst.Update
Wend

rst.Close
cnn.Close
End Sub


But when I goto run it I get user defined type not defined for the 2 dim's...

Thanks-
0
 
Bob LearnedCommented:
Or you could try:

Dim DB as Database

Set DB = opendatabase(app.path & "\mydb.mdb")

Call DB.Execute("UPDATE tblMyTable SET MyField = 2 WHERE MyField = 1", dbFailOnError)


0
 
jjmartinCommented:
Ah LearnedOne, I didn't even think of the query method.  Much better.

Yolish - You need to proper references in your app:

To add these references in Visual Basic:

From the Project menu select References…
From the list, select "Microsoft ActiveX Data Objects 2.1 Library."

From the list, select "Microsoft ADO Ext. 2.1 for DDL and Security."

From the list, select "Microsoft Jet and Replication Objects 2.1 Library."

Click OK
0
 
yolishAuthor Commented:
that did it.... but now it runs into an error with the edit saying that Method or data member not found...

Thanks again
0
 
yolishAuthor Commented:
I guess I could have done this all in SQL server....So thanks...
0
All Courses

From novice to tech pro — start learning today.