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
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 2010Applications 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
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
In general, I never write macros.
Menu Commands
DoCmd.RunCommand acCmdSubformDatasheetIf 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, , acMenuVer70acMenuVer70 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
Calculated Values
=Str$([Quantity]) & "/" & Str$([Quantity]) =Str$([Quantity]) & "/" & Format$([Quantity])produces
5/ 5 5/5Notice - for positive values,
Getting Data into Word Documents
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 / Merge It with MS Word to ????
Clearing a 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.
If SomeField <> "Ship" Then End Iffails 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")
Tab Controls
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.
If IsNull(AssemblyTypeID_UIField.Value) _ Or AssemblyTypeID_UIField.Value = "no" Then
Format Property
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