Compare Excel value to SQL table If statements.

Question

Is there a way I can use VBA to search a SQL table and return a Yes/No result if a cell in excel contains the same data found in a SQL table??

I have customer records in an Excel sheet where I need to compare the record id (A1) I need to compare cell by cell to the 'Client' table if there is a match, if so... I need some sort of output from sql if the value exists or not.

Example: If Cell.A1 is = SQLTableA then 'yes' else 'no'

I feel like I am close, but cant get the right output from sql.

enter code here
Option Explicit
Dim cell As Range
Dim CustRow As Range

Const SQLConStr As String = "Driver={SQL Server} ;Server=<svrname>;Database=CustData; UID=user; PWD=<pass>"

Sub connectTODB()

Dim CustDataConn As ADODB.Connection
Dim CustDataCMD As ADODB.Command
Dim rs As ADODB.Recordset


Set CustDataConn = New ADODB.Connection
Set CustDataCMD = New ADODB.Command
Set rs = New ADODB.Recordset

CustDataConn.ConnectionString = SQLConStr
CustDataConn.Open
CustDataCMD.ActiveConnection = CustDataConn


Dim CustValue As String
CustValue = "ACP"


Dim strSQL As String
strSQL = "SELECT * FROM [CustData].[dbo].[CustomerData] WHERE (CustomerData.Client='" & CustValue & "')"



With rs
.ActiveConnection = CustDataConn
'open strsql

'.Open "IF EXISTS(SELECT * FROM [CustData].[dbo].[CustomerData] WHERE CustomerData.Client = 'ACA') Print 'Yes' Else Print 'No'" '(notworking)
'.Open "IF EXISTS(SELECT Client FROM CustData.dbo.CustomerData WHERE  CustomerData.Client = 'hdh') Print 'Yes' Else Print 'No'" '(notworking)
.Open "IF EXISTS(SELECT Client FROM CustData.dbo.CustomerData WHERE  CustomerData.Client = 'hdh')"

Workbooks("<file>.xlsm").Worksheets("CustOutput").Range("A2").CopyFromRecordset rs

.Close
End With

CustDataConn.Close
Set rs = Nothing
Set CustDataConn = Nothing


End Sub

yes you can do that. I would shoot for a named cell in Excel, and a stored proc call — Drew
– Drew, Commented Aug 13, 2016 at 1:11
I only wrote one of these here. Due to the wild review it got, I am loathe to do another. — Drew
– Drew, Commented Aug 13, 2016 at 1:35
What are you trying to do with IF EXISTS(SELECT? If you're just wanting one value copied into one cell - you don't need copyfromrecordset. Just get your connection string right, and the query working with the specific field you want back. Then assign rs!YourFieldName to the cell — dbmitch
– dbmitch, Commented Aug 13, 2016 at 2:38

user6432984 · Accepted Answer · 2016-08-15 05:33:27Z

0

You can use the ADODB.Recordset.Filter property to return just the matching records. If there are no matching records then ADODB.Recordset.BOF will return true.

With rs
    .ActiveConnection = CustDataConn
    .Open strSQL

    .Filter = "CustomerData.Client = 'ACA'"
    Debug.Print IIf(.BOF, "No", "Yes")

    .Filter = "CustomerData.Client = 'hdh'"
    Debug.Print IIf(.BOF, "No", "Yes")

    'Clear Filter
    .Filter = ""

    Workbooks("<file>.xlsm").Worksheets("CustOutput").Range("A2").CopyFromRecordset rs

    .Close
End With

answered Aug 15, 2016 at 5:33

user6432984

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

2 Comments

SecuredIS Over a year ago

Thanks Thomas Inzina .

SecuredIS Over a year ago

I was unable to use the provided answer. different runtime errors and the output results were wrong. (I am new to asking questions, I am having a hard time replying with code...)

SecuredIS · Accepted Answer · 2016-08-16 22:20:04Z

0

can someone check my understanding here...

This will open the connection and send the string, then filter my value, after the filter the .BOF will return as false, I change the answer to a "yes/no" result and output that to a cell....

I have not used the BOF (EOF) values before and the description from Microsoft is even harder to understand... but..

If BOF = True (then the value does not exist > change to "No")

If BOF = False (then the value does exist > change to "Yes")

so..

enter code here

With rs

.ActiveConnection = CustDataConn
.Open strSQL
.Filter = "Client = 'ABC'"
Workbooks("file.xlsm").Worksheets("CustOutput").Range("A2") = IIf(.BOF, "No", "Yes")
.Close

End With

edited Aug 16, 2016 at 22:20

answered Aug 16, 2016 at 21:58

SecuredIS

114 bronze badges

Collectives™ on Stack Overflow

Compare Excel value to SQL table If statements.

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related