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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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
samopalCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
yolishAuthor Commented:
I guess I could have done this all in SQL server....So thanks...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.