Option Explicit‘这是公共模块Public adocon As ADODB.Connection
Public adminp As Boolean
Public readp As Boolean
Public username As StringPublic Function executesql(ByVal sql As String) As ADODB.Recordset
Dim rst As ADODB.Recordset
Set adocon = New ADODB.Connection
adocon.CursorLocation = adUseClient
adocon.ConnectionString = Connstring
adocon.Open
Dim stokens() As String
On Error GoTo executesql_error
stokens = Split(sql, " ")
If InStr("inser,delete,update", UCase(stokens(0))) Then
adocon.Execute sql
Else
Set rst = New ADODB.Recordset
rst.Open Trim(sql), adocon, adOpenKeyset, adLockOptimistic
Set executesql = rst
End If
executesql_exit:
Set rst = Nothing
Set adocon = Nothing
Exit Function
executesql_error:
Resume executesql_exit
End FunctionPublic Function Connstring() As StringConnstring = "provider = microsoft.Jet.OLEDB.4.0;Data source =" & App.Path & "\123.mdb "
End Function
Public Function executeqx() As Boolean
Dim sql As String
Dim rst As ADODB.Recordset
Set adocon = New ADODB.Connection
adocon.ConnectionString = Connstring
adocon.Open
Set rst = New ADODB.Recordset
sql = "select * from use where username='" & username & "'"
rst.Open Trim(sql), adocon, adOpenKeyset, adLockOptimistic
If rst.EOF = True Then
MsgBox "非法用户", vbExclamation + vbOKOnly, "警告"
executeqx = Null
Exit Function
End If
If rst.Fields("admin") Then
executeqx = True
Exit Function
ElseIf rst.Fields("readonly") Then
executeqx = False
End If
rst.Close
On Error GoTo executesql_error
executesql_exit:
Set rst = Nothing
Set adocon = Nothing
Exit Function
executesql_error:
Resume exectuesql_exit
End Function
’这是登录窗体代码
Private Sub Command1_Click()
Dim txtsql As String
Dim mrc As New ADODB.Recordset
txtsql = "select username form use where username=" & Trim(Text1.Text) & ""
Set mrc = executesql(txtsql)
If mrc.EOF = True Then
MsgBox "用户名错误!", vbExclamation + vbOKOnly, "警告"
Text1.SetFocus
Text1.SelStart = 0
Text1.SelLength = Len(Text1.Text)
Exit Sub
End If
username = mrc.Fields(0)
txtsql = "select username from use where password=" & Trim(Text2.Text) & ""
Set mrc = executesql(txtsql)
If mrc.EOF = True Then
MsgBox "密码错误!", vbExclamation + vbOKOnly, "警告"
Text2.SetFocus
Text2.SelStart = 0
Text2.SelLength = Len(Text1.Text)
Exit Sub
End If
If executeqx Then
adminp = True
Else
readp = True
End If
End Sub
Private Sub Command2_Click()
end
End Sub
运行时出现了“对象关闭时不允许操作”实时错误3704,不知是何故,请大家热指教
Public adminp As Boolean
Public readp As Boolean
Public username As StringPublic Function executesql(ByVal sql As String) As ADODB.Recordset
Dim rst As ADODB.Recordset
Set adocon = New ADODB.Connection
adocon.CursorLocation = adUseClient
adocon.ConnectionString = Connstring
adocon.Open
Dim stokens() As String
On Error GoTo executesql_error
stokens = Split(sql, " ")
If InStr("inser,delete,update", UCase(stokens(0))) Then
adocon.Execute sql
Else
Set rst = New ADODB.Recordset
rst.Open Trim(sql), adocon, adOpenKeyset, adLockOptimistic
Set executesql = rst
End If
executesql_exit:
Set rst = Nothing
Set adocon = Nothing
Exit Function
executesql_error:
Resume executesql_exit
End FunctionPublic Function Connstring() As StringConnstring = "provider = microsoft.Jet.OLEDB.4.0;Data source =" & App.Path & "\123.mdb "
End Function
Public Function executeqx() As Boolean
Dim sql As String
Dim rst As ADODB.Recordset
Set adocon = New ADODB.Connection
adocon.ConnectionString = Connstring
adocon.Open
Set rst = New ADODB.Recordset
sql = "select * from use where username='" & username & "'"
rst.Open Trim(sql), adocon, adOpenKeyset, adLockOptimistic
If rst.EOF = True Then
MsgBox "非法用户", vbExclamation + vbOKOnly, "警告"
executeqx = Null
Exit Function
End If
If rst.Fields("admin") Then
executeqx = True
Exit Function
ElseIf rst.Fields("readonly") Then
executeqx = False
End If
rst.Close
On Error GoTo executesql_error
executesql_exit:
Set rst = Nothing
Set adocon = Nothing
Exit Function
executesql_error:
Resume exectuesql_exit
End Function
’这是登录窗体代码
Private Sub Command1_Click()
Dim txtsql As String
Dim mrc As New ADODB.Recordset
txtsql = "select username form use where username=" & Trim(Text1.Text) & ""
Set mrc = executesql(txtsql)
If mrc.EOF = True Then
MsgBox "用户名错误!", vbExclamation + vbOKOnly, "警告"
Text1.SetFocus
Text1.SelStart = 0
Text1.SelLength = Len(Text1.Text)
Exit Sub
End If
username = mrc.Fields(0)
txtsql = "select username from use where password=" & Trim(Text2.Text) & ""
Set mrc = executesql(txtsql)
If mrc.EOF = True Then
MsgBox "密码错误!", vbExclamation + vbOKOnly, "警告"
Text2.SetFocus
Text2.SelStart = 0
Text2.SelLength = Len(Text1.Text)
Exit Sub
End If
If executeqx Then
adminp = True
Else
readp = True
End If
End Sub
Private Sub Command2_Click()
end
End Sub
运行时出现了“对象关闭时不允许操作”实时错误3704,不知是何故,请大家热指教
Set adocon = Nothing 去掉,开始处仅在 adocon is nothing 时才新建连接,Form_Unload 中调用 Set adocon = Nothing。
txtsql = "select username from use where username='" & Trim(Text1.Text) & "'"
所以肯定不是楼主写的
如果楼主有写这代码的水平,这点儿小问题根本就不是问题了
错误如下:
1.function:executesql的if else模块中并不是所有的分支都给executesql设置对象了
这样就有可能造成executesql没有设置就退出了function
因此建议:当判断为insert,delete ,update的时候,给executesql设置一个空recordset
2.既然connection设置为public了,就不要一会open,一会儿又set nothing,就在formload的时候打开它,unload的时候关闭它
3.function:executeqx中
rst.Close
On Error GoTo executesql_error
executesql_exit:
以及下面的executesql_error标签都可以去掉
程序都结束了,还on error有什么用
4.Sub Command1_Click中的txtsql中,看一下自己的from写成"form"了5建议:由于分析sql语句的时候可能出问题,所以对拆分sql那里我也没办法看
为了便于区分sql是查询还是update,建议用两个function:execsql和updatesql
execsql是返回只读的recordset,不允许删插改等操作,updatesql就可以进行任何操作
这样的话,就不用去分析sql语句的内容了
所以若是update,delete,insert等就用updatesql,返回值类型设置为boolean,标志成功或失败
若是select的就用execsql,返回recordset就OK了
你在调用Set mrc = executesql(txtsql)获取一个记录集对象
但实际上你在函数executesql的后面将这个记录集释放掉了Set rst = Nothing,所以会报这个错误,所以这一句应该去掉