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

Question

提问by rpg

I'm beginner in Excel VBA and your help is much appreciated.

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

Please advice how can I create a common function to Open and database connection, and another function to close it, to avoid duplication of coding?

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

Here is my code. I'm stuck on how to move on...

这是我的代码。我被困在如何继续前进...

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

My wish is to write common functions so that I don't need to keep writing code to connect and close connection.

我的愿望是写一些常用的函数，这样就不用一直写代码去连接和关闭连接了。

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

Edit based on Kittoe's suggestions and works properly now. Thanks!

根据 Kittoe 的建议进行编辑，现在可以正常工作了。谢谢！

Class: a. Created a class called AdoDbHelper, Private Instancing b. In AdoDbHelper, change "Option Compare Database" to "Option Compare Text"
Module: Create a function like this.

班级：A. 创建了一个名为 AdoDbHelper 的类，私有实例 b。在 AdoDbHelper 中，将“选项比较数据库”更改为“选项比较文本”
模块：创建一个这样的函数。

Code below:

代码如下：

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

回答by Kittoes0124

This type of stuff is best done in a class. Right click your VBA project in the "IDE" and go to Insert -> Class Module. Name your class something meaningful like clsAdoHelper (if Hungarian Notation is your thing), AdoDbHelper, or something. The following is an example of the code you'd put in this class:

这种类型的东西最好在课堂上完成。在“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

Using your new class:

使用你的新课程：

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

It's not exactly the best code I've ever written but it should give you a decent start. If you have trouble understanding how the class works I encourage you to do a little research on OOP and classes in VBA. You'll notice that you still have a little bit of necessary biolerplate code here but the bulk of the normal work has been taken care of for you in the class methods. If you wanted to put the data manipulation logic into a function of it's own you could pass it the ADODB.Recordset object that you create using the class (this would replace the WITHblocks).

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

I wouldn't suggest polluting the class with such logic as you want the class to handle all of your generic connect/disconnect/exception handling for any possible ADODB connections. That way you can reuse it in other projects =D.

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

Answer 2

回答by K_B

Use the Microsoft ActiveX Data Objects Library x.x:

使用 Microsoft ActiveX 数据对象库 xx：

In your VBA window go to Tools> References > Microsoft ActiveX Data Objects Library x.x

在您的 VBA 窗口中，转到工具 > 参考 > Microsoft ActiveX 数据对象库 xx

I usually use 2.7 for downward compatibility. Now you can create ADODB objects to open connections, perform queries (select/update/delete/...) and use the query results (called record sets) to the rest of your excel, into tables, in specific cells.

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

To be sure to use the same connection all the time, create a Public connection object and refer to that in all your subroutines. In each subroutine first check if it is already set up by (connbeing my ADODB.Connectionobject):

要确保始终使用相同的连接，请创建一个公共连接对象并在所有子例程中引用该对象。在每个子程序中，首先检查它是否已经由（conn作为我的ADODB.Connection对象）设置：

If conn = Nothing Then Call Setup_Connection

And the Subroutine Setup_Connection being something like:

子程序 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

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

提问by rpg

回答by Kittoes0124

回答by K_B

相关推荐

最近更新

标签

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

提问by rpg

回答by Kittoes0124

回答by K_B

相关推荐

VBA - 代码执行速度极慢

vba 对象“_Application”的方法“Names”失败

vba/excel - 根据用户输入到 sheet1 上的单元格，从 sheet2 中的值填充 sheet1 中的单元格

Excel 2007 vba if语句根据珍贵的单元格颜色填充单元格颜色

相关推荐

最近更新

标签