Geek asks and answers

How to return ADODB.Connection from function in VBA?

I recently started working with another programmer on a large VBA / SQL Server application. Each function has the same ADODB connection, and we are trying to replace them with one function that can be called every 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 this connection, but instead we get an error message:

User-Defined Function not found

on the line

ConnectionString = CN

What am I doing wrong?

+3
source share
1 answer

You need a Setreturn value:

Set ConnectionString = CN

, 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
+3

More articles:


All Articles