sql – VBA新数据库连接

如何更改下面的代码以防止您在屏幕截图中看到的内容.

我正在使用以下代码运行宏

Dim conn As ADODB.Connection
Dim rec1 As ADODB.Recordset
Dim thissql As String
 
Set conn = New ADODB.Connection
 
Dim sConn As String
  sConn = "Provider=sqlOLEDB;Trusted_Connection=Yes;Server=xyz;Database=xyz;UID=xyz;PWD=xyz"
conn.Open sConn
 
' this is creating multiple connections.
Set rec1 = New ADODB.Recordset
rec1.Open thissql,conn

它运行sql Server查询(大约20行,包含4个连接).一切都很好,除了经过几次运行后我的数据库管理员说我的查询过多地加载了DB.

现在,我的查询可能导致问题,或者可能是Excel开始一次运行多个连接.对此的一些证据是下面的屏幕截图以及数据库上的负载似乎随时间增加的事实.

如何在不创建新连接的情况下建立数据库连接？

有没有人在使用Excel DB宏时遇到类似的问题？

UPDATE

虽然下面的答案非常有用(特别是对于以VBA开始的人),但似乎我的查询占用负载的主要原因是多个连接的组合并忽略了我的代码中的一行：

With Sheets("FVols").QueryTables.Add(Connection:=rec1,Destination:=Sheets("FVols").Range("A1"))
    .name = "data"
    .FieldNames = True
    .Refresh BackgroundQuery:=True <<<<<<<<<<<<<<<<<<<<<<<-----
 
End With

解决方法

您只需要打开一次连接.这实际上意味着您可以在该活动连接上执行多个查询.您必须关闭连接并释放引用(特别是ADODB)以避免遇到冲突和其他与连接相关的问题.

如果您知道要执行的查询,则可以创建一个数组(或集合)并向队列添加查询.

虽然您已经有一个可以使用的开放连接,但您可以继续执行查询.

扫描代码,你和我之间没有太大区别,所以你应该能够看到发生了什么,在哪里.如果有任何不清楚的地方,请在评论中提问

Sub DbConnection()
 
    Dim cn As ADODB.Connection
    Set cn = New ADODB.Connection
    Dim rs As ADODB.Recordset
 
    Dim strConn As String
    strConn = "Driver={sql Server};Server=; Database=; UID=; PWD="
 
    cn.Open strConn
 
    Dim queryArr,i
    queryArr = Array("SELECT * FROM [MyTable]","SELECT * FROM [MyOtherTable]")
 
    For i = LBound(queryArr) To UBound(queryArr)
        ExecuteQuery queryArr(i),cn,rs
    Next i
 
    cn.Close
    Set cn = Nothing
End Sub
 
Private Sub ExecuteQuery(query As Variant,ByRef cn As ADODB.Connection,ByRef rs As ADODB.Recordset)
    Set rs = New ADODB.Recordset
    With rs
        .ActiveConnection = cn
        .Open CStr(query)
        Sheets(1).Range("A1").CopyFromRecordset rs
        .Close
    End With
    Set rs = Nothing
End Sub

现在,您只需要执行一次DBConnection(),并且将执行您在数组中列出的所有查询.

或者,如果您的查询是在运行时创建的,则可以将其作为参数传递给DbConnection().

Sub DbConnection(queryQueue As Collection)
 
    Dim cn As ADODB.Connection
    Set cn = New ADODB.Connection
    Dim rs As ADODB.Recordset
 
 
    Dim strConn As String
    strConn = "Driver={sql Server};Server=HELIUM\PRI; Database=sourcedata; UID=tabula; PWD=Tabula123!"
 
    cn.Open strConn
 
    For i = 1 To queryQueue.Count
        ExecuteQuery queryQueue.Item(i),ByRef rs As ADODB.Recordset)
    Set rs = New ADODB.Recordset
    With rs
        .ActiveConnection = cn
        .Open CStr(query)
        Sheets(1).Range("A1").CopyFromRecordset rs
        .Close
    End With
    Set rs = Nothing
End Sub

更新：

您可以将连接声明为全局变量.现在,您可以根据需要多次运行DBConnection(),并且每次都不会创建新连接.相反,您将使用全局连接对象.

Option Explicit
 
Public cn As ADODB.Connection
 
Sub DbConnection()
 
    Set cn = New ADODB.Connection
    Dim rs As ADODB.Recordset
 
    Dim strConn As String
    strConn = "Driver={sql Server};Server=; Database=; UID=; PWD="
 
    cn.Open strConn
 
    Set rs = New ADODB.Recordset
    With rs
        .ActiveConnection = cn
        .Open "SELECT * FROM [MyTable]"
        Sheets(1).Range("A1").CopyFromRecordset rs
        .Close
    End With
    Set rs = Nothing
 
    cn.Close
    Set cn = Nothing
End Sub

sql – VBA新数据库连接

解决方法

猜你在找的MsSQL相关文章