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)

Can't see USB drive

I suspect with updated security policies at my work is the reason I can no longer see my USB drives in My Computer. I opened the disk management by right-clicking My Computer. Select Manage. Select Disk Management. I can see the USB drives there. I even reassigned the drive letters, but still do not see the drive. I can go to the drive at the command prompt. I thought originally this may be my only option.
It is not. You can open the drive through the Disk Management application and see the drive contents there. Inconvenient, but a workable solution.
I googled for a solution beyond reassigning the drive letters, which seems to be most of the results. There was a small note in one forum that suggested tying all your USBs to a single drive letter and adding them separately as folders.
Rather than assign mine to a single letter, I kept the distinct letters and setup a folder in My Documents labeled USB. Inside the Disk Managment application, right-click the drive and select:


Select the Add button:


Select Browse to locate folder to assign this USB drive to:


Expand tree and select the folder or create a new folder to assign this drive:


Select OK and open Explorer to the folder you just assigned. You should now see the contents of your USB.