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.
Thanks in advance
Dave
Code:
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