Run All Rules In Outlook 2007

I like rules in Outlook. I like to sort and move messages to their proper folders. I like how rules can be set to automatically move these messages to their proper folder.
What I do not like though is that I can miss a message because that message was moved to a folder and I did not see it come in. Even with desktop alerts and window alerts, I can still miss them as they come in.
I found some code that allow the user to manually run all the rules, that were created, in the inbox. I create all the rules I need and uncheck all of them to prevent them from running automatically.
As each email comes in, it stays in the inbox. To me, that helps me ensure that I see those emails come in. When my inbox has accumulated enough emails, I run the code to run the rules which place those emails in their proper folder. My inbox is cleared and is ready to receive more emails.
The URL for the code can be found at this URL, http://www.outlookcode.com/codedetail.aspx?id=1266

	Open Outlook 2007 and press ALT-F11 to go to the Visual Basic editor. Insert a new Module and paste the code. Save the module.
	Return to Outlook and right-click the standard toolbar and select Customize.
	Under the Commands tab, select Macros under Categories. Select and drag Project1.RunAllInboxRules to the toolbar. Select Close and the macro is ready to run.
	I put mine in a menubar and relabeled it.
	I just select this to run all the rules. You will see a message like this.

Hope this works for you. Enjoy!

Create A List of Dependencies

I had been racking my brain trying to get Microsoft Access to tell me what Query corresponds with each Report. It is defined in each Report property RecordSource. This is not stored in the system tables MSysObjects or MSysQueries.
I came up with the solution programmatically by defining variables as AccessObjects and using a For Next loop to go through each Report and nest another loop to go through each Dependency.
In my example, I store this information into a table. I also included to blocks of code to handle apostrophies in the names of the reports and dependencies. The extra single ' plays havoc on the SQL execution because the single ' is used to define SQL statements. (i.e. SELECT * FROM myTable WHERE somefield = 'somevalue' )

Sub CreateListOfReports()

    Dim rpt As AccessObject     ' report object
    Dim rptdep As AccessObject  ' report dependency object
    Dim mySQL As String         ' SQL to insert report name and dependency into a table.
    Dim strReport As String     ' report name
    Dim strReportDep            ' report dependency name
    
    On Error GoTo Err_CreateListOfReports
    
    Application.SetOption "Track Name AutoCorrect Info", 1
    For Each rpt In CurrentProject.AllReports   ' go through each report
        strReport = rpt.NAME                    ' get report name
        DoCmd.SetWarnings False                 ' appending a row to a table so turn off warning prompt.
        
        ' I added this because some of the report names had the apostrophy which caused errors in the SQL execution.
        ' A report named, "Director's Report" would cause an error because the SQL would consider the ' as part of the SQL.
            s = 1
            While s < Len(strReport) And InStr(s, strReport, "'", vbTextCompare)  ' while there is a single ' in the name
                ' find where the single ' is in the report name
                s = InStr(s, strReport, "'", vbTextCompare)
                ' for any single ' in the name, make it become '' to work in the SQL statment.
                strReport = Left(strReport, s) + "'" + Right(strReport, Len(strReport) - s)     ' join the left and right of the report name
                s = s + 2   ' skip past the second ', don't want to mistake the '' for a single.
            Wend
            
            For Each rptdep In rpt.GetDependencyInfo.Dependencies   ' for each report, list each dependency.
            strReportDep = rptdep.NAME                              ' get the dependency name
            Debug.Print strReport, strReportDep                     ' I like to list each in the debugger window.
            
            ' repeat the same for the dependency name when dealing with single '
            ' otherwise, the SQL generates an error when executing.
            s = 1
            While s < Len(strReportDep) And InStr(s, strReportDep, "'", vbTextCompare) ' while there is a single ' in the dependency name
                s = InStr(s, strReportDep, "'", vbTextCompare)  ' find where the single ' is in the name
                strReportDep = Left(strReportDep, s) + "'" + Right(strReportDep, Len(strReportDep) - s) ' replace the single ' with the double ''
                s = s + 2   ' skip the '' you just added.
            Wend
            ' insert the report name and its dependency into the table.
            mySQL = "INSERT INTO tblListReportsAndDependencies (Report, Dependency) VALUES ('" + strReport + "', '" + strReportDep + "')"
            DoCmd.RunSQL mySQL
        Next
    Next
    DoCmd.SetWarnings True                      ' turn on warning prompt
    Exit Sub
    
