Hi
I am trying to insert a piece of code so the odbc databases timeout is set to 120secs rather then the standard 60secs.
Could somebody tel me the code to insert and where to insert it?
Please find my procedure below with the querys also.
Thanks
matthew
Sub Test_Reporta()
Dim AppExcel As Object
Dim LOCReport As Recordset
Dim LOCReport2 As Recordset
Dim CurrentSheet As Variant
Dim SPos As Integer
Dim rpos As Integer
Dim cpos As Integer
Dim i As Integer
Dim j As Integer
Dim count As Integer
Dim datasheet As Variant
Dim Test As Variant
Dim RepType As Integer
Dim Desc As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim EndDate As String
Dim StartDate As Date
Dim LocC As String
Dim LocL As String
Dim Par1 As Date
Dim Par2 As Date
Dim TeamNo As String
Dim strSql As String
' Stops warnings from appearing
DoCmd.SetWarnings False
' ************************************************** ******************************************
Set AppExcel = CreateObject("excel.application")
AppExcel.Visible = True
' Opens Excel template
'Selects Specialty
Select Case [Forms]![Test]![lstSpecialty]
Case "DNU"
AppExcel.Workbooks.Open "C:\Documents and Settings\msundhu\Desktop\Copy of DNU Activity_Revised.xls", , True
Case Else
MsgBox "No Valid Specialty selected"
Exit Sub
End Select
'************************************************* **********************************************
'Defines variable to queries records
'Extract all Referrals
AppExcel.StatusBar = "Running Referrals"
Select Case [Forms]![Test]![lstSpecialty]
Case "DNU"
AppExcel.StatusBar = "Running Referrals"
'runs the querie and puts the data into seperate table
strSql = "PARAMETERS Startdate DateTime, EndDate DateTime; " & vbCrLf & _
"SELECT dbo_vwReferrals.Service, Format([RefrlDate],""yyyymm"") AS [Date], Count(dbo_vwReferrals.REFRL_REFNO) AS CountOfREFRL_REFNO INTO tblDNUReferals " & vbCrLf & _
"FROM dbo_vwReferrals " & vbCrLf & _
"WHERE (((dbo_vwReferrals.RefrlDate) Between [StartDate] And [EndDate]) AND ((dbo_vwReferrals.ServiceID) Like ""dnu"") AND ((dbo_vwReferrals.StatusID) Not In (""R"",""C""))) " & vbCrLf & _
"GROUP BY dbo_vwReferrals.Service, Format([RefrlDate],""yyyymm"");"
DoCmd.RunSQL strSql
'selects all the data from the newly formed table
Set LOCReport = CurrentDb.OpenRecordset("SELECT tblDNUReferals.Service, tblDNUReferals.Date, tblDNUReferals.CountOfREFRL_REFNO FROM tblDNUReferals")
'selects named excel worksheett
Set datasheet = AppExcel.ActiveWorkBook.Sheets("RawData")
RepType = 1
Call Report_Run(LOCReport, datasheet, RepType)
AppExcel.StatusBar = "Running Community First Appointments"
strSql = "PARAMETERS StartDate DateTime, EndDate DateTime; " & vbCrLf & _
"SELECT dbo_vwReferrals.Service, Format([SchduleDate],""yyyymm"") AS [Date], Count(dbo_vwSchedules.SCHDL_REFNO) AS CountOfSCHDL_REFNO, IIf([schduledate]=[icntDate],""First"",""F/Up"") AS [Appt Type] INTO tblCommNew " & vbCrLf & _
"FROM dbo_vwReferrals INNER JOIN dbo_vwSchedules ON dbo_vwReferrals.REFRL_REFNO = dbo_vwSchedules.REFRL_REFNO " & vbCrLf & _
"WHERE (((dbo_vwSchedules.SchduleDate) Between [StartDate] And [EndDate]) AND ((dbo_vwReferrals.ServiceID) Like ""dnu"") AND ((dbo_vwSchedules.Shared) Is Null) AND ((dbo_vwSchedules.SchdlTypeID) Like ""c*"") AND ((dbo_vwSchedules.StatusID) Like ""f"")) " & vbCrLf & _
"GROUP BY dbo_vwReferrals.Service, Format([SchduleDate],""yyyymm""), IIf([schduledate]=[icntDate],""First"",""F/Up"") " & vbCrLf & _
"HAVING (((IIf([schduledate]=[icntDate],""First"",""F/Up""))=""First""));"
DoCmd.RunSQL strSql
Set LOCReport = CurrentDb.OpenRecordset("SELECT tblCommNew.Service, tblCommNew.Date, tblCommNew.CountOfSCHDL_REFNO FROM tblCommNew")
'selects named excel worksheett
Set datasheet = AppExcel.ActiveWorkBook.Sheets("RawData")
RepType = 1
Call Report_Run2(LOCReport, datasheet, RepType)
AppExcel.StatusBar = "Running Community Follow-Up Appointments"
strSql = "PARAMETERS StartDate DateTime, EndDate DateTime; " & vbCrLf & _
"SELECT dbo_vwReferrals.Service, Format([SchduleDate],""yyyymm"") AS [Date], Count(dbo_vwSchedules.SCHDL_REFNO) AS CountOfSCHDL_REFNO, IIf([schduledate]=[icntDate],""First"",""F/Up"") AS [Appt Type] INTO tblCommFUP " & vbCrLf & _
"FROM dbo_vwReferrals INNER JOIN dbo_vwSchedules ON dbo_vwReferrals.REFRL_REFNO = dbo_vwSchedules.REFRL_REFNO " & vbCrLf & _
"WHERE (((dbo_vwSchedules.SchduleDate) Between [StartDate] And [EndDate]) AND ((dbo_vwReferrals.ServiceID) Like ""dnu"") AND ((dbo_vwSchedules.Shared) Is Null) AND ((dbo_vwSchedules.SchdlTypeID) Like ""c*"") AND ((dbo_vwSchedules.StatusID) Like ""f"")) " & vbCrLf & _
"GROUP BY dbo_vwReferrals.Service, Format([SchduleDate],""yyyymm""), IIf([schduledate]=[icntDate],""First"",""F/Up"") " & vbCrLf & _
"HAVING (((IIf([schduledate]=[icntDate],""First"",""F/Up""))=""F/Up""));"
DoCmd.RunSQL strSql
Set LOCReport = CurrentDb.OpenRecordset("SELECT tblCommFUP.Service, tblCommFUP.Date, tblCommFUP.CountOfSCHDL_REFNO FROM tblCommFUP")
'selects named excel worksheett
Set datasheet = AppExcel.ActiveWorkBook.Sheets("RawData")
RepType = 1
Call Report_Run3(LOCReport, datasheet, RepType)
AppExcel.StatusBar = "Running Community Shared First Appointments"
strSql = "PARAMETERS startdate DateTime, enddate DateTime; " & vbCrLf & _
"SELECT dbo_vwReferrals.Service, Format([SchduleDate],""yyyymm"") AS [Date], Count(dbo_vwSchedules.SCHDL_REFNO) AS CountOfSCHDL_REFNO INTO tblCommSharedFirst " & vbCrLf & _
"FROM dbo_vwReferrals INNER JOIN dbo_vwSchedules ON dbo_vwReferrals.REFRL_REFNO = dbo_vwSchedules.REFRL_REFNO " & vbCrLf & _
"WHERE (((IIf([schduledate]=[icntDate],""First"",""F/Up""))=""First"") AND ((dbo_vwSchedules.SchduleDate) Between [StartDate] And [EndDate]) AND ((dbo_vwReferrals.ServiceID) Like ""dnu"") AND ((dbo_vwSchedules.Shared) Like ""y"")) " & vbCrLf & _
"GROUP BY dbo_vwReferrals.Service, Format([SchduleDate],""yyyymm"");"
DoCmd.RunSQL strSql
Set LOCReport = CurrentDb.OpenRecordset("SELECT tblCommSharedFirst.Service, tblCommSharedFirst.Date, tblCommSharedFirst.CountOfSCHDL_REFNO FROM tblCommSharedFirst")
'selects named excel worksheett
Set datasheet = AppExcel.ActiveWorkBook.Sheets("RawData")
RepType = 1
Call Report_Run4(LOCReport, datasheet, RepType)
AppExcel.StatusBar = "Running Community Shared Follow-Up Appointments"
strSql = "PARAMETERS startdate DateTime, enddate DateTime; " & vbCrLf & _
"SELECT dbo_vwReferrals.Service, Format([SchduleDate],""yyyymm"") AS [Date], Count(dbo_vwSchedules.SCHDL_REFNO) AS CountOfSCHDL_REFNO INTO tblCommSharedFUP " & vbCrLf & _
"FROM dbo_vwReferrals INNER JOIN dbo_vwSchedules ON dbo_vwReferrals.REFRL_REFNO = dbo_vwSchedules.REFRL_REFNO " & vbCrLf & _
"WHERE (((IIf([schduledate]=[icntDate],""First"",""F/Up""))=""F/UP"") AND ((dbo_vwSchedules.SchduleDate) Between [StartDate] And [EndDate]) AND ((dbo_vwReferrals.ServiceID) Like ""dnu"") AND ((dbo_vwSchedules.Shared) Like ""y"")) " & vbCrLf & _
"GROUP BY dbo_vwReferrals.Service, Format([SchduleDate],""yyyymm"");"
DoCmd.RunSQL strSql
Set LOCReport = CurrentDb.OpenRecordset("SELECT tblCommSharedFUP.Service, tblCommSharedFUP.Date, tblCommSharedFUP.CountOfSCHDL_REFNO FROM tblCommSharedFUP")
'selects named excel worksheett
Set datasheet = AppExcel.ActiveWorkBook.Sheets("RawData")
RepType = 1
Call Report_Run5(LOCReport, datasheet, RepType)
AppExcel.StatusBar = "Running Outpatient First Appointments"
strSql = "SELECT dbo_vwReferrals.Service, Format([SchduleDate],""yyyymm"") AS [Date], Count(dbo_vwSchedules.SCHDL_REFNO) AS CountOfSCHDL_REFNO INTO tblOutpatinentNew " & vbCrLf & _
"FROM dbo_vwReferrals INNER JOIN dbo_vwSchedules ON dbo_vwReferrals.REFRL_REFNO = dbo_vwSchedules.REFRL_REFNO " & vbCrLf & _
"WHERE (((IIf([schduledate]=[icntDate],""First"",""F/Up""))=""first"") AND ((dbo_vwSchedules.SchduleDate) Between [STARTDATE] And [ENDDATE]) AND ((dbo_vwReferrals.ServiceID) Like ""dnu"") AND ((dbo_vwSchedules.SchdlTypeID) Like ""o*"") AND ((dbo_vwSchedules.Shared) Is Null) AND ((dbo_vwSchedules.StatusID) Like ""f"")) " & vbCrLf & _
"GROUP BY dbo_vwReferrals.Service, Format([SchduleDate],""yyyymm"");"
DoCmd.RunSQL strSql
Set LOCReport = CurrentDb.OpenRecordset("SELECT tblCommNew.Service, tblCommNew.Date, tblCommNew.CountOfSCHDL_REFNO FROM tblCommNew")
'selects named excel worksheett
Set datasheet = AppExcel.ActiveWorkBook.Sheets("RawData")
RepType = 1
Call Report_Run6(LOCReport, datasheet, RepType)
AppExcel.StatusBar = "Running Outpatient Follow-Up Appointments"
strSql = "SELECT dbo_vwReferrals.Service, Format([SchduleDate],""yyyymm"") AS [Date], Count(dbo_vwSchedules.SCHDL_REFNO) AS CountOfSCHDL_REFNO INTO tblOutpatinentNew " & vbCrLf & _
"FROM dbo_vwReferrals INNER JOIN dbo_vwSchedules ON dbo_vwReferrals.REFRL_REFNO = dbo_vwSchedules.REFRL_REFNO " & vbCrLf & _
"WHERE (((IIf([schduledate]=[icntDate],""First"",""F/Up""))=""F/UP"") AND ((dbo_vwSchedules.SchduleDate) Between [STARTDATE] And [ENDDATE]) AND ((dbo_vwReferrals.ServiceID) Like ""dnu"") AND ((dbo_vwSchedules.SchdlTypeID) Like ""o*"") AND ((dbo_vwSchedules.Shared) Is Null) AND ((dbo_vwSchedules.StatusID) Like ""f"")) " & vbCrLf & _
"GROUP BY dbo_vwReferrals.Service, Format([SchduleDate],""yyyymm"");"
DoCmd.RunSQL strSql
Set LOCReport = CurrentDb.OpenRecordset("SELECT tblCommFUP.Service, tblCommFUP.Date, tblCommFUP.CountOfSCHDL_REFNO FROM tblCommFUP")
'selects named excel worksheett
Set datasheet = AppExcel.ActiveWorkBook.Sheets("RawData")
RepType = 1
Call Report_Run7(LOCReport, datasheet, RepType)
AppExcel.StatusBar = "Running Community DNA"
strSql = "PARAMETERS Startdate DateTime, EndDate DateTime; " & vbCrLf & _
"SELECT dbo_vwReferrals.Service, Format([RefrlDate],""yyyymm"") AS Datea, Count(dbo_vwReferrals.REFRL_REFNO) AS CountOfREFRL_REFNO INTO tblDNUReferals " & vbCrLf & _
"FROM dbo_vwReferrals " & vbCrLf & _
"WHERE (((dbo_vwReferrals.RefrlDate) Between [StartDate] And [EndDate]) AND ((dbo_vwReferrals.ServiceID) Like ""dnu"") AND ((dbo_vwReferrals.StatusID) Not In (""R"",""C""))) " & vbCrLf & _
"GROUP BY dbo_vwReferrals.Service, Format([RefrlDate],""yyyymm"");"
DoCmd.RunSQL strSql
Set LOCReport = CurrentDb.OpenRecordset("SELECT tblCommContactsDNA.Service, tblCommContactsDNA.Date, tblCommContactsDNA.CountOfSCHDL_REFNO FROM tblCommContactsDNA")
'selects named excel worksheett
Set datasheet = AppExcel.ActiveWorkBook.Sheets("RawData")
RepType = 1
Call Report_Run8(LOCReport, datasheet, RepType)
AppExcel.StatusBar = "Running Average F2f Contact Time"
strSql = "SELECT dbo_vwSchedules.Service, Format([SchduleDate],""yyyymm"") AS [Date], Avg(Round([Duration])) AS AverageDuration INTO tblAvgContactTimeF2F " & vbCrLf & _
"FROM dbo_vwSchedules " & vbCrLf & _
"WHERE (((dbo_vwSchedules.SchduleDate) Between [StartDate] And [EndDate]) AND ((dbo_vwSchedules.ServiceID) Like ""dnu"") AND ((dbo_vwSchedules.StatusID) Like ""f*"") AND ((dbo_vwSchedules.SchdlTypeID) Like ""c*"") AND ((dbo_vwSchedules.Shared) Is Null)) " & vbCrLf & _
"GROUP BY dbo_vwSchedules.Service, Format([SchduleDate],""yyyymm"");"
DoCmd.RunSQL strSql
Set LOCReport = CurrentDb.OpenRecordset("SELECT tblAvgContactTimeF2F.Service, tblAvgContactTimeF2F.Date, tblAvgContactTimeF2F.AverageDuration FROM tblAvgContactTimeF2F")
'selects named excel worksheett
Set datasheet = AppExcel.ActiveWorkBook.Sheets("RawData")
RepType = 1
Call Report_Run9(LOCReport, datasheet, RepType)
''
''
''
AppExcel.StatusBar = "Running Outpatient DNA"
strSql = "SELECT dbo_vwSchedules.Service, Format([SchduleDate],""yyyymm"") AS [Date], Count(dbo_vwSchedules.SCHDL_REFNO) AS CountOfSCHDL_REFNO INTO tblOutpatientsDNA " & vbCrLf & _
"FROM dbo_vwSchedules " & vbCrLf & _
"WHERE (((dbo_vwSchedules.SchduleDate) Between [StartDate] And [EndDate]) AND ((dbo_vwSchedules.ServiceID) Like ""dnu"") AND ((dbo_vwSchedules.SchdlTypeID) Like ""o*"") AND ((dbo_vwSchedules.StatusID) Like ""d"")) " & vbCrLf & _
"GROUP BY dbo_vwSchedules.Service, Format([SchduleDate],""yyyymm"");"
DoCmd.RunSQL strSql
Set LOCReport = CurrentDb.OpenRecordset("SELECT tblOutpatientsDNA.Service, tblOutpatientsDNA.Date, tblOutpatientsDNA.CountOfSCHDL_REFNO FROM tblOutpatientsDNA")
'selects named excel worksheett
Set datasheet = AppExcel.ActiveWorkBook.Sheets("RawData")
RepType = 1
Call Report_Run10(LOCReport, datasheet, RepType)
AppExcel.StatusBar = "Running Clinic Utilisation Part 1"
strSql = "SELECT 'Total Capacity' AS [Total Capacity], Format(dbo_tblServicePointTimeslots.START_DTTM,""y yyymm"") AS [Date], Count(dbo_tblServicePointTimeslots.TSTAT_REFNO) AS CountOfTSTAT_REFNO INTO tblCapacityTotal " & vbCrLf & _
"FROM (dbo_tblServicePointTimeslots LEFT JOIN dbo_tblServicePointSessions ON dbo_tblServicePointTimeslots.SPSSN_REFNO = dbo_tblServicePointSessions.SPSSN_REFNO) INNER JOIN dbo_tblReferenceValues ON dbo_tblServicePointTimeslots.TSTAT_REFNO = dbo_tblReferenceValues.RFVAL_REFNO " & vbCrLf & _
"WHERE (((dbo_tblServicePointTimeslots.START_DTTM) Between [StartDate] And [EndDate]) AND ((dbo_tblServicePointSessions.CODE) Like ""f2fdn*"") AND ((dbo_tblReferenceValues.DESCRIPTION) Not In (""No Longer Available"",""Reserved""))) " & vbCrLf & _
"GROUP BY 'Total Capacity', Format(dbo_tblServicePointTimeslots.START_DTTM,""y yyymm"");"
DoCmd.RunSQL strSql
Set LOCReport = CurrentDb.OpenRecordset("SELECT tblCapacityTotal.Date, tblCapacityTotal.CountOfTSTAT_REFNO FROM tblCapacityTotal")
'selects named excel worksheet
Set datasheet = AppExcel.ActiveWorkBook.Sheets("RawData")
RepType = 1
Call Report_Run11(LOCReport, datasheet, RepType)
AppExcel.StatusBar = "Running Clinic Utilisation Part 2"
strSql = "SELECT Format(dbo_tblServicePointTimeslots.START_DTTM,""y yyymm"") AS [Date], Count(dbo_tblServicePointTimeslots.TSTAT_REFNO) AS CountOfTSTAT_REFNO INTO tblCapacityBooked " & vbCrLf & _
"FROM (dbo_tblServicePointTimeslots INNER JOIN dbo_tblServicePointSessions ON dbo_tblServicePointTimeslots.SPSSN_REFNO = dbo_tblServicePointSessions.SPSSN_REFNO) INNER JOIN dbo_tblReferenceValues ON dbo_tblServicePointTimeslots.TSTAT_REFNO = dbo_tblReferenceValues.RFVAL_REFNO " & vbCrLf & _
"WHERE (((dbo_tblServicePointTimeslots.START_DTTM) Between [StartDate] And [EndDate]) AND ((dbo_tblServicePointSessions.CODE) Like ""f2fdn*"") AND ((dbo_tblReferenceValues.DESCRIPTION) Like ""booked"")) " & vbCrLf & _
"GROUP BY Format(dbo_tblServicePointTimeslots.START_DTTM,""y yyymm"");"
DoCmd.RunSQL strSql
Set LOCReport = CurrentDb.OpenRecordset("SELECT tblCapacityBooked.Date, tblCapacityBooked.CountOfTSTAT_REFNO FROM tblCapacityBooked")
'selects named excel worksheet
Set datasheet = AppExcel.ActiveWorkBook.Sheets("RawData")
RepType = 1
Call Report_Run12(LOCReport, datasheet, RepType)
AppExcel.StatusBar = "Indirect Contacts"
strSql = "SELECT dbo_vwSchedules.Service, Format([SchduleDate],""yyyymm"") AS [Date], Count(dbo_vwSchedules.SCHDL_REFNO) AS CountOfSCHDL_REFNO INTO tblIndirectContacts " & vbCrLf & _
"FROM dbo_vwSchedules " & vbCrLf & _
"WHERE (((dbo_vwSchedules.SchduleDate) Between [StartDate] And [EndDate]) AND ((dbo_vwSchedules.ServiceID) Like ""dnu"") AND ((dbo_vwSchedules.StatusID) Like ""i"") AND ((dbo_vwSchedules.SchdlTypeID) Like ""c*"") AND ((dbo_vwSchedules.Shared) Is Null)) " & vbCrLf & _
"GROUP BY dbo_vwSchedules.Service, Format([SchduleDate],""yyyymm"");"
DoCmd.RunSQL strSql
Set LOCReport = CurrentDb.OpenRecordset("SELECT tblIndirectContacts.Service, tblIndirectContacts.Date, tblIndirectContacts.CountOfSCHDL_REFNO FROM tblIndirectContacts")
'selects named excel worksheet
Set datasheet = AppExcel.ActiveWorkBook.Sheets("RawData")
RepType = 1
Call Report_Run14(LOCReport, datasheet, RepType)
AppExcel.StatusBar = "Group Contacts"
strSql = "SELECT dbo_vwSchedules.Service, Format([SchduleDate],""yyyymm"") AS [Date], Count(dbo_vwSchedules.SCHDL_REFNO) AS CountOfSCHDL_REFNO INTO tblGroupContacts " & vbCrLf & _
"FROM dbo_vwSchedules INNER JOIN dbo_tblSchedules ON dbo_vwSchedules.PARNT_REFNO = dbo_tblSchedules.SCHDL_REFNO " & vbCrLf & _
"WHERE (((dbo_vwSchedules.SchduleDate) Between [StartDate] And [EndDate]) AND ((dbo_vwSchedules.ServiceID) Like ""dnu"") AND ((dbo_tblSchedules.SATYP_REFNO) Like ""1452"")) " & vbCrLf & _
"GROUP BY dbo_vwSchedules.Service, Format([SchduleDate],""yyyymm"");"
DoCmd.RunSQL strSql
Set LOCReport = CurrentDb.OpenRecordset("SELECT tblGroupContacts.Service, tblGroupContacts.Date, tblGroupContacts.CountOfSCHDL_REFNO FROM tblGroupContacts")
'selects named excel worksheet
Set datasheet = AppExcel.ActiveWorkBook.Sheets("RawData")
RepType = 1
Call Report_Run13(LOCReport, datasheet, RepType)
End Select
DoCmd.SetWarnings True
AppExcel.StatusBar = "Run has finished"
MsgBox "Run has finished"
AppExcel.StatusBar = False
End Sub
I am trying to insert a piece of code so the odbc databases timeout is set to 120secs rather then the standard 60secs.
Could somebody tel me the code to insert and where to insert it?
Please find my procedure below with the querys also.
Thanks
matthew
Sub Test_Reporta()
Dim AppExcel As Object
Dim LOCReport As Recordset
Dim LOCReport2 As Recordset
Dim CurrentSheet As Variant
Dim SPos As Integer
Dim rpos As Integer
Dim cpos As Integer
Dim i As Integer
Dim j As Integer
Dim count As Integer
Dim datasheet As Variant
Dim Test As Variant
Dim RepType As Integer
Dim Desc As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim EndDate As String
Dim StartDate As Date
Dim LocC As String
Dim LocL As String
Dim Par1 As Date
Dim Par2 As Date
Dim TeamNo As String
Dim strSql As String
' Stops warnings from appearing
DoCmd.SetWarnings False
' ************************************************** ******************************************
Set AppExcel = CreateObject("excel.application")
AppExcel.Visible = True
' Opens Excel template
'Selects Specialty
Select Case [Forms]![Test]![lstSpecialty]
Case "DNU"
AppExcel.Workbooks.Open "C:\Documents and Settings\msundhu\Desktop\Copy of DNU Activity_Revised.xls", , True
Case Else
MsgBox "No Valid Specialty selected"
Exit Sub
End Select
'************************************************* **********************************************
'Defines variable to queries records
'Extract all Referrals
AppExcel.StatusBar = "Running Referrals"
Select Case [Forms]![Test]![lstSpecialty]
Case "DNU"
AppExcel.StatusBar = "Running Referrals"
'runs the querie and puts the data into seperate table
strSql = "PARAMETERS Startdate DateTime, EndDate DateTime; " & vbCrLf & _
"SELECT dbo_vwReferrals.Service, Format([RefrlDate],""yyyymm"") AS [Date], Count(dbo_vwReferrals.REFRL_REFNO) AS CountOfREFRL_REFNO INTO tblDNUReferals " & vbCrLf & _
"FROM dbo_vwReferrals " & vbCrLf & _
"WHERE (((dbo_vwReferrals.RefrlDate) Between [StartDate] And [EndDate]) AND ((dbo_vwReferrals.ServiceID) Like ""dnu"") AND ((dbo_vwReferrals.StatusID) Not In (""R"",""C""))) " & vbCrLf & _
"GROUP BY dbo_vwReferrals.Service, Format([RefrlDate],""yyyymm"");"
DoCmd.RunSQL strSql
'selects all the data from the newly formed table
Set LOCReport = CurrentDb.OpenRecordset("SELECT tblDNUReferals.Service, tblDNUReferals.Date, tblDNUReferals.CountOfREFRL_REFNO FROM tblDNUReferals")
'selects named excel worksheett
Set datasheet = AppExcel.ActiveWorkBook.Sheets("RawData")
RepType = 1
Call Report_Run(LOCReport, datasheet, RepType)
AppExcel.StatusBar = "Running Community First Appointments"
strSql = "PARAMETERS StartDate DateTime, EndDate DateTime; " & vbCrLf & _
"SELECT dbo_vwReferrals.Service, Format([SchduleDate],""yyyymm"") AS [Date], Count(dbo_vwSchedules.SCHDL_REFNO) AS CountOfSCHDL_REFNO, IIf([schduledate]=[icntDate],""First"",""F/Up"") AS [Appt Type] INTO tblCommNew " & vbCrLf & _
"FROM dbo_vwReferrals INNER JOIN dbo_vwSchedules ON dbo_vwReferrals.REFRL_REFNO = dbo_vwSchedules.REFRL_REFNO " & vbCrLf & _
"WHERE (((dbo_vwSchedules.SchduleDate) Between [StartDate] And [EndDate]) AND ((dbo_vwReferrals.ServiceID) Like ""dnu"") AND ((dbo_vwSchedules.Shared) Is Null) AND ((dbo_vwSchedules.SchdlTypeID) Like ""c*"") AND ((dbo_vwSchedules.StatusID) Like ""f"")) " & vbCrLf & _
"GROUP BY dbo_vwReferrals.Service, Format([SchduleDate],""yyyymm""), IIf([schduledate]=[icntDate],""First"",""F/Up"") " & vbCrLf & _
"HAVING (((IIf([schduledate]=[icntDate],""First"",""F/Up""))=""First""));"
DoCmd.RunSQL strSql
Set LOCReport = CurrentDb.OpenRecordset("SELECT tblCommNew.Service, tblCommNew.Date, tblCommNew.CountOfSCHDL_REFNO FROM tblCommNew")
'selects named excel worksheett
Set datasheet = AppExcel.ActiveWorkBook.Sheets("RawData")
RepType = 1
Call Report_Run2(LOCReport, datasheet, RepType)
AppExcel.StatusBar = "Running Community Follow-Up Appointments"
strSql = "PARAMETERS StartDate DateTime, EndDate DateTime; " & vbCrLf & _
"SELECT dbo_vwReferrals.Service, Format([SchduleDate],""yyyymm"") AS [Date], Count(dbo_vwSchedules.SCHDL_REFNO) AS CountOfSCHDL_REFNO, IIf([schduledate]=[icntDate],""First"",""F/Up"") AS [Appt Type] INTO tblCommFUP " & vbCrLf & _
"FROM dbo_vwReferrals INNER JOIN dbo_vwSchedules ON dbo_vwReferrals.REFRL_REFNO = dbo_vwSchedules.REFRL_REFNO " & vbCrLf & _
"WHERE (((dbo_vwSchedules.SchduleDate) Between [StartDate] And [EndDate]) AND ((dbo_vwReferrals.ServiceID) Like ""dnu"") AND ((dbo_vwSchedules.Shared) Is Null) AND ((dbo_vwSchedules.SchdlTypeID) Like ""c*"") AND ((dbo_vwSchedules.StatusID) Like ""f"")) " & vbCrLf & _
"GROUP BY dbo_vwReferrals.Service, Format([SchduleDate],""yyyymm""), IIf([schduledate]=[icntDate],""First"",""F/Up"") " & vbCrLf & _
"HAVING (((IIf([schduledate]=[icntDate],""First"",""F/Up""))=""F/Up""));"
DoCmd.RunSQL strSql
Set LOCReport = CurrentDb.OpenRecordset("SELECT tblCommFUP.Service, tblCommFUP.Date, tblCommFUP.CountOfSCHDL_REFNO FROM tblCommFUP")
'selects named excel worksheett
Set datasheet = AppExcel.ActiveWorkBook.Sheets("RawData")
RepType = 1
Call Report_Run3(LOCReport, datasheet, RepType)
AppExcel.StatusBar = "Running Community Shared First Appointments"
strSql = "PARAMETERS startdate DateTime, enddate DateTime; " & vbCrLf & _
"SELECT dbo_vwReferrals.Service, Format([SchduleDate],""yyyymm"") AS [Date], Count(dbo_vwSchedules.SCHDL_REFNO) AS CountOfSCHDL_REFNO INTO tblCommSharedFirst " & vbCrLf & _
"FROM dbo_vwReferrals INNER JOIN dbo_vwSchedules ON dbo_vwReferrals.REFRL_REFNO = dbo_vwSchedules.REFRL_REFNO " & vbCrLf & _
"WHERE (((IIf([schduledate]=[icntDate],""First"",""F/Up""))=""First"") AND ((dbo_vwSchedules.SchduleDate) Between [StartDate] And [EndDate]) AND ((dbo_vwReferrals.ServiceID) Like ""dnu"") AND ((dbo_vwSchedules.Shared) Like ""y"")) " & vbCrLf & _
"GROUP BY dbo_vwReferrals.Service, Format([SchduleDate],""yyyymm"");"
DoCmd.RunSQL strSql
Set LOCReport = CurrentDb.OpenRecordset("SELECT tblCommSharedFirst.Service, tblCommSharedFirst.Date, tblCommSharedFirst.CountOfSCHDL_REFNO FROM tblCommSharedFirst")
'selects named excel worksheett
Set datasheet = AppExcel.ActiveWorkBook.Sheets("RawData")
RepType = 1
Call Report_Run4(LOCReport, datasheet, RepType)
AppExcel.StatusBar = "Running Community Shared Follow-Up Appointments"
strSql = "PARAMETERS startdate DateTime, enddate DateTime; " & vbCrLf & _
"SELECT dbo_vwReferrals.Service, Format([SchduleDate],""yyyymm"") AS [Date], Count(dbo_vwSchedules.SCHDL_REFNO) AS CountOfSCHDL_REFNO INTO tblCommSharedFUP " & vbCrLf & _
"FROM dbo_vwReferrals INNER JOIN dbo_vwSchedules ON dbo_vwReferrals.REFRL_REFNO = dbo_vwSchedules.REFRL_REFNO " & vbCrLf & _
"WHERE (((IIf([schduledate]=[icntDate],""First"",""F/Up""))=""F/UP"") AND ((dbo_vwSchedules.SchduleDate) Between [StartDate] And [EndDate]) AND ((dbo_vwReferrals.ServiceID) Like ""dnu"") AND ((dbo_vwSchedules.Shared) Like ""y"")) " & vbCrLf & _
"GROUP BY dbo_vwReferrals.Service, Format([SchduleDate],""yyyymm"");"
DoCmd.RunSQL strSql
Set LOCReport = CurrentDb.OpenRecordset("SELECT tblCommSharedFUP.Service, tblCommSharedFUP.Date, tblCommSharedFUP.CountOfSCHDL_REFNO FROM tblCommSharedFUP")
'selects named excel worksheett
Set datasheet = AppExcel.ActiveWorkBook.Sheets("RawData")
RepType = 1
Call Report_Run5(LOCReport, datasheet, RepType)
AppExcel.StatusBar = "Running Outpatient First Appointments"
strSql = "SELECT dbo_vwReferrals.Service, Format([SchduleDate],""yyyymm"") AS [Date], Count(dbo_vwSchedules.SCHDL_REFNO) AS CountOfSCHDL_REFNO INTO tblOutpatinentNew " & vbCrLf & _
"FROM dbo_vwReferrals INNER JOIN dbo_vwSchedules ON dbo_vwReferrals.REFRL_REFNO = dbo_vwSchedules.REFRL_REFNO " & vbCrLf & _
"WHERE (((IIf([schduledate]=[icntDate],""First"",""F/Up""))=""first"") AND ((dbo_vwSchedules.SchduleDate) Between [STARTDATE] And [ENDDATE]) AND ((dbo_vwReferrals.ServiceID) Like ""dnu"") AND ((dbo_vwSchedules.SchdlTypeID) Like ""o*"") AND ((dbo_vwSchedules.Shared) Is Null) AND ((dbo_vwSchedules.StatusID) Like ""f"")) " & vbCrLf & _
"GROUP BY dbo_vwReferrals.Service, Format([SchduleDate],""yyyymm"");"
DoCmd.RunSQL strSql
Set LOCReport = CurrentDb.OpenRecordset("SELECT tblCommNew.Service, tblCommNew.Date, tblCommNew.CountOfSCHDL_REFNO FROM tblCommNew")
'selects named excel worksheett
Set datasheet = AppExcel.ActiveWorkBook.Sheets("RawData")
RepType = 1
Call Report_Run6(LOCReport, datasheet, RepType)
AppExcel.StatusBar = "Running Outpatient Follow-Up Appointments"
strSql = "SELECT dbo_vwReferrals.Service, Format([SchduleDate],""yyyymm"") AS [Date], Count(dbo_vwSchedules.SCHDL_REFNO) AS CountOfSCHDL_REFNO INTO tblOutpatinentNew " & vbCrLf & _
"FROM dbo_vwReferrals INNER JOIN dbo_vwSchedules ON dbo_vwReferrals.REFRL_REFNO = dbo_vwSchedules.REFRL_REFNO " & vbCrLf & _
"WHERE (((IIf([schduledate]=[icntDate],""First"",""F/Up""))=""F/UP"") AND ((dbo_vwSchedules.SchduleDate) Between [STARTDATE] And [ENDDATE]) AND ((dbo_vwReferrals.ServiceID) Like ""dnu"") AND ((dbo_vwSchedules.SchdlTypeID) Like ""o*"") AND ((dbo_vwSchedules.Shared) Is Null) AND ((dbo_vwSchedules.StatusID) Like ""f"")) " & vbCrLf & _
"GROUP BY dbo_vwReferrals.Service, Format([SchduleDate],""yyyymm"");"
DoCmd.RunSQL strSql
Set LOCReport = CurrentDb.OpenRecordset("SELECT tblCommFUP.Service, tblCommFUP.Date, tblCommFUP.CountOfSCHDL_REFNO FROM tblCommFUP")
'selects named excel worksheett
Set datasheet = AppExcel.ActiveWorkBook.Sheets("RawData")
RepType = 1
Call Report_Run7(LOCReport, datasheet, RepType)
AppExcel.StatusBar = "Running Community DNA"
strSql = "PARAMETERS Startdate DateTime, EndDate DateTime; " & vbCrLf & _
"SELECT dbo_vwReferrals.Service, Format([RefrlDate],""yyyymm"") AS Datea, Count(dbo_vwReferrals.REFRL_REFNO) AS CountOfREFRL_REFNO INTO tblDNUReferals " & vbCrLf & _
"FROM dbo_vwReferrals " & vbCrLf & _
"WHERE (((dbo_vwReferrals.RefrlDate) Between [StartDate] And [EndDate]) AND ((dbo_vwReferrals.ServiceID) Like ""dnu"") AND ((dbo_vwReferrals.StatusID) Not In (""R"",""C""))) " & vbCrLf & _
"GROUP BY dbo_vwReferrals.Service, Format([RefrlDate],""yyyymm"");"
DoCmd.RunSQL strSql
Set LOCReport = CurrentDb.OpenRecordset("SELECT tblCommContactsDNA.Service, tblCommContactsDNA.Date, tblCommContactsDNA.CountOfSCHDL_REFNO FROM tblCommContactsDNA")
'selects named excel worksheett
Set datasheet = AppExcel.ActiveWorkBook.Sheets("RawData")
RepType = 1
Call Report_Run8(LOCReport, datasheet, RepType)
AppExcel.StatusBar = "Running Average F2f Contact Time"
strSql = "SELECT dbo_vwSchedules.Service, Format([SchduleDate],""yyyymm"") AS [Date], Avg(Round([Duration])) AS AverageDuration INTO tblAvgContactTimeF2F " & vbCrLf & _
"FROM dbo_vwSchedules " & vbCrLf & _
"WHERE (((dbo_vwSchedules.SchduleDate) Between [StartDate] And [EndDate]) AND ((dbo_vwSchedules.ServiceID) Like ""dnu"") AND ((dbo_vwSchedules.StatusID) Like ""f*"") AND ((dbo_vwSchedules.SchdlTypeID) Like ""c*"") AND ((dbo_vwSchedules.Shared) Is Null)) " & vbCrLf & _
"GROUP BY dbo_vwSchedules.Service, Format([SchduleDate],""yyyymm"");"
DoCmd.RunSQL strSql
Set LOCReport = CurrentDb.OpenRecordset("SELECT tblAvgContactTimeF2F.Service, tblAvgContactTimeF2F.Date, tblAvgContactTimeF2F.AverageDuration FROM tblAvgContactTimeF2F")
'selects named excel worksheett
Set datasheet = AppExcel.ActiveWorkBook.Sheets("RawData")
RepType = 1
Call Report_Run9(LOCReport, datasheet, RepType)
''
''
''
AppExcel.StatusBar = "Running Outpatient DNA"
strSql = "SELECT dbo_vwSchedules.Service, Format([SchduleDate],""yyyymm"") AS [Date], Count(dbo_vwSchedules.SCHDL_REFNO) AS CountOfSCHDL_REFNO INTO tblOutpatientsDNA " & vbCrLf & _
"FROM dbo_vwSchedules " & vbCrLf & _
"WHERE (((dbo_vwSchedules.SchduleDate) Between [StartDate] And [EndDate]) AND ((dbo_vwSchedules.ServiceID) Like ""dnu"") AND ((dbo_vwSchedules.SchdlTypeID) Like ""o*"") AND ((dbo_vwSchedules.StatusID) Like ""d"")) " & vbCrLf & _
"GROUP BY dbo_vwSchedules.Service, Format([SchduleDate],""yyyymm"");"
DoCmd.RunSQL strSql
Set LOCReport = CurrentDb.OpenRecordset("SELECT tblOutpatientsDNA.Service, tblOutpatientsDNA.Date, tblOutpatientsDNA.CountOfSCHDL_REFNO FROM tblOutpatientsDNA")
'selects named excel worksheett
Set datasheet = AppExcel.ActiveWorkBook.Sheets("RawData")
RepType = 1
Call Report_Run10(LOCReport, datasheet, RepType)
AppExcel.StatusBar = "Running Clinic Utilisation Part 1"
strSql = "SELECT 'Total Capacity' AS [Total Capacity], Format(dbo_tblServicePointTimeslots.START_DTTM,""y yyymm"") AS [Date], Count(dbo_tblServicePointTimeslots.TSTAT_REFNO) AS CountOfTSTAT_REFNO INTO tblCapacityTotal " & vbCrLf & _
"FROM (dbo_tblServicePointTimeslots LEFT JOIN dbo_tblServicePointSessions ON dbo_tblServicePointTimeslots.SPSSN_REFNO = dbo_tblServicePointSessions.SPSSN_REFNO) INNER JOIN dbo_tblReferenceValues ON dbo_tblServicePointTimeslots.TSTAT_REFNO = dbo_tblReferenceValues.RFVAL_REFNO " & vbCrLf & _
"WHERE (((dbo_tblServicePointTimeslots.START_DTTM) Between [StartDate] And [EndDate]) AND ((dbo_tblServicePointSessions.CODE) Like ""f2fdn*"") AND ((dbo_tblReferenceValues.DESCRIPTION) Not In (""No Longer Available"",""Reserved""))) " & vbCrLf & _
"GROUP BY 'Total Capacity', Format(dbo_tblServicePointTimeslots.START_DTTM,""y yyymm"");"
DoCmd.RunSQL strSql
Set LOCReport = CurrentDb.OpenRecordset("SELECT tblCapacityTotal.Date, tblCapacityTotal.CountOfTSTAT_REFNO FROM tblCapacityTotal")
'selects named excel worksheet
Set datasheet = AppExcel.ActiveWorkBook.Sheets("RawData")
RepType = 1
Call Report_Run11(LOCReport, datasheet, RepType)
AppExcel.StatusBar = "Running Clinic Utilisation Part 2"
strSql = "SELECT Format(dbo_tblServicePointTimeslots.START_DTTM,""y yyymm"") AS [Date], Count(dbo_tblServicePointTimeslots.TSTAT_REFNO) AS CountOfTSTAT_REFNO INTO tblCapacityBooked " & vbCrLf & _
"FROM (dbo_tblServicePointTimeslots INNER JOIN dbo_tblServicePointSessions ON dbo_tblServicePointTimeslots.SPSSN_REFNO = dbo_tblServicePointSessions.SPSSN_REFNO) INNER JOIN dbo_tblReferenceValues ON dbo_tblServicePointTimeslots.TSTAT_REFNO = dbo_tblReferenceValues.RFVAL_REFNO " & vbCrLf & _
"WHERE (((dbo_tblServicePointTimeslots.START_DTTM) Between [StartDate] And [EndDate]) AND ((dbo_tblServicePointSessions.CODE) Like ""f2fdn*"") AND ((dbo_tblReferenceValues.DESCRIPTION) Like ""booked"")) " & vbCrLf & _
"GROUP BY Format(dbo_tblServicePointTimeslots.START_DTTM,""y yyymm"");"
DoCmd.RunSQL strSql
Set LOCReport = CurrentDb.OpenRecordset("SELECT tblCapacityBooked.Date, tblCapacityBooked.CountOfTSTAT_REFNO FROM tblCapacityBooked")
'selects named excel worksheet
Set datasheet = AppExcel.ActiveWorkBook.Sheets("RawData")
RepType = 1
Call Report_Run12(LOCReport, datasheet, RepType)
AppExcel.StatusBar = "Indirect Contacts"
strSql = "SELECT dbo_vwSchedules.Service, Format([SchduleDate],""yyyymm"") AS [Date], Count(dbo_vwSchedules.SCHDL_REFNO) AS CountOfSCHDL_REFNO INTO tblIndirectContacts " & vbCrLf & _
"FROM dbo_vwSchedules " & vbCrLf & _
"WHERE (((dbo_vwSchedules.SchduleDate) Between [StartDate] And [EndDate]) AND ((dbo_vwSchedules.ServiceID) Like ""dnu"") AND ((dbo_vwSchedules.StatusID) Like ""i"") AND ((dbo_vwSchedules.SchdlTypeID) Like ""c*"") AND ((dbo_vwSchedules.Shared) Is Null)) " & vbCrLf & _
"GROUP BY dbo_vwSchedules.Service, Format([SchduleDate],""yyyymm"");"
DoCmd.RunSQL strSql
Set LOCReport = CurrentDb.OpenRecordset("SELECT tblIndirectContacts.Service, tblIndirectContacts.Date, tblIndirectContacts.CountOfSCHDL_REFNO FROM tblIndirectContacts")
'selects named excel worksheet
Set datasheet = AppExcel.ActiveWorkBook.Sheets("RawData")
RepType = 1
Call Report_Run14(LOCReport, datasheet, RepType)
AppExcel.StatusBar = "Group Contacts"
strSql = "SELECT dbo_vwSchedules.Service, Format([SchduleDate],""yyyymm"") AS [Date], Count(dbo_vwSchedules.SCHDL_REFNO) AS CountOfSCHDL_REFNO INTO tblGroupContacts " & vbCrLf & _
"FROM dbo_vwSchedules INNER JOIN dbo_tblSchedules ON dbo_vwSchedules.PARNT_REFNO = dbo_tblSchedules.SCHDL_REFNO " & vbCrLf & _
"WHERE (((dbo_vwSchedules.SchduleDate) Between [StartDate] And [EndDate]) AND ((dbo_vwSchedules.ServiceID) Like ""dnu"") AND ((dbo_tblSchedules.SATYP_REFNO) Like ""1452"")) " & vbCrLf & _
"GROUP BY dbo_vwSchedules.Service, Format([SchduleDate],""yyyymm"");"
DoCmd.RunSQL strSql
Set LOCReport = CurrentDb.OpenRecordset("SELECT tblGroupContacts.Service, tblGroupContacts.Date, tblGroupContacts.CountOfSCHDL_REFNO FROM tblGroupContacts")
'selects named excel worksheet
Set datasheet = AppExcel.ActiveWorkBook.Sheets("RawData")
RepType = 1
Call Report_Run13(LOCReport, datasheet, RepType)
End Select
DoCmd.SetWarnings True
AppExcel.StatusBar = "Run has finished"
MsgBox "Run has finished"
AppExcel.StatusBar = False
End Sub