如何编写通用函数来打开和关闭数据库连接？

Question

我是Excel VBA的初学者，非常感谢您的帮助。

请教如何创建一个通用函数来打开和数据库连接，以及另一个关闭它的函数，以避免重复编码？

---

这是我的代码。我坚持就如何继续......

Const connection_string As String = "Provider=SQLOLEDB.1;Password=XXX;Persist Security    `Info=True;User ID=sa;Initial Catalog=TESTDB;Data Source=XXXX;" 

Sub DuplicateDBConnection() 

'Declare variables 
Set cn1 = New ADODB.Connection 
Set cmd1 = New ADODB.Command 
Set rs1 = New ADODB.Recordset 

'Open Connection 
cn1.ConnectionString = connection_string 
cn1.Open 

'Set and Excecute SQL Command 
Set cmd1.ActiveConnection = cn1 
cmd1.CommandText = "Select stock_code, name, sector_id from stock_master" 
cmd1.CommandType = adCmdText 
cmd1.Execute 

'Open Recordset 
Set rs1.ActiveConnection = cn1 
rs1.Open cmd1 

'Copy Data to Excel 
ActiveSheet.Range("A1").CopyFromRecordset (rs1) 

'Close Connection 
rs1.Close 
cn1.Close 

'Throw Object 
Set rs1 = Nothing 
Set cn1 = Nothing 

End Sub 

---

我的愿望是写常见的功能，因此我不需要继续编写代码来连接和密切的联系。

Sub ConnectDB() 
    'Codes to connect DB 
End Sub 

Sub CloseConnnection() 
    'Codes to close connection 
End Sub 

Sub ExecuteCode() 
    ConnectDB 
    'Execute SQL command to manipulate data on excel and SQL database 
    CloseConnection 
End Sub 

---

编辑基于Kittoe的建议，现在工作正常。谢谢！

1. 类： a。创建了一个名为AdoDbHelper的类，Private Instancing b。在AdoDbHelper中，将“选项比较数据库”更改为“选项比较文本”

2. 模块： 创建一个这样的功能。下面

代码：

Const connection_string As String = "Provider=SQLOLEDB.1;Password=XXX;Persist Security    `Info=True;User ID=sa;Initial Catalog=TESTDB;Data Source=XXXX;" 

Sub Test() 

Dim sourceDb As New AdoDbHelper 
Dim sourceRs As New ADODB.Recordset 

sourceDb.Connect (connection_string) 

Set sourceRs = sourceDb.OpenRecordset("Select stock_code, name, sector_id from  stock_master") 

With sourceRs 
    'Do stuff! 

    ActiveSheet.Range("A1").CopyFromRecordset sourceRs 

    .Close 
End With 

sourceDb.Disconnect 

Set sourceRs = Nothing 
Set sourceDb = Nothing 

End Sub 

Answer 1

使用Microsoft ActiveX数据对象库XX：

在VBA窗口的工具>参考> Microsoft ActiveX数据对象库XX

我通常使用2.7来向下兼容。现在，您可以创建ADODB对象来打开连接，执行查询（选择/更新/删除/ ...）并将查询结果（称为记录集）添加到您的Excel中的其他表格中的特定单元格中。

就一定要使用相同的连接所有的时间，创建一个公共连接对象，是指在所有的子程序。在每个子程序首先检查它是否已经成立（conn是我ADODB.Connection对象）：

If conn = Nothing Then Call Setup_Connection 

而且子程序Setup_Connection是这样的：

Private Sub Setup_Connection 
    Dim strConnection as String 

    If conn = Nothing Then 
     'Choose your own database connection details 
     strConnection = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _ 
       "Persist Security Info=False;" & _ 
       "Initial Catalog=DatabaseName;" & _ 
       "Data Source=DATABASESERVER" 
     Set conn = New ADODB.Connection 
     With conn 
      .Open strConnection 
      .CommandTimeout = 30 
     End With 
    End If 
End Sub 

Answer 2

