SQL Server & DBVLookup Function

[stumped73]

Hi all, for those of you who are familiar with working with SQL Server, I am wondering if it's possible to get the VBA code below to pull data from SQL Server to Excel. I have it working perfectly with Access right now but I need to migrate to SQL Server and I'm quite new to SQL Server. If it's possible can you please advise or if not maybe offer an alternate solution.

Dim adoCN As ADODB.ConnectionDim strSQL As String


Const DatabasePath As String = "C:\DatabaseName.accdb"


'Function argument descriptions
'LookupFieldName - the field you wish to search =
'LookupValue - the value in LookupFieldName you're searching for =
'ReturnField - the matching field containing the value you wish to return =


Public Function DBVLookUP(TableName As String, _
LookUpFieldName As String, _
LookupValue As String, _
ReturnField As String) As Variant


Dim adoRS As ADODB.Recordset


If adoCN Is Nothing Then SetUpConnection


Set adoRS = New ADODB.Recordset
strSQL = "SELECT " & LookUpFieldName & ", " & ReturnField & _
             " FROM " & TableName & _
             " WHERE " & LookUpFieldName & "='" & LookupValue & "';"
'If lookup value is a number then remove the two '
        
        adoRS.Open strSQL, adoCN, adOpenForwardOnly, adLockReadOnly
        If adoRS.BOF And adoRS.EOF Then
        DBVLookUP = NaN()
        
            Else
        DBVLookUP = adoRS.Fields(ReturnField).Value
    End If
        adoRS.Close
End Function


Sub SetUpConnection()
On Error GoTo ErrHandler
Set adoCN = New Connection
adoCN.Provider = "Microsoft.ACE.OLEDB.12.0"
adoCN.ConnectionString = "Data Source=" & "C:\DatabaseName.accdb" & ";Jet OLEDB:Database Password=Password;" 
adoCN.Open
Exit Sub
ErrHandler:
MsgBox Err.Description, vbExclamation, "An error occurred"


End Sub


Function NaN() As Variant
    NaN = CVErr(xlErrNA)
End Function

Thanks in advance
Dave