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

Question

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.

Christian Specht · Accepted Answer · 2012-04-18 22:57:01Z

5

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

edited Apr 18, 2012 at 22:57

answered Apr 18, 2012 at 22:50

Christian Specht

36.6k16 gold badges132 silver badges190 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SaintWacko Over a year ago

That worked! Such a simple solution. Should have tried that, but I'm still pretty new at this.

Cosmin · Accepted Answer · 2021-01-19 19:17:21Z

From sub caller:
    Dim DBConnection As New ADODB.Connection
    Call getDBConnection(ServerInfo, DBConnection)
The sub called:

Public Sub getDBConnection(ByRef paramServerInfo As ConnState, ByRef pCN As ADODB.Connection)
    Dim flagConnect As Boolean
    Dim errConnect As String
    Dim optionConn As String
    Dim strConn As String
    
    optionConn = "16427"
    strConn = "Provider=MSDASQL.1;Persist Security Info=False;Extended Properties=""" & _
            "DRIVER={" & paramServerInfo.ODBC & "};" & _
            "DATABASE=" & paramServerInfo.Database & ";" & _
            "SERVER=" & paramServerInfo.Server & ";" & _
            "USER=" & paramServerInfo.UserName & ";" & _
            "PASSWORD=" & paramServerInfo.Password & ";" & _
            "Port=" & paramServerInfo.Port & ";" & _
            "OPTION=" & optionConn & ";"
    
    On Error Resume Next
    paramServerInfo.Conneted = False
    Do
        pCN.CursorLocation = adUseClient
        pCN.Open strConn
        paramServerInfo.Conneted = Not pCN Is Nothing
        paramServerInfo.LastMsg = Err.Description
        If Not paramServerInfo.Conneted Then
            If MsgBox("Existe un problema al intentar conectar con la Base de Datos" & vbCrLf & paramServerInfo.LastMsg & vbCrLf & "Por favor determine que hacer:", vbCritical + vbAbortRetryIgnore) = vbAbort Then
                Exit Sub
            End If
        End If
    Loop Until paramServerInfo.Conneted
End Sub

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related