My custom Outlook form added some canned text in the message object in the Item_Open() function. Annoyingly, when I open the form in design mode, the Item_Open() is called, cluttering up the message object. It turns out that you have to hold down the Shift key when opening a form to prevent VBScript in the form from executing.
Holding down the
When a MS-Access 2003 VBA script tried to open MS-Word and run the mail merge function, VBA displayed this error message:
Run-time error '5922': Word was unable to open the data source
The problem was tracked to this statement:
Dim objWordApp As Object
Set objWordApp = CreateObject("Word.Application")
With objWordApp
...
.ActiveDocument.MailMerge.OpenDataSource Name:=CurrentDb.Name, SQLStatement:=sQuery, SubType:=wdMergeSubTypeWord2000
There's plenty of different solutions to this problem on the web. In this case, the problem was due to the second argument, the SQL query, sQuery, which had an error.
I spent some time browsing the web to investigate the run-time error, when it would have been more effective to first check the arguments in the subroutine call. Live and learn.
If you may have to write a query in MS-Access 2003 to get the maximum or minimum of the values of two columns, you could use the iif() expression like this:iif(n1 > n2, n1, n2) or iif(n1 < n2, n1, n2) to obtain the maximum or minimum, respectively. The expression becomes more complicated when you have to compare three columns:
IIf(n1>n2, IIf(n1>n3,n1,n3), IIf(n2>n3,n2,n3))
Such expressions are hard to get right, and let's not even consider writing one to compare four columns! (In case you were wondering, we can't use Access' max() and min() aggregate functions because they operate on rows.)
Access allows you to call VBA functions in queries, so we can replace the long and cumbersome iif() expression with a call to a custom VBA function. In the rest of this article, we will:
Here's a simple VBA function one to find the maximum of a list of values (just change the name and reverse the comparison to find the minimum):
Function max_value(ParamArray n() As Variant)
Dim i As Integer
Dim ret As Variant
ret = n(0)
For i = 1 To (UBound(n))
If ret < n(i) Then ret = n(i)
Next i
max_value = ret
End Function
The ParamArray declares n() as a variable argument list (or varargs in C). Using ParamArray means that the function is not restricted to a pre-defined number of parameters, and using a Variant means that the function will work for different types of data, such as numbers and dates.
The body of the function just loops through all the values in the argument list and returns the largest value.
If you have a table called Number with fields n1, n2, n3, n4, n5, you can call this function in a query, just like any other built-in function, like this:
SELECT max_value(n1, n2, n3, n4, n5) as max_value, n1, n2, n3, n4, n5 FROM [Number];If you want to generate a dummy or extra row, say for a combo box control, then you can write a SQL statement like this: select col1 from table union all select 'All' from table. What this statement does is create an extra row with text All
in the last row in your combo box.
While testing, we found that this statement works in Microsoft Access 2003 but not in Access 2000. In Access 2000, it works if your table has at least one row, but if your table has no rows, no result is returned! However, if you use the OLEDB interface, the dummy row is returned, so at least we can continue working.
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();
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.
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()
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.
I wanted to export all tables from an Access database into a folder of CSV formatted text files. For most data types, such as Text or Integer, all data was exported without any loss. However, floating point numbers (Float or Double) were exported with only two decimal places.
You can export a Microsoft Access table to a text file using either the menu item File / Export or this Access SQL statement from KB208408 …
SELECT * INTO [Text;FMT=Delimited;HDR=Yes;Database=<Path>].[<Table>.csv] FROM <Table>
Here's three solutions with varying advantages:
A slight digression: schema.ini is a file used by the Text Driver to determine the characteristics of tables and columns of a database. You can find the syntax and properties of the schema.ini file in Schema.ini File (Text File Driver).
When exporting tables, Access creates a schema.ini file in the export folder if that file does not exist. If there is an existing schema.ini file and the section for the output CSV file does not exist, Access will add a new section for the output CSV file. Otherwise, Access does not change the schema.ini file.
None of the solutions are very satisfactory for writing a general program to export tables from an Access database into a folder of text files. The simplest solution is to define the required number of decimal places in the database connection string (the part between the square brackets in the SQL statement). However, I couldn't find any formal specification of this string on the Web.
The table below relates users to groups. Which groups do two specified users have in common?
ID USER_ID GROUP_ID
---------- ---------- ----------
1 1 1
2 2 1
3 1 2
4 3 2
5 2 2
Here's a query that works in MS-Access and Oracle Express:
select group_id
from
user_group where group_id in (select group_id from user_group where user_id = 1)
and user_id = 2;
GROUP_ID
----------
1
2
Note: I've used where … in because a user can belong to more than one group. If the sub-query returns more than one row, Oracle Express reports this error:
ORA-01427: single-row subquery returns more than one row
You can look at the problem as one where you're looking for the intersection of two sets. If your DBMS supports it, you can use the intersect set operator. The following query works in Oracle Express:
select group_id from user_group where user_id = 1
intersect
select group_id from user_group where user_id = 2;
GROUP_ID
----------
1
2