How do I return a ADODB.Connection from a function in VBA?

Question

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:

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

What am I doing wrong?

Answer 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).

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