我程序里写的类模块,你参考一下: 'gCon为公共连接,类型为ADODB.Connection Private mvarManageName As String Private mvarRoadName As String Private mvarIdNum As Long Private mvarStandbyOne As String Private mvarStandbyTwo As Long Public Sub Update() Dim recFind As ADODB.Recordset Dim strSql As String On Error GoTo ErrHandler gCon.BeginTrans If mvarManageName = "" Then Err.Raise vbObjectError + 50001 If mvarRoadName = "" Then Err.Raise vbObjectError + 50002 strSql = "update name set name_manage='" & mvarManageName & "',name_road='" & mvarRoadName & "' where name_id=" & mvarIdNum & "" gCon.Execute strSql gCon.CommitTrans Exit Sub ErrHandler: gCon.RollbackTrans Select Case Err.Number Case vbObjectError + 50001 Err.Clear Err.Raise vbError + 50001, , "名称不许为空!" Case vbObjectError + 50002 Err.Clear Err.Raise vbError + 50002, , "街道不许为空!" Case Else Err.Clear Err.Raise vbError + 51000, , "更新资料 [" & mvarManageName & "] 出错!" End Select
End SubPublic Sub Delete() Dim strSql As String On Error GoTo ErrHandler gCon.BeginTrans strSql = "delete from name where name_id=" & mvarIdNum & "" gCon.Execute strSql strSql = "delete from address where address_name_id=" & mvarIdNum & "" gCon.Execute strSql gCon.CommitTrans Exit Sub ErrHandler: gCon.RollbackTrans Err.Raise vbError + 50001, , "删除操作失败!" Err.Clear End SubPublic Function AddNew() As Long Dim recFind As ADODB.Recordset Dim strSql As String On Error GoTo ErrHandler gCon.BeginTrans If mvarManageName = "" Then Err.Raise vbObjectError + 50001 If mvarRoadName = "" Then Err.Raise vbObjectError + 50002 strSql = "inSert into name (name_manage,name_road) values('" & mvarManageName & "','" & mvarRoadName & "')" gCon.Execute strSql strSql = "select name_id from name order by name_id desc" Set recFind = New ADODB.Recordset recFind.Open strSql, gCon, adOpenStatic, adLockReadOnly, adCmdText AddNew = recFind!name_id gCon.CommitTrans Exit Function ErrHandler: gCon.RollbackTrans Select Case Err.Number Case vbObjectError + 50001 Err.Clear Err.Raise vbError + 50001, , "名称不许为空!" Case vbObjectError + 50002 Err.Clear Err.Raise vbError + 50002, , "街道不许为空!" Case Else Err.Clear Err.Raise vbError + 51000, , "新增资料 [" & mvarManageName & "] 出错!" End Select End Function Public Function QueryAll() As ADODB.Recordset Dim strSql As String On Error GoTo ErrHandler strSql = "select * from name order by name_id desc" Set QueryAll = gCon.Execute(strSql) Exit Function ErrHandler: MsgBox Err.Description, vbCritical, "错误" Err.Clear End Function Public Function QueryId() As BooleanEnd FunctionPublic Property Let StandbyTwo(ByVal vData As Long) mvarStandbyTwo = vData End Property Public Property Get StandbyTwo() As Long StandbyTwo = mvarStandbyTwo End PropertyPublic Property Let StandbyOne(ByVal vData As String) mvarStandbyOne = vData End Property Public Property Get StandbyOne() As String StandbyOne = mvarStandbyOne End PropertyPublic Property Let IdNum(ByVal vData As Long) mvarIdNum = vData End Property Public Property Get IdNum() As Long IdNum = mvarIdNum End PropertyPublic Property Let RoadName(ByVal vData As String) mvarRoadName = vData End Property Public Property Get RoadName() As String RoadName = mvarRoadName End PropertyPublic Property Let ManageName(ByVal vData As String) mvarManageName = vData End Property Public Property Get ManageName() As String ManageName = mvarManageName End Property
Public Function RecordAddEdit(SQLstr As String) As Boolean
Dim cn As New ADODB.Connection
On Error GoTo E cn.ConnectionString = DataEnvironment1.Connection1.ConnectionString cn.Open cn.Execute SQLstr, , adExecuteNoRecords cn.Close Set cn = Nothing RecordAddEdit = True Exit Function E: RecordAddEdit = False End Function
3个函数,在一个类模块中
dim i as integer rs.add
for i= 0 to ubound(list)
rs.field(i)=list(i)
next
rs.update
end sub
rs 为公共变量,类型adodb.recordset
list输入一个数组,与各字段相关。当然,如果有其它数据类型,可以用type。
'gCon为公共连接,类型为ADODB.Connection
Private mvarManageName As String
Private mvarRoadName As String
Private mvarIdNum As Long
Private mvarStandbyOne As String
Private mvarStandbyTwo As Long
Public Sub Update()
Dim recFind As ADODB.Recordset
Dim strSql As String
On Error GoTo ErrHandler
gCon.BeginTrans
If mvarManageName = "" Then Err.Raise vbObjectError + 50001
If mvarRoadName = "" Then Err.Raise vbObjectError + 50002
strSql = "update name set name_manage='" & mvarManageName & "',name_road='" & mvarRoadName & "' where name_id=" & mvarIdNum & ""
gCon.Execute strSql
gCon.CommitTrans
Exit Sub
ErrHandler:
gCon.RollbackTrans
Select Case Err.Number
Case vbObjectError + 50001
Err.Clear
Err.Raise vbError + 50001, , "名称不许为空!"
Case vbObjectError + 50002
Err.Clear
Err.Raise vbError + 50002, , "街道不许为空!"
Case Else
Err.Clear
Err.Raise vbError + 51000, , "更新资料 [" & mvarManageName & "] 出错!"
End Select
End SubPublic Sub Delete()
Dim strSql As String
On Error GoTo ErrHandler
gCon.BeginTrans
strSql = "delete from name where name_id=" & mvarIdNum & ""
gCon.Execute strSql
strSql = "delete from address where address_name_id=" & mvarIdNum & ""
gCon.Execute strSql
gCon.CommitTrans
Exit Sub
ErrHandler:
gCon.RollbackTrans
Err.Raise vbError + 50001, , "删除操作失败!"
Err.Clear
End SubPublic Function AddNew() As Long
Dim recFind As ADODB.Recordset
Dim strSql As String
On Error GoTo ErrHandler
gCon.BeginTrans
If mvarManageName = "" Then Err.Raise vbObjectError + 50001
If mvarRoadName = "" Then Err.Raise vbObjectError + 50002
strSql = "inSert into name (name_manage,name_road) values('" & mvarManageName & "','" & mvarRoadName & "')"
gCon.Execute strSql
strSql = "select name_id from name order by name_id desc"
Set recFind = New ADODB.Recordset
recFind.Open strSql, gCon, adOpenStatic, adLockReadOnly, adCmdText
AddNew = recFind!name_id
gCon.CommitTrans
Exit Function
ErrHandler:
gCon.RollbackTrans
Select Case Err.Number
Case vbObjectError + 50001
Err.Clear
Err.Raise vbError + 50001, , "名称不许为空!"
Case vbObjectError + 50002
Err.Clear
Err.Raise vbError + 50002, , "街道不许为空!"
Case Else
Err.Clear
Err.Raise vbError + 51000, , "新增资料 [" & mvarManageName & "] 出错!"
End Select
End Function
Public Function QueryAll() As ADODB.Recordset
Dim strSql As String
On Error GoTo ErrHandler
strSql = "select * from name order by name_id desc"
Set QueryAll = gCon.Execute(strSql)
Exit Function
ErrHandler:
MsgBox Err.Description, vbCritical, "错误"
Err.Clear
End Function
Public Function QueryId() As BooleanEnd FunctionPublic Property Let StandbyTwo(ByVal vData As Long)
mvarStandbyTwo = vData
End Property
Public Property Get StandbyTwo() As Long
StandbyTwo = mvarStandbyTwo
End PropertyPublic Property Let StandbyOne(ByVal vData As String)
mvarStandbyOne = vData
End Property
Public Property Get StandbyOne() As String
StandbyOne = mvarStandbyOne
End PropertyPublic Property Let IdNum(ByVal vData As Long)
mvarIdNum = vData
End Property
Public Property Get IdNum() As Long
IdNum = mvarIdNum
End PropertyPublic Property Let RoadName(ByVal vData As String)
mvarRoadName = vData
End Property
Public Property Get RoadName() As String
RoadName = mvarRoadName
End PropertyPublic Property Let ManageName(ByVal vData As String)
mvarManageName = vData
End Property
Public Property Get ManageName() As String
ManageName = mvarManageName
End Property
Dim cn As New ADODB.Connection
On Error GoTo E
cn.ConnectionString = DataEnvironment1.Connection1.ConnectionString
cn.Open
cn.Execute SQLstr, , adExecuteNoRecords
cn.Close
Set cn = Nothing
RecordAddEdit = True
Exit Function
E:
RecordAddEdit = False
End Function