Dim adocmd As New ADODB.Command With adocmd .ActiveConnection = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database Password=;Data Source=" & App.Path & "\bzh.mdb;" .CommandText = "insert into 打印板记录 select * from tmp order by 箱号" .CommandType = adCmdText .Execute End With With adocmd .ActiveConnection = Connection .CommandType = adCmdText .CommandText = "update 箱记录 set 托盘号=Null where 托盘号='无板号'" .Execute End With Set adocmd = Nothing
update 表名 SET 字段=值
创建一个更新查询,根据指定的条件更改指定表中的字段值。语法UPDATE table SET newvalue WHERE criteria;UPDATE 语句可分为以下几个部分:部分 描述 table 表的名称,其中包含要更改的数据。 newvalue 表达式,用来计算要插入更新记录中特定字段的值。 criteria 表达式,用来计算被更新的记录。只有符合表达式的记录才会被更新。 说明当需要更改多个记录,或要更改的记录在多重表中时,UPDATE 特别有用。 可以同时更改多个字段。下列示例为对联合王国的一家公司增加 10% 的 Order Amount (订货量)和 3% 的 Freight (货运):UPDATE Orders SET OrderAmount = OrderAmount * 1.1, Freight = Freight * 1.03 WHERE ShipCountry = 'UK';重点?UPDATE 不生成结果集。而且当使用更新查询更新记录之后,不能取消这次操作。如果想知道哪些记录被更新,先看一下使用相同的条件的选定查询的结果,然后运行更新查询。 ?随时注意维护数据的复制备份。如果更新了错误记录,可从备份副本中恢复这些数据。==========================================================================Option Explicit'工程->引用->Microsoft ActiveX Data Objects 2.0 Library (后面为版本号) Dim cn As New ADODB.Connection Dim rs As New ADODB.RecordsetPrivate Sub Command1_Click() cn.Execute "UPDATE Employees " _ & "SET ReportsTo = 5 " _ & "WHERE ReportsTo = 2;"
End SubPrivate Sub Form_Load() cn.ConnectionString = "DBQ=" & App.Path & "\TelePhone.mdb;DefaultDir=" & _ App.Path & ";Driver={Microsoft Access Driver (*.mdb)};" & _ "DriverId=25;FIL=MS Access;ImplicitCommitSync=Yes;" & _ "MaxBufferSize=512;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;" & _ "Threads=3;UID=ADMIN;UserCommitSync=Yes;PWD=admind1234;" cn.Open '查询字符串可以上这里查 'http://www.connectionstrings.com/ End SubPrivate Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer) On Error Resume Next rs.Close Set rs = Nothing cn.Close Set cn = Nothing End Sub
我这部分的代码如下:access数据库 Option Explicit Dim rs_htluru As New ADODB.Recordset '合同录入 Dim rs_htjiekuan As New ADODB.Recordset '合同结款Dim ID As String '记录合同编号Private Sub cmdCancel_Click() cmdModify.Caption = "修 改" Text1.Text = rs_htluru.Fields(0) Text1.BackColor = &HFFFF80 Combo1.Text = rs_htluru.Fields(1) Text14.Text = rs_htluru.Fields(2) Combo2.Text = rs_htluru.Fields(3) Combo3.Text = rs_htluru.Fields(4) Text13.Text = rs_htluru.Fields(5) Text12.Text = rs_htluru.Fields(6) Text11.Text = rs_htluru.Fields(7) Text9.Text = rs_htluru.Fields(8) Combo4.Text = rs_htluru.Fields(9) DTPicker1.Value = rs_htluru.Fields(10) Text10.Text = rs_htluru.Fields(11) Combo5.Text = rs_htluru.Fields(12) 'Text2.Text = rs_htluru.Fields(1) Text1.Enabled = True Combo1.Enabled = False Combo3.Enabled = False Text12.Enabled = False DTPicker1.Enabled = False Text14.Enabled = False Text13.Enabled = False Text11.Enabled = False Combo5.Enabled = False Combo2.Enabled = False Combo4.Enabled = False Text9.Enabled = False Text10.Enabled = FalseEnd SubPrivate Sub cmdExit_Click() Unload Me End SubPrivate Sub cmdModify_Click() '合同变更的修改 Dim sql As String Dim XX As String Dim strSQL As String If cmdModify.Caption = "修 改" Then rs_htjiekuan.Close sql = "select * from 合同结款 where 合同编号='" & Text1.Text & "' and 本次结款 <> Val(0) and 类型<>'初始值'" rs_htjiekuan.CursorLocation = adUseClient rs_htjiekuan.Open sql, conn, adOpenKeyset, adLockPessimistic If rs_htjiekuan.RecordCount >= 1 Then MsgBox "对不起,此合同已有结款记录,不可变更!!", vbOKOnly + vbExclamation Text1.SetFocus Exit Sub Else 'Text2.Enabled = True
要看后台所使用的数据库的update的语法。
数据库:SQL2000
Dim adocmd As New ADODB.Command
With adocmd
.ActiveConnection = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database Password=;Data Source=" & App.Path & "\bzh.mdb;"
.CommandText = "insert into 打印板记录 select * from tmp order by 箱号"
.CommandType = adCmdText
.Execute
End With With adocmd
.ActiveConnection = Connection
.CommandType = adCmdText
.CommandText = "update 箱记录 set 托盘号=Null where 托盘号='无板号'"
.Execute
End With
Set adocmd = Nothing
SET newvalue
WHERE criteria;UPDATE 语句可分为以下几个部分:部分 描述
table 表的名称,其中包含要更改的数据。
newvalue 表达式,用来计算要插入更新记录中特定字段的值。
criteria 表达式,用来计算被更新的记录。只有符合表达式的记录才会被更新。
说明当需要更改多个记录,或要更改的记录在多重表中时,UPDATE 特别有用。
可以同时更改多个字段。下列示例为对联合王国的一家公司增加 10% 的 Order Amount (订货量)和 3% 的 Freight (货运):UPDATE Orders
SET OrderAmount = OrderAmount * 1.1,
Freight = Freight * 1.03
WHERE ShipCountry = 'UK';重点?UPDATE 不生成结果集。而且当使用更新查询更新记录之后,不能取消这次操作。如果想知道哪些记录被更新,先看一下使用相同的条件的选定查询的结果,然后运行更新查询。
?随时注意维护数据的复制备份。如果更新了错误记录,可从备份副本中恢复这些数据。==========================================================================Option Explicit'工程->引用->Microsoft ActiveX Data Objects 2.0 Library (后面为版本号)
Dim cn As New ADODB.Connection
Dim rs As New ADODB.RecordsetPrivate Sub Command1_Click()
cn.Execute "UPDATE Employees " _
& "SET ReportsTo = 5 " _
& "WHERE ReportsTo = 2;"
End SubPrivate Sub Form_Load()
cn.ConnectionString = "DBQ=" & App.Path & "\TelePhone.mdb;DefaultDir=" & _
App.Path & ";Driver={Microsoft Access Driver (*.mdb)};" & _
"DriverId=25;FIL=MS Access;ImplicitCommitSync=Yes;" & _
"MaxBufferSize=512;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;" & _
"Threads=3;UID=ADMIN;UserCommitSync=Yes;PWD=admind1234;"
cn.Open
'查询字符串可以上这里查
'http://www.connectionstrings.com/
End SubPrivate Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
On Error Resume Next
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub
Option Explicit
Dim rs_htluru As New ADODB.Recordset '合同录入
Dim rs_htjiekuan As New ADODB.Recordset '合同结款Dim ID As String '记录合同编号Private Sub cmdCancel_Click()
cmdModify.Caption = "修 改"
Text1.Text = rs_htluru.Fields(0)
Text1.BackColor = &HFFFF80
Combo1.Text = rs_htluru.Fields(1)
Text14.Text = rs_htluru.Fields(2)
Combo2.Text = rs_htluru.Fields(3)
Combo3.Text = rs_htluru.Fields(4)
Text13.Text = rs_htluru.Fields(5)
Text12.Text = rs_htluru.Fields(6)
Text11.Text = rs_htluru.Fields(7)
Text9.Text = rs_htluru.Fields(8)
Combo4.Text = rs_htluru.Fields(9)
DTPicker1.Value = rs_htluru.Fields(10)
Text10.Text = rs_htluru.Fields(11)
Combo5.Text = rs_htluru.Fields(12)
'Text2.Text = rs_htluru.Fields(1)
Text1.Enabled = True
Combo1.Enabled = False
Combo3.Enabled = False
Text12.Enabled = False
DTPicker1.Enabled = False
Text14.Enabled = False
Text13.Enabled = False
Text11.Enabled = False
Combo5.Enabled = False
Combo2.Enabled = False
Combo4.Enabled = False
Text9.Enabled = False
Text10.Enabled = FalseEnd SubPrivate Sub cmdExit_Click()
Unload Me
End SubPrivate Sub cmdModify_Click() '合同变更的修改
Dim sql As String
Dim XX As String
Dim strSQL As String
If cmdModify.Caption = "修 改" Then
rs_htjiekuan.Close
sql = "select * from 合同结款 where 合同编号='" & Text1.Text & "' and 本次结款 <> Val(0) and 类型<>'初始值'"
rs_htjiekuan.CursorLocation = adUseClient
rs_htjiekuan.Open sql, conn, adOpenKeyset, adLockPessimistic
If rs_htjiekuan.RecordCount >= 1 Then
MsgBox "对不起,此合同已有结款记录,不可变更!!", vbOKOnly + vbExclamation
Text1.SetFocus
Exit Sub
Else
'Text2.Enabled = True
Text1.Enabled = True
Combo1.Enabled = True
Combo3.Enabled = True
Text12.Enabled = True
DTPicker1.Enabled = True
Text14.Enabled = True
Text13.Enabled = True
Text11.Enabled = True
Combo5.Enabled = True
Combo2.Enabled = True
Combo4.Enabled = True
Text9.Enabled = True
Text10.Enabled = True
cmdModify.Caption = "保 存"
cmdCancel.Enabled = True
End If
Else
'sql = "update 合同录入 Set 合同编号='" & Text1.Text & "',客户名称='" & Combo1.Text & "' where 合同编号='" & ID & "'"(此处出错,提示"编译错误.要实现对某记录的修改,此处用update语句如何做?)
End If
End SubPrivate Sub Combo1_Change()
Text2.Text = Combo1.Text
End SubPrivate Sub Command1_Click()
Dim sql As Stringrs_htluru.Close
sql = "select 合同编号,客户名称,项目名称,项目分类,城市,面积,总合同款,预付款,余款,负责人,签单日期,备注,状态 from 合同录入 where 合同编号='" & Text1.Text & "'"
rs_htluru.CursorLocation = adUseClient
rs_htluru.Open sql, conn, adOpenKeyset, adLockPessimistic
If rs_htluru.RecordCount = 0 Then
MsgBox "对不起,没有符合检索条件的记录!!", vbOKOnly + vbExclamation
Text1.SetFocus
Exit Sub
End If
ID = Text1.Text
'Text1.Text = rs_htluru.Fields(0)
Text1.BackColor = &HFFFF80
Combo1.Text = rs_htluru.Fields(1)
Text14.Text = rs_htluru.Fields(2)
Combo2.Text = rs_htluru.Fields(3)
Combo3.Text = rs_htluru.Fields(4)
Text13.Text = rs_htluru.Fields(5)
Text12.Text = rs_htluru.Fields(6)
Text11.Text = rs_htluru.Fields(7)
Text9.Text = rs_htluru.Fields(8)
Combo4.Text = rs_htluru.Fields(9)
DTPicker1.Value = rs_htluru.Fields(10)
Text10.Text = rs_htluru.Fields(11)
Combo5.Text = rs_htluru.Fields(12)
Text2.Text = rs_htluru.Fields(1)End Sub
Private Sub Form_Load()
Dim sql As String'On Error GoTo loaderrorcmdCancel.Enabled = False '"确定"按钮设为无效
sql = "select * from 合同录入"
rs_htluru.CursorLocation = adUseClient
rs_htluru.Open sql, conn, adOpenKeyset, adLockPessimistic
sql = "select * from 合同结款"
rs_htjiekuan.CursorLocation = adUseClient
rs_htjiekuan.Open sql, conn, adOpenKeyset, adLockPessimistic
'loaderror:
'MsgBox Err.Description
End SubPrivate Sub Form_Unload(Cancel As Integer)
rs_htluru.Close
rs_htjiekuan.Close
End Sub