这种类型的东西最好是在做一类。右键单击“IDE”中的VBA项目，然后转到插入 - >类模块。为你的班级命名一些有意义的东西，如clsAdoHelper（如果匈牙利语是你的东西），AdoDbHelper或其他东西。以下是代码的一个例子，你就会把这个类：

Option Compare Database 
Option Explicit 

Private WithEvents conn As ADODB.Connection 
Private WithEvents rs As ADODB.Recordset 

Public Sub Connect(ConnectionString As String) 
    If Not conn Is Nothing Then 
     Debug.Print "A connection is already open." 
     Exit Sub 
    End If 

    If ConnectionString = CurrentProject.Connection.ConnectionString Then 
     Set conn = CurrentProject.Connection 
    Else 
     Set conn = New ADODB.Connection 
     conn.Open ConnectionString 
    End If 
End Sub 

Public Sub Disconnect() 
    If Not conn Is Nothing Then 
     If conn.State <> 0 Then 
      conn.Close 
     End If 

     Set conn = Nothing 
    End If 
End Sub 

Public Sub Execute(SQL As String) 
    If conn Is Nothing Then 
     Debug.Print "No connection open." 
     Exit Sub 
    End If 

    conn.Execute (SQL) 
End Sub 

Public Function OpenRecordset(SQL As String, Optional CursorLocation As ADODB.CursorLocationEnum = adUseClient, Optional CursorType As ADODB.CursorTypeEnum = adOpenForwardOnly, Optional LockType As ADODB.LockTypeEnum = adLockReadOnly) As ADODB.Recordset 
    If conn Is Nothing Then 
     Debug.Print "No connection open." 
     Exit Function 
    End If 

    If Not rs Is Nothing Then 
     Debug.Print "A recordset is already open." 
     Exit Function 
    End If 

    Set rs = New ADODB.Recordset 

    With rs 
     .CursorLocation = CursorLocation 
     .CursorType = CursorType 
     .LockType = LockType 
     .Open SQL, conn 
    End With 

    Set OpenRecordset = rs 
End Function 

Public Sub BeginTransaction() 
    conn.BeginTrans 
End Sub 

Public Sub CommitTransaction() 
    conn.CommitTrans 
End Sub 

Public Sub RollbackTransaction() 
    conn.RollbackTrans 
End Sub 

Private Sub conn_BeginTransComplete(ByVal TransactionLevel As Long, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection) 
    Debug.Print "Transaction started." 
End Sub 

Private Sub conn_CommitTransComplete(ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection) 
    Debug.Print "Transaction committed." 
End Sub 

Private Sub conn_ConnectComplete(ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection) 

End Sub 

Private Sub conn_Disconnect(adStatus As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection) 

End Sub 

Private Sub conn_ExecuteComplete(ByVal RecordsAffected As Long, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pCommand As ADODB.Command, ByVal pRecordset As ADODB.Recordset, ByVal pConnection As ADODB.Connection) 
    Debug.Print "SQL execution complete." 
End Sub 

Private Sub conn_RollbackTransComplete(ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection) 
    Debug.Print "Transaction rolled back." 
End Sub 

使用你的新类：

Dim sourceDb As New AdoDbHelper 
Dim sourceRs as New ADODB.Recordset 

sourceDb.Connect (<insert connection string here>) 

Set sourceRs = sourceDb.OpenRecordSet(<insert SQL string here>) 

With sourceRs 
    'Do stuff! 

    .Close 
End With 

sourceDb.Disconnect 

Set sourceRs = Nothing 
Set sourceDb = Nothing 

这不正是我曾经写过的最好的代码，但它应该给你一个体面的开始。如果您在理解课程的工作方式时遇到困难，我鼓励您对VBA中的OOP和课程进行一些研究。你会注意到你仍然有一些必要的biolerplate代码，但大部分的正常工作已经在班级方法中为你处理。如果您想将数据处理逻辑放入它自己的函数中，可以将它传递给您使用该类创建的ADODB.Recordset对象（这将替代WITH块）。

我不会建议用类似逻辑来污染类，因为您希望该类为任何可能的ADODB连接处理所有通用连接/断开连接/异常处理。这样你可以在其他项目= D中重用它。