Public Function ExecuteSQL(ByVal sql As String) As ADODB.Recordset Dim mycon As ADODB.Connection Dim rst As ADODB.Recordset Set mycon = New ADODB.Connection mycon.ConnectionString = connstring mycon.Open Dim stokens() As String On Error GoTo exectuesql_error stokens = Split(sql) If InStr("INSER,DELETE,UPDATE", UCase(stokens(0))) Then mycon.Execute sql Else Set rst = New ADODB.Recordset rst.Open Trim(sql), mycon, adOpenKeyset, adLockOptimistic Set ExecuteSQL = rst End If exectuesql_exit: Set rst = Nothing Set mycon = Nothing Exit Function exectuesql_error: Resume exectuesql_exit End FunctionPublic Function connstring() As String connstring = "provider=Microsoft.Jet.OLEDB.4.0;Data source =" & App.Path & "/aaaa.mdb" End Function
操作示例:1. Dim mrc1 As ADODB.Recordset txtsql = "select * from tablename where sex='女'" Set mrc1 = ExecuteSQL(txtsql)'条件显示2.ExecuteSQL("update tablename set myname='abc'")'修改
\2.ExecuteSQL("update tablename set myname='abc'")'修改\这个不用括号.因为不需要返回值. ExecuteSQL "update tablename set myname='abc'" 也可以这样 call ExecuteSQL("update tablename set myname='abc'")'修改
Dim mycon As ADODB.Connection
Dim rst As ADODB.Recordset
Set mycon = New ADODB.Connection
mycon.ConnectionString = connstring
mycon.Open
Dim stokens() As String
On Error GoTo exectuesql_error
stokens = Split(sql)
If InStr("INSER,DELETE,UPDATE", UCase(stokens(0))) Then
mycon.Execute sql
Else
Set rst = New ADODB.Recordset
rst.Open Trim(sql), mycon, adOpenKeyset, adLockOptimistic
Set ExecuteSQL = rst
End If
exectuesql_exit:
Set rst = Nothing
Set mycon = Nothing
Exit Function
exectuesql_error:
Resume exectuesql_exit
End FunctionPublic Function connstring() As String
connstring = "provider=Microsoft.Jet.OLEDB.4.0;Data source =" & App.Path & "/aaaa.mdb"
End Function
Dim mrc1 As ADODB.Recordset
txtsql = "select * from tablename where sex='女'"
Set mrc1 = ExecuteSQL(txtsql)'条件显示2.ExecuteSQL("update tablename set myname='abc'")'修改
问一下:mycon.ConnectionString = connstring 调用时不用加括号吗?
ExecuteSQL "update tablename set myname='abc'"
也可以这样
call ExecuteSQL("update tablename set myname='abc'")'修改
若还想执行CREATE,DROP...,用INSTR()比较。