ITEEDU

26.1.19.2. DAO: rs.addNew, rs.update和滚动

Private Sub myodbc_dao_Click()
 
Dim ws As Workspace
Dim conn As Connection
Dim queryDef As queryDef
Dim str As String
 
'connect to MySQL server using MySQL ODBC 3.51 Driver（使用MySQL ODBC 3.51驱动程序连接到MySQL）
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（创建表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（使用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..（rs更新）
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滚动）
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