'执行sql语句并返回adodb.recordset值
Public Function ExecuteSql(ByVal sql As String) As ADODB.Recordset
On Error GoTo Errormsg
Dim sqlcon As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
sqlcon.Provider = "sqloledb"
sqlcon.Properties("Data Source").Value = ServerDb '// SLMAIL 为SQL Server 服务器名
sqlcon.Properties("Initial Catalog").Value = Database '//已创建数据库名
sqlcon.Properties("persist security info").Value = "False"
sqlcon.Properties("user id").Value = SqlUserId
sqlcon.Properties("password").Value = SqlPassword
sqlcon.Open
cmd.ActiveConnection = sqlcon
cmd.CommandText = sql
Set ExecuteSql = cmd.Execute
'sqlcon.Close
If 1 <> 1 Then
Errormsg: MsgBox Error
End If
End Function这个是我用的sql连接。为啥客户端2个人同时用就不行呢。必须一个人查询完毕。另外一个人才可以查询。。该怎么改一下?
Public Function ExecuteSql(ByVal sql As String) As ADODB.Recordset
On Error GoTo Errormsg
Dim sqlcon As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
sqlcon.Provider = "sqloledb"
sqlcon.Properties("Data Source").Value = ServerDb '// SLMAIL 为SQL Server 服务器名
sqlcon.Properties("Initial Catalog").Value = Database '//已创建数据库名
sqlcon.Properties("persist security info").Value = "False"
sqlcon.Properties("user id").Value = SqlUserId
sqlcon.Properties("password").Value = SqlPassword
sqlcon.Open
cmd.ActiveConnection = sqlcon
cmd.CommandText = sql
Set ExecuteSql = cmd.Execute
'sqlcon.Close
If 1 <> 1 Then
Errormsg: MsgBox Error
End If
End Function这个是我用的sql连接。为啥客户端2个人同时用就不行呢。必须一个人查询完毕。另外一个人才可以查询。。该怎么改一下?
On Error GoTo Errormsg
Dim sqlcon As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
sqlcon.Provider = "sqloledb"
sqlcon.Properties("Data Source").Value = ServerDb '// SLMAIL 为SQL Server 服务器名
sqlcon.Properties("Initial Catalog").Value = Database '//已创建数据库名
sqlcon.Properties("persist security info").Value = "False"
sqlcon.Properties("user id").Value = SqlUserId
sqlcon.Properties("password").Value = SqlPassword
sqlcon.Open
cmd.ActiveConnection = sqlcon
cmd.CommandText = sql
set rs=cmd.Execute
Set ExecuteSql = rs
'应该加上
set rs=nothing
set cmd=nothing
set sqlcon=nothing
'sqlcon.Close
exit function
Errormsg: MsgBox Error
End If
End Function
set rs=nothing
set cmd=nothing
set sqlcon=nothing
还是修改一下游标?
sqlcon.CursorLocation = adUseClient
如果不行,可能你用来数据绑定、或者对 RecordSet 进行了修改,那么需要设法缩短锁定时间,需要具体问题具体对待。