DoCmd.SetWarnings False
DoCmd.OpenQuery ("qry_tbl_tsktsrhst_DATES_Delete")
'append LastLoads date + 1
DoCmd.OpenQuery ("qry_Next_Table_Date_APPEND")
Dim rsTableName As Recordset
Dim strTableName As String
Dim fldsDate As DAO.Fields
Dim fldDate As DAO.Field
Dim fldsDateFormat As DAO.Fields
Dim fldDateFormat As DAO.Field
'queries the LastLoads date + 1
Set rsTableName = CurrentDb.OpenRecordset("qry_Create_ODBC_Tbl_Name")
Set fldsDate = rsTableName.Fields
Set fldDate = fldsDate("table_Date")
Set fldsDateFormat = rsTableName.Fields
Set fldDateFormat = fldsDateFormat("NextLoadDateFormat")
'record**** will always be one, namely, LastLoad + 1
If rsTableName.RecordCount > 0 Then
With rsTableName
.MoveFirst
Do While Not .EOF
Set strFullTableName = fldDate
Set strFullTableNameFormat = fldDateFormat
On Error GoTo Errchk
DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;DSN=ServerName;DBQ=USER;PWD=MYPSWD;SERVER=CCCC", acTable, "tsktsrhst" & strFullTableName, "tsktsrhstDAILY", False, False
'consolidate and append to main table
strSql = "INSERT INTO tbl_Phone_Stats_by_Day ( tsr, listid, call_date, SumOftot_calls, SumOftime_connect, SumOftime_paused, SumOftime_waiting, SumOftime_deassigned, SumOflogon_time, logoff_time, SumOftime_acw ) " & vbCrLf & _
"SELECT tsktsrhstDAILY.tsr, tsktsrhstDAILY.listid, tsktsrhstDAILY.call_date, Sum(tsktsrhstDAILY.tot_calls) AS SumOftot_calls, Sum(tsktsrhstDAILY.time_connect) AS SumOftime_connect, Sum(tsktsrhstDAILY.time_paused) AS SumOftime_paused, Sum(tsktsrhstDAILY.time_waiting) AS SumOftime_waiting, Sum(tsktsrhstDAILY.time_deassigned) AS SumOftime_deassigned, Sum(tsktsrhstDAILY.logon_time) AS SumOflogon_time, Sum(tsktsrhstDAILY.logoff_time) AS SumOflogoff_time, Sum(tsktsrhstDAILY.time_acw) AS SumOftime_acw " & vbCrLf & _
"FROM tsktsrhstDAILY " & vbCrLf & _
"GROUP BY tsktsrhstDAILY.tsr, tsktsrhstDAILY.listid, tsktsrhstDAILY.call_date;"
DoCmd.RunSQL strSql
DoCmd.DeleteObject acTable, "tsktsrhstDAILY"
Errchk:
'do nothing, just loop to next table
rsTableName.MoveNext
Loop
End With
End If
DoCmd.SetWarnings True
End Function