VB connect to SQL DB not returning all values

I have a project where it connect to sql db and returns the info to db worksheet. I then do a lookup and return the value on another worksheet. The problem is it all works well. Maybe 1 or 2. Then i run it and get n/a on my lookup and when i search for the vlaue on the DB worksheet its not there. I don't understand why it returns all the values one time and not the next. Before i do a connect to the db i do a cell.clearcontents ont he db worksheet. I have copied in my db code below to see if you can see anythin i may have wrong in it. Cannot figure it out. Thanks

Sub RetrieveDBData()
Dim startDate As Date
Dim endDate As Date
Dim nextMonth As Integer
Dim nextYear As Integer
Dim r As Long
 
month = UserForm1.txtMonth.Value
year = UserForm1.txtYear.Value
 
If month <> "12" Then
    nextMonth = UserForm1.txtMonth.Value + 1
    nextYear = UserForm1.txtYear.Value + 1
Else
    nextMonth = "01"
    year = UserForm1.txtYear.Value + 1
End If
 
 
startDate = CDate(year & "/" & month & "/" & "01")
endDate = CDate(year & "/" & nextMonth & "/" & "01")
 
'Create a connection object
Dim cnPow As ADODB.Connection
Set cnPow = New ADODB.Connection
 
 
Dim cnPowDR As ADODB.Connection
Set cnPowDR = New ADODB.Connection
 
'Declare connection string
Dim strConn As String
Dim strConnDR As String
 
'Use the SQL Server OLE DB Provider
strConn = "PROVIDER=SQLOLEDB.1;"
strConnDR = "PROVIDER=SQLOLEDB.1;"
 
strConn = strConn & "DATA SOURCE=FARFAX1;INITIAL CATALOG=Pow;"
strConnDR = strConnDR & "DATA SOURCE=FARFAX2;INITIAL CATALOG=Pow2;"
 
 
'Use an integrated login
strConn = strConn & " INTEGRATED SECURITY=sspi;"
strConnDR = strConnDR & " INTEGRATED SECURITY=sspi;"
 
'Open the connection
cnPow.Open strConn
cnPowDR.Open strConnDR
        
        
'Create a recordset object
Dim rsFacsys As ADODB.Recordset
Set rsPow = New ADODB.Recordset
Dim rsPowDR As ADODB.Recordset
Set rsPowDR = New ADODB.Recordset
 
With rsPow
    ' Assign the Connection object
    .ActiveConnection = cnPow
    ' Extract the required records
    .Open "SELECT *  FROM Admin Where [Date] >= '" & Format(startDate, "yyyy-MM-dd") & "' And [Date] <= '" & Format(endDate, "yyyy-MM-dd") & "'"
    
    
    ws.Range("A2").CopyFromRecordset rsPow
    
    ' Close recordset
    .Close
End With
 
'Close connection
cnPow.Close
Set rsPow = Nothing
Set cnPow = Nothing
 
r = ws3.Range("A65536").End(xlUp).Row
r = r + 1
 
With rsPowDR
    ' Assign the Connection object
    .ActiveConnection = cnPowDR
    ' Extract the required records
    .Open "Select *  FROM Admin Where [Date] >= '" & Format(startDate, "yyyy-MM-dd") & "' And [Date] <= '" & Format(endDate, "yyyy-MM-dd") & "'"
 
   ws.Cells(r, 1).CopyFromRecordset rsPowDR
    
    ' Close recordset
    .Close
End With
 
 
cnPowDR.Close
Set rsPowDR = Nothing
Set cnPowDR = Nothing
 
End Sub

Select allOpen in new window