Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

VB connect to SQL DB not returning all values

Avatar of j8547
j8547 asked on
Visual Basic ClassicMicrosoft SQL Server 2005VB Script
2 Comments1 Solution413 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Avatar of mdougan
mdouganFlag of United States of America image

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Commented:
This problem has been solved!
Unlock 1 Answer and 2 Comments.
See Answers