changing data in a database

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

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

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

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-

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)

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

that did it.... but now it runs into an error with the edit saying that Method or data member not found...

Thanks again

I guess I could have done this all in SQL server....So thanks...