Err_CreateListOfReports:
    MsgBox Error(Err), vbCritical, "Error " & Err
    
End Sub

Download getdependenciesmodule-bas

Access System Tables

While working on a project with hundreds of reports, each with a query, I wanted a list of each report along with its query. I found out two things. Hidden tables such as MSysObjects and programatically accessing Object dependencies.
I will talk about the hidden tables first.
1. Under 2007, go to the Office button in the upper-left corner of Access.
2. Select Access Options.
3. Select Current Database.
4. Under Navigation, select Navigation Options.
5. Check Show System Objects.

Now when creating a query, you have simple access to tables such as MSysOjects where you can list objects like reports or queries.

To list all the report names, I used

SELECT Name FROM MSysObjects WHERE Not Like "~*" And Not Like "MSys*" AND Type = "-32764"

This lists only reports and excludes system reports.

Under MSysQueries, you can list query expressions.

SELECT MsysObjects.Name, MSysQueries.Expression
FROM MSysQueries INNER JOIN MsysObjects ON MSysQueries.ObjectId = MsysObjects.Id
WHERE (((MsysObjects.Name) Not Like "~*" And (MsysObjects.Name) Not Like "MSys*") AND ((MSysQueries.Attribute)=8) AND ((MsysObjects.Type)=5));

This lists the queries and the criteria. You can list much more.
These system tables do not list what report uses what query. That is where dependencies come in.

Preventing Windows Automatic Update Reboot

IT Dojo on Tech Republic has a challenge that has some very interesting solutions to preventing Windows from trying to automatically reboot after an update. Handy, if your working on a project and do not wish to be interupted.
The user manually reboots the computer when ready.

http://blogs.techrepublic.com.com/itdojo/?p=1055

I chose a method as such:

Run the command gpedit.msc. Press Windows key + R to do this.

In the folder tree, select

Double-click the setting

Enable the setting and select OK.

Folder Size in Windows Explorer

Ever wanted to know how big each folder was? Need to find out which folders are hogging all that disk space?
I googled "Folder Size XP" and found two sites that help you install a DLL file that allows you add a Folder Size column to you detailed file listing.

Those sites are
http://windowsxp.mvps.org/foldersize.htm
and
http://www.codeproject.com/KB/shell/DirectorySize.aspx?df=100&forumid=26286&exp=0&select=1151181

SnagIt is the Bomb!

I wholeheartedly endorse the screen capture program, SnagIt from TechSmith. They offer a free screen capture program called Jing, but SnagIt has so many features that it is worth the cost.

AutoGenerate IF EXISTS, DROP TABLE SQL

I created some SQL that can generate IF EXISTS, DROP TABLE SQL code for all existing tables in a database. Handy, if you need to create SQL for all or even most of the tables in your database without having to write the code yourself. SQL Server Management Studio can generate the code for each table individually, but I'm not aware that it can multiple tables at a time.
I set the Results to Text option because it keeps the text formatting for my SQL.

-- Generate an IF EXISTS, DROP TABLE SQL code for every table that currently exists in a database
-- For optimal text formatting, Set RESULTS TO TEXT to copy code.
-- 09.29.2009 http://tlinget.blogspot.com/

DECLARE @database varchar(128)
DECLARE @sql varchar(4000)

SET @database = 'yourdatabasename' -- User select which database to generate IF EXISTS, DROP TABLE SQL.

SET @sql = 'SELECT ''IF EXISTS(SELECT [name] FROM [' + @database + ']..sysobjects
    WHERE [name] = N'''''' + name + '''''' AND xtype=''''U'''')
    BEGIN DROP TABLE '' + name + '' END''
    FROM [' + @database + ']..sysobjects WHERE xtype=''U'''

PRINT '@sql: ' + @sql -- just some debugging to show the SQL generated

EXEC (@sql)