[AccessD] How to tell how much resources have been used

[Bill Benson]

Code... i hope it all posts.

Private Sub cmdCreateReports_Click()
Dim Rst As DAO.Recordset
Dim SQL As String
Dim bContinue As Boolean
Dim CurDB As DAO.Database
Dim strSQL As String
Dim i As Long
Dim bDoPartnerReportsAllEast As Boolean, bDoPartnerReportsAllWest As
Boolean, bDoManagerReportsAllEast As Boolean, bDoManagerReportsAllWest As
Boolean
Dim bDoPartnerReportsEast As Boolean, bDoPartnerReportsWest As Boolean,
bDoManagerReportsEast As Boolean, bDoManagerReportsWest As Boolean
InitialMemoryUsed = GetCurrentProcessMemory
Set CurDB = CurrentDb
m_strFY = ""
If dtBeginSD = "" Or dtEndSD = "" Then
    MsgBox "Make sure valid beginning and ending sign dates have been
entered"
    Exit Sub
End If
If Not IsDate(dtBeginSD) Or Not IsDate(dtEndSD) Then
    MsgBox "Make sure valid beginning and ending sign dates have been
entered"
    Exit Sub
End If
For i = 0 To lstFY.ListCount - 1
    If lstFY.Selected(i) Then
        m_strFY = lstFY.Column(0, i)
        Exit For
    End If
Next
SaveSetting "PDR", "RuntimeSettings", "LastReportDateBegin",
CStr(Me.dtBeginSD)
SaveSetting "PDR", "RuntimeSettings", "LastReportDateend", CStr(Me.dtEndSD)
g_strBegin = CStr(Me.dtBeginSD)
g_strEnd = CStr(Me.dtEndSD)
i = 0
For i = 0 To lstReports.ListCount - 1
    If lstReports.Selected(i) Then
        Select Case lstReports.Column(0, i)
        Case Is = "Deliverables By Partner (All - East)"
            bDoPartnerReportsAllEast = True
        Case Is = "Deliverables By Partner (All - West)"
            bDoPartnerReportsAllWest = True
        Case Is = "Deliverables By Manager (All - East)"
            bDoManagerReportsAllEast = True
        Case Is = "Deliverables By Manager (All - West)"
            bDoManagerReportsAllWest = True
        Case Is = "Deliverables By Partner (East)"
            bDoPartnerReportsEast = True
        Case Is = "Deliverables By Partner (West)"
            bDoPartnerReportsWest = True
        Case Is = "Deliverables By Manager (East)"
            bDoManagerReportsEast = True
        Case Is = "Deliverables By Manager (West)"
            bDoManagerReportsWest = True
        Case Is = "Compliance By Manager (All - East)"
        Case Is = "Compliance By Manager (All - West)"
        Case Is = "Signed Projects Summary (All - East)"
        Case Is = "Signed Projects Summary (All - West)"
        End Select
    End If
Next

Set CurDB = CurrentDb
On Error Resume Next
Debug.Print "Used " & Format((GetCurrentProcessMemory - InitialMemoryUsed)
/ 1024 ^ 2, "0.0") & " mb by time parameters obtained."
DoCmd.Close acTable, "TblSpoolReports", acSaveNo
DoCmd.DeleteObject acTable, "TblSpoolReports"
On Error GoTo 0
CurDB.Execute "CREATE TABLE TblSpoolReports (ID COUNTER, ReportName
Text(255), EmployeeID LONG, SignDateBegin DATETIME,SignDateEnd
DATETIME,Spooled DATETIME,FY TEXT(10))"
Debug.Print "Used " & Format((GetCurrentProcessMemory - InitialMemoryUsed)
/ 1024 ^ 2, "0.0") & " mb after creating spool table."
If bDoPartnerReportsEast Then
    strSQL = "SELECT DISTINCT B.ID as EmployeeID, B.Name AS [Engagement
Partner],B.[First Name] as FirstName, B.Email"
    strSQL = strSQL & " FROM ([User Information List] AS B INNER JOIN [PDR
Tracking] AS A ON B.[ID] = A.[Tax Partner])"
    strSQL = strSQL & " LEFT JOIN tblCityRegion ON B.City =
tblCityRegion.City WHERE tblCityRegion.Region='East'"
    Set Rst = CurDB.OpenRecordset(strSQL)
    With Rst
        While Not .EOF
            If QualifyingRecords("Partner", "East", Rst.Fields(0).Value)
Then
                CurDB.Execute "Insert Into TblSpoolReports
