(mysql.info) dao--rs-addnew

Info Catalog

(mysql.info) ado--rs-addnew

(mysql.info) myodbc-with-vb

(mysql.info) rdo--rs-addnew-and-rs-update

 
 23.1.19.2 DAO: `rs.addNew', `rs.update', and Scrolling
 ......................................................
 
 The following DAO (Data Access Objects) example creates a table
 `my_dao' and demonstrates the use of `rs.addNew', `rs.update', and
 result set scrolling.
 
      Private Sub myodbc_dao_Click()
 
      Dim ws As Workspace
      Dim conn As Connection
      Dim queryDef As queryDef
      Dim str As String
 
      'connect to MySQL using MySQL ODBC 3.51 Driver
      Set ws = DBEngine.CreateWorkspace("", "venu", "venu", dbUseODBC)
      str = "odbc;DRIVER={MySQL ODBC 3.51 Driver};"_
                            & "SERVER=localhost;"_
                            & " DATABASE=test;"_
                            & "UID=venu;PWD=venu; OPTION=3"
      Set conn = ws.OpenConnection("test", dbDriverNoPrompt, False, str)
 
      'Create table my_dao
      Set queryDef = conn.CreateQueryDef("", "drop table if exists my_dao")
      queryDef.Execute
 
      Set queryDef = conn.CreateQueryDef("", "create table my_dao(Id INT AUTO_INCREMENT PRIMARY KEY, " _
                                                               & "Ts TIMESTAMP(14) NOT NULL, Name varchar(20), Id2 INT)")
      queryDef.Execute
 
      'Insert new records using rs.addNew
      Set rs = conn.OpenRecordset("my_dao")
      Dim i As Integer
 
        For i = 10 To 15
        rs.AddNew
        rs!Name = "insert record" & i
        rs!Id2 = i
        rs.Update
        Next i
                 rs.Close
 
      'rs update..
      Set rs = conn.OpenRecordset("my_dao")
      rs.Edit
      rs!Name = "updated-string"
      rs.Update
      rs.Close
 
      'fetch the table back...
      Set rs = conn.OpenRecordset("my_dao", dbOpenDynamic)
      str = "Results:"
      rs.MoveFirst
      While Not rs.EOF
      str = " " & rs!Id & " , " & rs!Name & ", " & rs!Ts & ", " & rs!Id2
      Debug.Print "DATA:" & str
      rs.MoveNext
      Wend
 
      'rs Scrolling
      rs.MoveFirst
      str = " FIRST ROW: " & rs!Id & " , " & rs!Name & ", " & rs!Ts & ", " & rs!Id2
      Debug.Print str
 
      rs.MoveLast
      str = " LAST ROW: " & rs!Id & " , " & rs!Name & ", " & rs!Ts & ", " & rs!Id2
      Debug.Print str
 
      rs.MovePrevious
      str = " LAST-1 ROW: " & rs!Id & " , " & rs!Name & ", " & rs!Ts & ", " & rs!Id2
      Debug.Print str
 
      'free all resources
      rs.Close
      queryDef.Close
      conn.Close
      ws.Close
 
      End Sub
 

Info Catalog

(mysql.info) ado--rs-addnew

(mysql.info) myodbc-with-vb

(mysql.info) rdo--rs-addnew-and-rs-update