Databases - MS Access


Versions

There are several versions of MS Access 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).


Methods vs Actions

Many commands have both a method and an action. 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. If you use the Access 97 SR II wizard to create a command button, it will use the obsolete form - DoMenuItem 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. produces Notice - for positive values, Str() adds a leading space where the sign would go, but Format() doesn't.


Getting Data into Word Documents

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.


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.


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

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.


References / Additional Reading

comp.databases.ms-access

Tips for Access Database Users by Allen Browne
There are many tips, including several on comboboxes. Also, I specifically want to remember that the following are available
Creating an Audit Log
Carry data over to new record
Fill a ListBox or Combo using code

Help from lebans.com
FormatByCriteria
List/Combo Boxes

Author: Robert Clemenzi
URL: http:// mc-computing.com / Databases / MSAccess / index.html