这个是一个窗体的代码,你试试,要引用ado Dim rs As ADODB.Recordset Dim msgtext As String Private Sub Command1_Click() Dim strsql As String strsql = "select * from qx_table " Set rs = ExecuteSQL(strsql, msgtext) rs.addnew rs.fields(1)=text1.text ...... rs.update rs.close msgbox "添加成功" End Sub 这是数据库连接函数,你放到类模块中 Dim msgtext As String Dim mrc As ADODB.RecordsetPublic Function ExecuteSQL(ByVal sql As String, MsgString As String) As ADODB.Recordset Dim cnn As ADODB.Connection Dim rst As ADODB.Recordset Dim sTokens() As String 'Dim SQL As String On Error GoTo ExecuteSQL_Error sTokens = Split(sql) Set cnn = New ADODB.Connection cnn.Open ConnectString If InStr("INSERT,DELETE,UPDATE", UCase$(sTokens(0))) Then cnn.Execute sql MsgString = sTokens(0) & "query successful" Else Set rst = New ADODB.Recordset rst.Open Trim$(sql), cnn, adOpenKeyset, adLockOptimistic
Set ExecuteSQL = rst
MsgString = "查询到" & rst.RecordCount & "条纪录" End If ExecuteSQL_Exit: Set rst = Nothing Exit Function Set cnn = Nothing ExecuteSQL_Error: MsgString = "查询错误:" & Err.Description Resume ExecuteSQL_Exit End FunctionPublic Function ConnectString() As String ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\计划管理系统.mdb;Persist Security Info=False" 'ConnectString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=aa;Data Source=RJB-CL" End Function
to cuilei197979(风) :我用的是 cnn.execute "delete * from tb where id=001"ado.addnew ...ado.update我想如果有该id(主索引)存在,不用执行 cnn.execute "delete * from tb where id=001"ado.addnew直接用...ado.update 感兴趣的朋友可以试试:建立一个库,定义索引,用ado.update就不能更新存在的记录。用SQL语句就可以。
to winehero(编程人生) 要是你有空,试试建立一个库,表tb,字段 id,name,age,id定义唯一索引idxId,用ado.update就不能更新存在的记录。用SQL语句UPDATE就可以。
to vbman2003(家人) rst.open cnn,动态,批量锁定 --后两个参数想不起来了,只大概记得 用中文表示了
to vbman2003(家人) 错了rst.open "tb", cnn,动态,批量锁定 --后两个参数想不起来了,只大概记得 用中文表示了
你试试: dim rs as adodb.recordset sql="select * from tb where id=001" rs.open sql,cn,adopenkeyset, adlockoptimistic 直接用 ..... rs.update应该没有问题的,不能打开所有数据记录集
Dim rs As ADODB.Recordset
Dim msgtext As String
Private Sub Command1_Click()
Dim strsql As String
strsql = "select * from qx_table "
Set rs = ExecuteSQL(strsql, msgtext)
rs.addnew
rs.fields(1)=text1.text
......
rs.update
rs.close
msgbox "添加成功"
End Sub
这是数据库连接函数,你放到类模块中
Dim msgtext As String
Dim mrc As ADODB.RecordsetPublic Function ExecuteSQL(ByVal sql As String, MsgString As String) As ADODB.Recordset
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim sTokens() As String
'Dim SQL As String
On Error GoTo ExecuteSQL_Error
sTokens = Split(sql)
Set cnn = New ADODB.Connection
cnn.Open ConnectString
If InStr("INSERT,DELETE,UPDATE", UCase$(sTokens(0))) Then
cnn.Execute sql
MsgString = sTokens(0) & "query successful"
Else
Set rst = New ADODB.Recordset
rst.Open Trim$(sql), cnn, adOpenKeyset, adLockOptimistic
Set ExecuteSQL = rst
MsgString = "查询到" & rst.RecordCount & "条纪录"
End If
ExecuteSQL_Exit:
Set rst = Nothing
Exit Function
Set cnn = Nothing
ExecuteSQL_Error:
MsgString = "查询错误:" & Err.Description
Resume ExecuteSQL_Exit
End FunctionPublic Function ConnectString() As String
ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\计划管理系统.mdb;Persist Security Info=False"
'ConnectString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=aa;Data Source=RJB-CL"
End Function
我没有用SQL的update方法,因为字段较多,用程序执行比较容易。
cnn.execute "delete * from tb where id=001"ado.addnew ...ado.update我想如果有该id(主索引)存在,不用执行
cnn.execute "delete * from tb where id=001"ado.addnew直接用...ado.update
感兴趣的朋友可以试试:建立一个库,定义索引,用ado.update就不能更新存在的记录。用SQL语句就可以。
要是你有空,试试建立一个库,表tb,字段 id,name,age,id定义唯一索引idxId,用ado.update就不能更新存在的记录。用SQL语句UPDATE就可以。
dim rs as adodb.recordset
sql="select * from tb where id=001"
rs.open sql,cn,adopenkeyset, adlockoptimistic
直接用
.....
rs.update应该没有问题的,不能打开所有数据记录集
===========================================
这个可以另外讨论,在我的实践中,应该没有问题
就你更新的问题,你更新的只是一条数据,所以关键是只能打开要更新数据的记录集