用 ADO 访问数据库时支持事务,不过不是很好;最好用 COM+ 来实现! Private Sub Command1_Click() On Error GoTo errorHandlerDim adoCnn As New ADODB.Connection Dim adoRst As New ADODB.Recordset Dim BeginTransBol As Boolean '是否已开始一个 ado 事务 Dim CnnStr As String Dim cSql As StringBeginTransBol = 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 .IsolationLevel = adXactReadUncommitted ' adXactReadCommitted .BeginTrans '开始一个事务 BeginTransBol = True
cSql = "DELETE * FROM report_head1" .Execute cSql, , adCmdText + adExecuteNoRecords '参数 adExecuteNoRecords 表示不返回任何记录集,可提高执行速度 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, "出错拉 . . ."End Sub
Dim con as adodb.connection con.BeginTrans '开始事务处理 con.CommitTrans '提交事务处理 con.RollbackTrans '回退事务就知道这些了
Dim con as adodb.connection con.BeginTrans '开始事务处理 con.CommitTrans '提交事务处理 con.RollbackTrans '回退事务
Private Sub Command1_Click()
On Error GoTo errorHandlerDim adoCnn As New ADODB.Connection
Dim adoRst As New ADODB.Recordset
Dim BeginTransBol As Boolean '是否已开始一个 ado 事务
Dim CnnStr As String
Dim cSql As StringBeginTransBol = 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 .IsolationLevel = adXactReadUncommitted ' adXactReadCommitted
.BeginTrans '开始一个事务
BeginTransBol = True
cSql = "DELETE * FROM report_head1"
.Execute cSql, , adCmdText + adExecuteNoRecords '参数 adExecuteNoRecords 表示不返回任何记录集,可提高执行速度 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, "出错拉 . . ."End Sub
con.CommitTrans '提交事务处理
con.RollbackTrans '回退事务就知道这些了
con.CommitTrans '提交事务处理
con.RollbackTrans '回退事务