Using a VBA function to query SQL

Question

I have written the below vba function to query a sql database called Nelus, and a table called ShareName. The table has 2 columns, ShortCode and Name. The function takes the company code as parameter e.g. "c01", and should return the company name e.g. "company01".

When I run the vba code as a Sub, it works (commented out in below code).
When I try to run it as a Function as below, it returns a value of '0', instead of e.g. "company01".

Any suggestions how to get it to run as a function? Thanks! Nelus

'Sub TestSQLQuery()
Function ShareInfo(CompCode As String)
    Dim SQL As String
    Dim RS As ADODB.Recordset
    Dim Field As ADODB.Field
    'Dim CompCode As String
    Dim Server, Database As String
    Dim Connect As Boolean

    'CompCode = "c01"

    SQL = "select p.[Name]" & _
    "from dbo.['ShareName'] p " & _
    "where p.ShortCode = '" & CompCode & "' "

    Server = "npc\SQLEXPRESS"
    Database = "Nelus"

    Set CN = New ADODB.Connection
    On Error Resume Next
    With CN
        .ConnectionString = "Provider=SQLOLEDB.1;" & _
                            "Integrated Security=SSPI;" & _
                            "Server=" & Server & ";" & _
                            "Database=" & Database & ";"
        .Open
    End With
    If CN.State = 0 Then
        Connect = False
    Else
        Connect = True
    End If

    If Connect = False Then
        MsgBox "Could Not Connect!"
    Else
       Set RS = New ADODB.Recordset
       RS.Open SQL, CN, adOpenStatic, adLockReadOnly, adCmdText
       If RS.State Then
           Cells(ActiveCell.Row, ActiveCell.Column).CopyFromRecordset RS
           Set RS = Nothing
       End If
       CN.Close
    End If
End Function
'End Sub

gembird · Accepted Answer · 2014-12-28 16:57:59Z

Use Do While Not RS.EOF to go through the recodset and get the results. In your case it probably should be one or no record? Then assign the result from recordset to the function name ShareInfo = RS.fields(nameColumnIndex).value. So the function returns it to the calling procedure where it can be used further e.g. like Cells(ActiveCell.row, ActiveCell.column).value = companyName. HTH

Note: is this working for you? from dbo.['ShareName']. The apostropes seems to be not correct.

Sub test()
    Dim companyCode As String
    Dim companyName As String

    companyCode = "c01"
    companyName = ShareInfo(companyCode)

    If Not companyName = "" Then
        Cells(ActiveCell.row, ActiveCell.column).value = companyName
    Else
        MsgBox "Company name not found for code '" & companyCode & "'.", vbExclamation
    End If
End Sub

Function ShareInfo(CompCode As String) As String
    Dim SQL As String
    Dim RS As ADODB.Recordset
    Dim CN As ADODB.Connection
    Dim Field As ADODB.Field
    Dim Server, Database As String
    Dim Connect As Boolean

    ShareInfo = ""

    SQL = "select p.[Name]" & _
    "from dbo.[ShareName] p " & _
    "where p.ShortCode = '" & CompCode & "' "

    Server = "npc\SQLEXPRESS"
    Database = "Nelus"

    Set CN = New ADODB.Connection
    On Error Resume Next
    With CN
        .ConnectionString = "Provider=SQLOLEDB.1;" & _
                            "Integrated Security=SSPI;" & _
                            "Server=" & Server & ";" & _
                            "Database=" & Database & ";"
        .Open
    End With
    If CN.State = 0 Then
        Connect = False
    Else
        Connect = True
    End If

    If Connect = False Then
        MsgBox "Could Not Connect!"
    Else
        Set RS = New ADODB.Recordset
        RS.Open SQL, CN, adOpenStatic, adLockReadOnly, adCmdText

        Const nameColumnIndex As Integer = 0
        Dim i As Integer

        If RS.State Then
            Do While Not RS.EOF
                i = i + 1
                ShareInfo = RS.fields(nameColumnIndex).value
                RS.MoveNext
            Loop
        End If

        RS.Close
        CN.Close

        If i > 1 Then _
            MsgBox "More then one company name found for code '" & CompCode & "'.", vbExclamation
    End If
End Function

1) yes, only one or no record. 2) 'ShareName' is the name of the table in my case. So its needed in my case. 3) Above makes sense and works! Thanks dee!

Collectives™ on Stack Overflow

Using a VBA function to query SQL

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related