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.
When asked, what has been your best career decision?
Deciding to stick with EE.
Being involved with EE helped me to grow personally and professionally.
Connect with Certified Experts to gain insight and support on specific technology challenges including:
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE
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