用 ADO 访问数据库时支持事务,不过不是很好;最好用 COM+ 来实现!
Private Sub Command1_Click()
On Error GoTo errorHandler
Dim adoCnn As New ADODB.Connection
Dim adoRst As New ADODB.Recordset
Dim BeginTransBol As Boolean '是否已开始一个 ado 事务
Dim CnnStr As String
Dim cSql As String
BeginTransBol = False
CnnStr = "Provider=SQLOLEDB.1;Persist Security Info=True; Initial Catalog=[数据库名];Data Source=[服务器名]; User ID=sa;Password=;" '基于 用户名和密码的访问
'CnnStr = "Provider=SQLOLEDB.1;Persist Security Info=False;Initial Catalog=[数据库名];Data Source=[服务器名]; Integrated Security=SSPI;" '基于 windows 集成的安全访问
'CnnStr = "provider=Microsoft.Jet.OLEDB.4.0;Data source =" + [Access97、Access2000 数据库路径及名称] + " ;Persist Security Info=False;Jet OLEDB:Database Password=" + [数据库密码] '访问 Access 数据库
With adoCnn
If .State = adStateOpen Then .Close
.ConnectionString = CnnStr
.Open
If .State = adStateOpen Then
' 数据库打开成功
' 开始一个事物,设置事务的隔离层为 adXactReadCommitted
cSql = "SELECT * FROM report_head1 WHERE 编号 = " + Text1.Text
With adoRst
If .State = adStateOpen Then .Close
.ActiveConnection = adoCnn
.CursorLocation = adUseClient
.LockType = adLockBatchOptimistic
.CursorType = adOpenStatic
' 当查询为多个表的联合查询同时更新只对其中的一个表进行时,可用下面的语句显式指定要更新的表名
.Properties("Unique Table") = "report_head1"
.Source = cSql
.Open
' Disconnect the recordset 切断记录集
Set .ActiveConnection = Nothing
If .RecordCount <> 0 Then
MsgBox "编号已经存在,请重新输入!", vbInformation, "录入重复. . ."
Text1.SetFocus
Else
.AddNew
'开始添加记录
.Fields("编号").Value = Text1.Text
'...
' Reconnect and update Access or SQL Server
.ActiveConnection = adoCnn
.UpdateBatch
End If
End With
.CommitTrans '提交当前事务
BeginTransBol = False
Else
' 数据库打开失败
' ...
End If
End With
Exit Sub
errorHandler:
If adoCnn.State = adStateOpen And BeginTransBol Then adoCnn.RollbackTrans '有错误发生,回滚当前事务
MsgBox Err.Description + "(错误号:" + CStr(Err.Number) + ")", vbCritical, "出错拉 . . ."