Avatar of W.E.B
W.E.B
 asked on

VBA - SQL SELECT

Hello,
I'm using a  cnn = New ADODB.Connection, to get some data from SQL SERVER 2008R2
When I run my simple select statment below, I'm getting thousands of records back. (in the sample attached, I should have received back the first  3 records back only).
if I run the same script inside SQL, I get the proper results. (one record per Tracking).

Any help is appreciated.

    Dim cnt As Integer
    Dim i As Integer
 For i = 2 To Range("A2").End(xlDown).Row + 1
 cnt = cnt + 1
 
' Start
cnn.Open "Provider=SQLOLEDB.1;User ID= xxxxxxxxxxxxxxxxxxxx

sql = "Select OrderNo, " & _
"NoteNumber from FinalizedOrderNotes " & _
"where NoteText Like ('%" & sh.Cells(i, 1) & "%') "

    rs.Open sql, cnn, adOpenDynamic, adLockOptimistic
    sh.Cells(i, 2).CopyFromRecordset rs
    rs.Close
    cnn.Close
   
     Next i

Thank you,
SAMPLE.xls
Microsoft ExcelVisual Basic Classic

Avatar of undefined
Last Comment
W.E.B

8/22/2022 - Mon
David Christal CISSP

It looks like that if your target cell is empty your filter evaluates to '%%' with is no filter at all, explaining the too many records problem.  Also, it looks like you're pulling from column A instead of column B .
W.E.B

ASKER
Hello,
This is the way it's supposed to be,
Column "A" is the noteText that I'm looking up in SQL
Results will be in column "B".

The main question I guess  is why is it not stopping and ending the macro after the last row used in Column "A"

Thanks,
ASKER CERTIFIED SOLUTION
David Christal CISSP

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
W.E.B

ASKER
Awesome,
Thank you very much.
It worked.,
Your help has saved me hundreds of hours of internet surfing.
fblack61