如何从VBA中的函数返回ADODB.Connection？

I have recently started working with another programmer on a large Access VBA/SQL Server application. In each function there is the same ADODB connection, and we are trying to replace those with a single function that can be called each time, to save space. Here is the function:

我最近开始与另一个程序员在一个大型Access VBA / SQL Server应用程序上工作。在每个函数中都有相同的ADODB连接，我们试图用每个可以调用的单个函数替换那些，以节省空间。这是功能：

Public Function ConnectionString() As ADODB.Connection
Dim CN As ADODB.Connection

Set CN = New ADODB.Connection

With CN
    .Provider = "Microsoft.Access.OLEDB.10.0"
    .Properties("Data Provider").Value = "SQLOLEDB"
    .Properties("Data Source").Value = DLookup("Source", "tbl_Connection")
    .Properties("Initial Catalog").Value = DLookup("Catalog", "tbl_Connection")
    .Properties("Integrated Security").Value = SSPI
    .Open
End With

ConnectionString = CN

End Function

It seems like this should return that connection, but instead we get an error message:

看起来这应该返回该连接，但我们收到一条错误消息：

User-Defined Function not found

找不到用户定义的函数

on the line

在线上

ConnectionString = CN

ConnectionString = CN

What am I doing wrong?

我究竟做错了什么？

1 个解决方案

#1

You need to Set the return value:

您需要设置返回值：

Set ConnectionString = CN

---

Plus, if it's always the same ADODB connection anyway, you can save it in a variable and "recycle" it from there, so the actual creation of the connection happens exactly once (when the ConnectionString function is called for the first time).

另外，如果它始终是相同的ADODB连接，您可以将其保存在变量中并从那里“循环”它，因此实际创建的连接只发生一次（当第一次调用ConnectionString函数时）。

Private CN As ADODB.Connection 'variable in the module - NOT in the function

Public Function ConnectionString() As ADODB.Connection

If CN Is Nothing Then

    Set CN = New ADODB.Connection

    With CN
        'do stuff
    End With

End If

Set ConnectionString = CN

End Function