Databases - MS Access

Misc	MS Access page in Languages section. Just a bunch of notes.
Sub-forms	MS Access uses sub-forms to display 1->M (one-to-many) relationships.
Inventory Control Demo	This discusses one of the built-in demos which comes with MS Access.
Queries	Solutions to special challenges wrt MS Access queries.
IDC/HTX Queries	This applies specifically to writing queries for idc/htx files. However, most of this also applies to any MS Access queries via an ODBC interface (ie, it is language independent) and it also applies to queries written within MS Access itself.
CM Data	Specific data on how to produce a list of tables, forms, and reports, and on how to access the Description associated with fields and tables.
Graphs/Charts	How to plot data.
Calendar Examples	How to pop-up a calendar so that you can select a date. Warning: The calendar objects in MS Access 97 won't work with MS Access 2007, the date function fails in MS Access 2010
Versions	How to get several different MS Access versions to share the same data.
Dialog Boxes	Opening a new form as a Dialog Box is not a problem. However, transferring data back to the calling form is.
My Library Modules	This is a link to various modules (libraries) I have written.
Format problem	When used in queries, several string processing commands may fail - Format, Left, Right, Mid, etc. This describes the problem and provides a solution.
ActiveX Components	Microsoft provides a number of ActiveX components that you can use in any VBA application (such as an Access form). Unfortunately, there is no software or procedure for you to distribute those components so that YOUR application can run on other machines.
Application Navigation	Once an application has a few forms, it should have a method to select them. In other cases, a single report may be used with several queries.
Modifying Tables	How to modify the data in a table using code.
Access XP	Warning: Code examples for MS Access 97 won't work with MS Access 2000/2002/XP
Template

Versions

There are several versions of MS Access

MS Office 97 Pro includes MS Access 97
MS Office 2000 Pro includes MS Access 2000
MS Office XP Pro includes MS Access 2002
MS Office 2007 Pro includes MS Access 2007
MS Office 2010 ??? includes MS Access 2010

Applications written in one version are not backward compatible (but the data can be made backward compatible).

I have an application written in MS Access 97. It needs to be modified (but not converted) to run in each of the later versions. It must not be "read only" to be "modified".

MS Access 97 is one of the best products Microsoft has ever produced ... even the help is pretty good. However, they keep fiddling with it and reducing its user friendliness. There is basically no backward compatibility.

In Access XP (2002), the help is missing several critical topics and is actually wrong in others (it shows the older, no longer supported, way to accomplish certain functions).

MS Access 2007 uses a different (and much simpler) method to popup a calendar to select dates.

MS Access 2010 broke the date function. Use CDate(Now) instead.

DAO Object Model

The DBEngine object is the top level object in the DAO object model.

It contains 1 or more Workspaces (aka sessions)

A Workspace contains 1 or more Databases (a collection of tables, linked tables, and queries).

TableDef	The table structure (fields and indices). Use this to connect to a remote table.
Recordset	The contents of the table (or view). A new Recordset can be opened against a TableDef, a QueryDef, or an existing Recordset.
QueryDef	A query

Methods vs Actions

Many commands have both a method and an action.

Actions are executed in macros
The associated DoCmd.method performs the same action when executed via Visual Basic code.

For instance, DoCmd.OpenForm performs the same action as OpenForm in a macro.

In general, I never write macros.

Menu Commands

MS Access does not provide methods for all possible menu commands. However, there are constants to perform all menu actions via DoCmd.RunCommand.

DoCmd.RunCommand acCmdSubformDatasheet

If you use the Access 97 SR II wizard to create a command button, it will use the obsolete form - DoMenuItem

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 2, , acMenuVer70

acMenuVer70 indicates that this is using Access 95 syntax.

The help also says that the only way to determine the numerical values that represent the various commands is to use the Macro editor "in an earlier version of MS Access". Hum, more worthless help.

Misc

The toggle button does not work when Form.AllowEdits is disabled. Therefore, enable edits and use the datafield.Locked property to control when a field can be edited.

Calculated Values

When a field - [Quantity] - contains a numeric value, this is how to use it in a string.

=Str$([Quantity]) & "/" & Str$([Quantity])
=Str$([Quantity]) & "/" & Format$([Quantity])

produces

5/ 5
5/5

Notice - for positive values, Str() adds a leading space where the sign would go, but Format() doesn't.

Getting Data into Word Documents

Make a query and run it
From the menu, select File / Save As/Export...
In the dialog box, select To an External File or Database
In Save as type, select Microsoft Word Merge (*.txt) and save the data.

This produces a tab delimited file with strings in double quotes. A currency field contains a dollar sign, but a computed field does not even though its format is set to currency.

The following is from schema.ini which is in the same directory and has the same date & time as the associated txt file.

[DD 250 Items.txt]
ColNameHeader=True
CharacterSet=ANSI
Format=TabDelimited
Col1=LineNumber Integer
Col2=DO_Part_Name Char Width 50
Col3=Expr1 Char Width 255
Col4=Unit_Sale_Price Currency
Col5=Amount Float

Other Methods

Use Tools / Office Links / Publish It with MS Word to create a new Word document with the data in a table.

Use Tools / Office Links / Merge It with MS Word to ????

Clearing a Table

There is no obvious way to delete all the records in a table and to get the AutoNumber (autoincrement) fields to start over. I suppose that a Delete Query would remove the records. Then select SaveAs, delete the original table, and rename the new table to the original name. Finally, you would need to compact the database (to recover the space used by the original table).

This would be useful after testing a new form (which generates records that are for test only).

The Delphi Database Desktop provides an Empty function.

Tools / Utilities / Empty...

Testing a field value

  If SomeField <> "Ship" Then

  End If

fails if SomeField is null. Well, that makes no sense at all. I automatically assumed that the null values should also match. The correct, non-obvious code is

  (SomeField <> "ship") Or (IsNull(SomeField))

This should also work

  (Nz(SomeField) <> "ship")

twips

1 inch equals 1440 twips
1 centimeter equals 567 twips
1 font point equals 20 twips

See the scale mode property.

Tab Controls

There is no way to gray out a single tab. Font, Back Color, and the like are not available on a per tab basis. The only property you can use is Visible. (You could load a different picture ... but I don't use pictures.)

Of course, conditionally hiding controls violates all the good user interface design practices - graying things out is much more user friendly.

The "Tab Control Help" sucks.

There is a problem when reading values from a field - Sometimes the value is null, other times it is "no", and occasionally the form actually writes "no" to the table. I can't figure out why.

  If IsNull(AssemblyTypeID_UIField.Value) _
      Or AssemblyTypeID_UIField.Value = "no" Then

Format Property

On both Forms and Reports, if you make the mistake of using the Format property of a Text box (UIEdit), then the contents will be truncated at 255 characters. (MS Access 97 SR2)

That's right. I had a memo field with about 300 characters in it. The Format property had a greater than (>) to force the display to show/print upper case only. Well, in both cases, only 255 characters were shown. On top of that, the form showed both upper and lower case letters.

As usual, the help does not indicate that this is how it is supposed to work.