(ReportName,EmployeeID,SignDateBegin,SignDateEnd,FY) Values
('PartnerReportsEast'," & .Fields("EmployeeID").Value & ",#" & g_strBegin &
"#,#" & g_strEnd & "#,'" & m_strFY & "')"
            End If
            .MoveNext
        Wend
    End With
    Debug.Print "Used " & Format((GetCurrentProcessMemory -
InitialMemoryUsed) / 1024 ^ 2, "0.0") & " mb after inserting
PartnerReportsEast records into spool table."
    Rst.Close
    Set Rst = Nothing
End If
If bDoPartnerReportsWest Then
    strSQL = "SELECT DISTINCT B.ID as EmployeeID, B.Name AS [Engagement
Partner],B.[First Name] as FirstName, B.Email"
    strSQL = strSQL & " FROM ([User Information List] AS B INNER JOIN [PDR
Tracking] AS A ON B.[ID] = A.[Tax Partner])"
    strSQL = strSQL & " LEFT JOIN tblCityRegion ON B.City =
tblCityRegion.City WHERE tblCityRegion.Region='West'"
    Set Rst = CurDB.OpenRecordset(strSQL)
    With Rst
        While Not .EOF
            If Rst.Fields(0) = 25 Then Stop
            If QualifyingRecords("Partner", "West", Rst.Fields(0).Value)
Then
                CurDB.Execute "Insert Into TblSpoolReports
(ReportName,EmployeeID,SignDateBegin,SignDateEnd,FY) Values
('PartnerReportsWest'," & .Fields("EmployeeID").Value & ",#" & g_strBegin &
"#,#" & g_strEnd & "#,'" & m_strFY & "')"
            End If
            .MoveNext
        Wend
    End With
    Rst.Close
    Set Rst = Nothing
    Debug.Print "Used " & Format((GetCurrentProcessMemory -
InitialMemoryUsed) / 1024 ^ 2, "0.0") & " mb after inserting
PartnerReportsWest records into spool table."
End If
If bDoManagerReportsEast Then
    strSQL = "SELECT DISTINCT B.ID as EmployeeID, B.Name AS [Engagement
Manager], B.[first Name] as FirstName, B.Email"
    strSQL = strSQL & " FROM ([User Information List] AS B INNER JOIN [PDR
Tracking] AS A ON B.[ID] = A.[Tax Manager])"
    strSQL = strSQL & "LEFT JOIN tblCityRegion ON B.City =
tblCityRegion.City WHERE tblCityRegion.Region=""East"""
    Set Rst = CurDB.OpenRecordset(strSQL)
    With Rst
        While Not .EOF
            If QualifyingRecords("Manager", "East", Rst.Fields(0).Value)
Then
                CurDB.Execute "Insert Into TblSpoolReports
(ReportName,EmployeeID,SignDateBegin,SignDateEnd,FY) Values
('ManagerReportsEast'," & .Fields("EmployeeID").Value & ",#" & g_strBegin &
"#,#" & g_strEnd & "#,'" & m_strFY & "')"
            End If
            .MoveNext
        Wend
    End With
    Rst.Close
    Set Rst = Nothing
    Debug.Print "Used " & Format((GetCurrentProcessMemory -
InitialMemoryUsed) / 1024 ^ 2, "0.0") & " mb after inserting
ManagerReportsEast records into spool table."
End If
If bDoManagerReportsWest Then
    strSQL = "SELECT DISTINCT B.ID as EmployeeID, B.Name AS [Engagement
Manager], B.[first Name] as FirstName, B.Email"
    strSQL = strSQL & " FROM ([User Information List] AS B INNER JOIN [PDR
Tracking] AS A ON B.[ID] = A.[Tax Manager])"
    strSQL = strSQL & "LEFT JOIN tblCityRegion ON B.City =
tblCityRegion.City WHERE tblCityRegion.Region=""West"""
    Set Rst = CurDB.OpenRecordset(strSQL)
    With Rst
        While Not .EOF
            If QualifyingRecords("Manager", "West", Rst.Fields(0).Value)
Then
                CurDB.Execute "Insert Into TblSpoolReports
(ReportName,EmployeeID,SignDateBegin,SignDateEnd,FY) Values
('ManagerReportsWest'," & .Fields("EmployeeID").Value & ",#" & g_strBegin &
"#,#" & g_strEnd & "#,'" & m_strFY & "')"
            End If
            .MoveNext
        Wend
    End With
    Rst.Close
    Set Rst = Nothing
    Debug.Print "Used " & Format((GetCurrentProcessMemory -
InitialMemoryUsed) / 1024 ^ 2, "0.0") & " mb after inserting
ManagerReportsWest records into spool table."
End If

'Access has already gone from near zero to 563 mb of resource usage as of
this point.
... Rest of Sub