Firefox Unexpectedly Popular

Falls in the use of older versions of MSIE are always offset by rises in use of newer versions and some rise in the use for other browsers. The July 08 browser statistics for W3 Schools show that the use of all versions of MSIE fell 2.0% while use of Firefox rose 1.6% (and Opera gains 0.2%). Has the use of MSIE7 peaked? (Insert usual caveats about selective audience of this site, monthly fluctuations, rounding errors, yada yada.)

Change Default for Show Markup in MS-Word 2003

An annoying feature in Microsoft Word 2003 is that the default setting for the Reviewing toolbar is Final Showing Markup, so that all changes in your documents are highlighted when you open it, even after you accept all changes and saved the document previously. The solution is to unset your Security option Make hidden markup visible when opening or saving.. See How to turn off annoying MS Word Features, 'Change Default for "Show Markup" Box' for this and other solutions to Word annoyances.

List Empty Access Tables using Perl

A port of my Python DBI program to Perl. Note that you have to install package DBD-ODBC for the ODBC driver.

use warnings;
use strict;
use DBI;

use constant MDB_PATH => '<path>';

my $dbh = DBI->connect('DBI:ODBC:DRIVER=Microsoft Access Driver (*.mdb);Dbq=' . MDB_PATH);

my $sth = $dbh->prepare(
  "SELECT name FROM MSYSOBJECTS WHERE name NOT LIKE 'MSYS%' AND type = 1");
$sth->execute();
my $ref = $sth->fetchall_arrayref();

for my $row ( @{$ref} )  {
  my $table_name = @$row[0];
  my $sth = $dbh->prepare("SELECT COUNT(*) FROM [$table_name]");
  $sth->execute();
  my @data = $sth->fetchrow_array();
  if ($data[0] == 0) {
    print "$table_name is empty\n";
  }
}

$dbh->disconnect();

See Also

• List Empty Access Tables using Python.

PS

2008-08-08: Replaced sprintf("SELECT COUNT(*) FROM [%s]", $table_name) with "SELECT COUNT(*) FROM [$table_name]" since Perl can evaluate variables in a string.

Added empty parentheses when calling functions to be consistent.

List Empty Access Tables using Python

We wanted to find empty tables in a Microsoft Access database. Below is a Python script that uses the PythonWin odbc module to find empty tables in an MS-Access database. Edit the required path to your database file by modifying the MDB_PATH variable.

Follow the note at the start of the script to configure your MS-Access database security if you get the following message: dbi.program-error: [Microsoft][ODBC Microsoft Access Driver] Record(s) cannot be read; no read permission on 'MSYSOBJECTS'. in EXEC.

# List empty tables in an Access database by Kam-Hung Soh 2008.
# Before using this script, you have to allow User and Group Permissions in Access.
# 1. Open database.
# 2. Select menu item Tools / Security / User and Workgroup Permissions.
# 3. In 'User and Group Permissions' dialog:
# 3.1. Select User/Group Name = Admin.
# 3.2. Select Object Name = MSysObjects.
# 3.3. Check 'Read Data' check box.
# 3.4. Press OK button to close dialog box.

import odbc

MDB_PATH = r'<path>'

conn = odbc.odbc(r"DRIVER={Microsoft Access Driver (*.mdb)}; Dbq=%s;" % MDB_PATH)
cur = conn.cursor()
cur.execute(r"SELECT name from MSYSOBJECTS WHERE name NOT LIKE 'MSYS%' AND type = 1")
for x in cur.fetchall():
    table_name = x[0]
    cur.execute(r'SELECT COUNT(*) FROM [%s]' % table_name)
    row = cur.fetchone()
    if row[0] == 0:
        print table_name + ' is empty'
cur.close()
conn.close()

Script Notes

MS-Access stores object metadata in a system table called MSysObjects. In this table, a user table object has a type value of '1' and its name doesn't start with 'MSys'. This script first gets a list of all user tables from MSysObjects, then counts the number of rows in those tables. If a table has no rows, the script prints the table name and a message.

The fetchall() function always returns a list of tuples even if only one column is selected, so you have to extract the required column data using an array operator (e.g. x[0]).

The table name in the second cur.execute() SQL statement is delimited by square brackets in case the table name has whitespaces. Without these delimiters, you may see the following message: dbi.program-error: [Microsoft][ODBC Microsoft Access Driver] Syntax error in WITH OWNERACCESS OPTION declaration. in EXEC.

See Also

• List Empty Access Tables using Perl.