举个例吧 dim cn as new adodb.connect dim res as new adodb.recordset 如在按扭块中加入下面两句 cn.close set res=nothing
set cn = nothing 不断开直接释放,可以形成一个RECORDSET的离线数据集,只是传说,初步测试可行,不知道压力测试结果怎么样
我是说断开所有连接,如果这个SQL Server上有多个连接,怎么将这些连接全部断开。
'断开与数据库sqldb的所有连接 Dim cn As New ADODB.Connection, sn As New ADODB.Recordset cn.ConnectionString = "provider=SQLOLEDB;server=(local);uid=sa;pwd=;database=master" cn.CursorLocation = adUseClient cn.Open sn.Open "select spid from sysprocesses where dbid=db_id('sqldb')", cn Do While Not sn.EOF cn.Execute "kill " & sn("spid") sn.MoveNext Loop sn.Close Set cn = Nothing
Conn.Close set Conn = Nothing
数据库的连接都放在master数据库中的sysprocesses 表里,要断开某个数据库的连接,把sysprocesses 表里的那个数据库的连接删除就行了:Private Sub Command1_Click() Dim cn As ADODB.Connection,rs As New ADODB.RecordSet cn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=登录名;Password=密码;Initial Catalog=master;Data Source=Sql服务器别名"'借助master数据库来删除数据库的连接 cn.CursorLocation=adUseClient cn.Open rs.Open "select spid from sysprocesses where dbid=db_id('你的数据库名)", cn Do While Not rs.EOF cn.Execute "kill " & rs("spid") '杀掉你的数据库的所有连接rs.MoveNext Loop rs.Close cn.Close Set rs=Nothing Set cn=Nothing End Sub
dim cn as new adodb.connect
dim res as new adodb.recordset
如在按扭块中加入下面两句
cn.close
set res=nothing
不断开直接释放,可以形成一个RECORDSET的离线数据集,只是传说,初步测试可行,不知道压力测试结果怎么样
Dim cn As New ADODB.Connection, sn As New ADODB.Recordset
cn.ConnectionString = "provider=SQLOLEDB;server=(local);uid=sa;pwd=;database=master"
cn.CursorLocation = adUseClient
cn.Open
sn.Open "select spid from sysprocesses where dbid=db_id('sqldb')", cn
Do While Not sn.EOF
cn.Execute "kill " & sn("spid")
sn.MoveNext
Loop
sn.Close
Set cn = Nothing
set Conn = Nothing
Dim cn As ADODB.Connection,rs As New ADODB.RecordSet
cn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=登录名;Password=密码;Initial Catalog=master;Data Source=Sql服务器别名"'借助master数据库来删除数据库的连接
cn.CursorLocation=adUseClient
cn.Open
rs.Open "select spid from sysprocesses where dbid=db_id('你的数据库名)", cn
Do While Not rs.EOF
cn.Execute "kill " & rs("spid") '杀掉你的数据库的所有连接rs.MoveNext
Loop
rs.Close
cn.Close
Set rs=Nothing
Set cn=Nothing
End Sub
set conn=nothing
经测试