Help with a piece of code

paulinhobqe13 · July 7th, 2015, 09:33 AM

Hi guys, I have this code here:

Code:

Option Explicit

Const ConStrSQL As String = "Provider=SQLNCLI10;Server=MYSERVER;Database=MYDATABASE;Trusted_Connection=yes;"


Public Sub CopyDataFromDatabase()

Dim con As ADODB.Connection
Set con = New ADODB.Connection
    
Dim conData As ADODB.Recordset
Set conData = New ADODB.Recordset
    
Dim i As Integer
i = 2
    
con.ConnectionString = ConStrSQL
    
con.Open
    
With conData
    
.ActiveConnection = con       
        
Do While Sheet1.Cells(i, 3).Value <> ""
       
.Source = " SELECT distinct left(ut.Text,27) " & _
" FROM [MYDATABASE].[dbo].[UnitText] ut, dbo.unit u " & _
" WHERE ut.unitid in (select unitid from dbo.unit where serialno like '" & CStr(Right(Sheet1.Cells(i, 3), 8)) & "' ) " & _
" AND ut.text like 'EPP:%' " & _
" AND u.unitid = ut.unitid " & _
" ORDER BY 1 "
i = i + 1
Loop
        
.Open
        
Sheet1.Cells(i, 4).CopyFromRecordset conData        
        
End With
     
conData.Close
    
con.Close

End Sub

My problem is that it should appear in the Sheet1 all 22 values that it searches for but it is actually only showing the last result. I have a guess the problem is in the .copyRecordSet part from what I read on the internet but I am newbie in VBA (started learning last Wednesday) and couldn't figure it out yet (almost 2 days going nuts). All the help is welcome :)