# Re: Anser for "Action Query"

Posted by Spencer Yang on May 31, 1999 at 01:19:23:

In Reply to: 救命啊呀!!我快想得摸穿头!! posted by !!一个快发疯的人!!救命啊!! on May 30, 1999 at 17:45:28:

' 说明:
' 由于相当部分 vb user 直觉认为 data object 及 adodc object
' 可以处理新增、修改、删除...功能, 也可做到下列 "动作查询"
' . 产生资料表查询
' . 更新查询
' . 新增查询
' . 删除查询
' 实际上，data object 及 adodc object是无此项功能, 因为 data object
' 及 adodc object 是逐笔记录处理功能。

' 兹列举四种 vb 执行 动作查询方式
' 环境: access
' 表单: tbl1, tbl2(二者只有一栏 fld1)
' 查询: qry1

' method 1:
' execute SQL statement by using then Excute methd on a
' DAO Database object in code.
' Resource: Microsoft Press <>
' Second Edition pp. 72-73
Private Sub Command1_Click()
Dim dbs As Database
Dim strSQL As String
Dim strDBPath As String
strDBPath = "D:\MYDATA\ActionQueryTest.mdb"
strSQL = "INSERT INTO tbl1 ( fld1 ) SELECT tbl2.fld1 FROM tbl2"
Set dbs = OpenDatabase(strDBPath)
dbs.Execute strSQL
End Sub

' method 2:
' Anyother way to execute an SQL statement is to create a permanent
' DAO QueryDef object theat contains the SQL statement, and then use
' the Execute method to run the QueryDef object in code.
'
' if strSQL = "INSERT INTO tbl1 ( fld1 ) SELECT tbl2.fld1 FROM tbl2"
' have save as "qry1"
'
' Resource: Microsoft Press <>
' Second Edition pp. 72-73

Private Sub Command2_Click()
Dim dbs As Database
Dim qdf As QueryDef
Dim strSQL As String
Dim strDBPath As String
strDBPath = "D:\MYDATA\ActionQueryTest.mdb"
Set dbs = OpenDatabase(strDBPath)
Set qdf = dbs.QueryDefs("qry1")
qdf.Execute
End Sub

' method 3:
' use ADODB Connection object
' 来源: 王国荣编着 <> pp.294-295

Private Sub Command3_Click()
Dim conn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strConn As String
Dim strSQL As String
strConn = "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=D:\MYDATA\ActionQueryTest.mdb"
strSQL = "INSERT INTO tbl1 ( fld1 ) SELECT tbl2.fld1 FROM tbl2"
' 接上水源
conn.Open strConn
' 执行 SQL
conn.Execute strSQL
' 截断水源
conn.Close
End Sub

' Method 4:
' use ADODB Command object
' 来源: 王国荣编着 <> pp.294-295

Private Sub Command4_Click()
Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rst As New ADODB.Recordset
Dim strConn As String
Dim strSQL As String
strConn = "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=D:\MYDATA\ActionQueryTest.mdb"
strSQL = "INSERT INTO tbl1 ( fld1 ) SELECT tbl2.fld1 FROM tbl2"
' 接上水源
conn.Open strConn
' 执行 SQL
Set cmd.ActiveConnection = conn
cmd.CommandText = strSQL
cmd.Execute
' 截断水源
conn.Close
End Sub

Follow Ups: