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

adodb, database-connection, ms-access, sql-server, vba

Solution

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

Problem

I have a large Access VBA/SQL Server application. In each function there is the same ADODB connection. How do I replace those with a single function that can be called each time, to save space. ``` 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 there is an error message: User-Defined Function not found on the line `ConnectionString = CN`.

Original source