CODEWITHSUNDEEP
sql-serverms-accessdatabase-connectionvbaadodb
SaintWacko
SaintWacko

Reputation: 924

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

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.

Upvotes: 5

Views: 11259

Answers (2)

Fabos
Fabos

Reputation: 1

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

Upvotes: 0

Christian Specht
Christian Specht

Reputation: 36421

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

Upvotes: 5

Related Questions