Comparing Computer Languages - MS Access 97

This page currently contains MS Access 97 specific notes. It will be reorganized into an index later.

The MS Access programming language is based on VisualBasic.


Debug

Debug.Print "any string or value"


Errors

It is possible to use File / Save As/Export... / To an External File or Database to save a report in Rich Text Format (RTF). However, the formatting is not reliable and, in my experience, some data may be loss. (MS Access 97 SR-2) The format destruction is a major nuisance, but the actual loss of data is NEVER acceptable. (I verified the data loss in the rtf file.)


Basic Component Parameters

When entering parameters in the property editer, sometimes there isn't enough room to read the data. You can open a new text window by pressing Ctrl-F2 or by right clicking the parameter and selecting Zoom....


Setting Fonts

In Datasheet View, the display font can not be set unless a default printer is selected. (Hum, that's lame.)

Also, the selected font applies to all fields (columns). There is no way to set different fonts for different fields - such as proportional for comments and courier for numbers. (That's very lame.)

There is no way to bold individual characters in a memo field.

(Paradox does not have any of these problems. :) But it does use a Formatted Memo field to allow character formatting.)

In a form, each field can have its own font.


Setting Font Colors


ScrollBar Control


Menus and ToolBars


Opening Windows Common Controls

FileOpen, Save, etc.

From the Toolbox, select More Controls / Microsoft Common Dialog Control, version 6.0.

  ActiveXCtl0.DialogTitle = "Import Text Data"
  ActiveXCtl0.Flags = cdlOFNHideReadOnly
  ActiveXCtl0.ShowOpen


Misc

Use CurrentDB to refer to the current database
Create a new Table
  CurrentDB.Execute("select * into xx from yy"), dbFailOnEror

Append to an existing table
  CurrentDB.Execute("Insert Into xx select * from yy"), dbFailOnEror
The following help example fails if the word Set is omitted! (In VB, set causes a variable to point to the object. BTW, in MS Access (before 97), searching the help for Set returned nothing.)
  Dim dbsA As Database
  Set dbsA = CurrentDB
Use a Recordset to reference a table.

Warning: If Form.DataEntry is set to yes, then no existing data will be displayed!


Text Box References

For a Text box control, use Text or Value to access the current contents.

Neither of these appears on the list of Text Box Control Properties. (What worthless help. As usual, if you don't know the answer, don't bother looking for it in the help :( I found this by clicking on DefaultValue / See Also / Value Property. Sometimes you're lucky, this time it took 4 hours.


Select Statement

The following trivial select statement took forever to figure out (> 4 hrs)
  "select * " & _
  "into  [New table] " & _
  "from  [Some table] " & _
  "where [Test case id]='" & [Test case id].value & "'"

  &  - string concatenate
  [] - use brackets to indicate an identifier (name) which
       contians spaces (field name or component name)
  _  - line continuation code (space/underscore)
  '  - single quote required when entering string values 
       in a select statement

  The where clause compares a value in a table 
  with a value on the form


Find a Record

How do you set the form's current record? - seek, find, locate ?

Actually, the code is very simple, 3 hrs to figure it out (more worthless help). FindFirst accepts a criteria string used to find the record. However, FindFirst can not be used on the form. Instead, you clone the current recordset, find the record, then use the bookmark variables to synchronize the display. (Found in the RecordsetClone help. No pointers from any place logical.)

  Form.RecordsetClone.FindFirst _
     "[Table Field Name]='" & [FormField_UIText].value & "'"
  Form.Bookmark = Form.RecordsetClone.Bookmark
Form. is optional and not required.
Form_Current is called when a new record is displayed.

Oh yes, the help is not quite complete - If you are using an "attached" combobox to search for an existing record, then you must include Form.Undo.

Private Sub FirmName_BeforeUpdate(Cancel As Integer)
  Cancel = True
  Form.RecordsetClone.FindFirst _
     "[Table Field Name]='" & [FormField_UIText].value & "'"
  Form.Undo
  If RecordsetClone.NoMatch Then
    MsgBox "Record not found"
  Else
    Form.Bookmark = Form.RecordsetClone.Bookmark
  End If
End Sub


Referring to a Form

Forms!FormName.object
Forms![Form Name].object       Use brackets if the name contains a space
Me!object                      Refers to the form that the code is in
Form.object                    Uses the Form property
Screen.ActiveForm.object       Refers to the form with input focus


Screen.ActiveControl           Refers to the control with input focus

formProperty                   Refers to the propery or control/object
object                         without explicitly naming the form


Naming Syntax

  object.method                . - Use before methods and properties

  collection!userDefinedName   ! - Use before any name you created
                                   Normally, these are members of a 
                                   collection

  [Some Name]                  Brackets indicate a
                                 Table, Query, Form, Report, Field, Control
                               These are required if the name contains 
                               a space, otherwise, they are optional
                               In the query builder, these are NOT optional -
                               when they are omitted, the text is treated 
                               as a string and placed in double quotes

  #1-30-00#                    Pound signs indicate a date

  "Some text"                  Double quotes indicate a string


Linking Tables

In order to link 2 tables in a 1-to-many relationship, when the link is based on several fields, first create an index on the master table where the field combination is unique (this is defined in the index definition). Then, in the relationship definition, link the fields.


Linking to Tables in Another Database

When creating a new table, it is possible (frequently, desirable) to make it a link to an existing table in another database (.mdb) file. The problem is that these links are absolute - you must provide an explicit path to the other database. In order to determine what database an existing link is pointing to open the linked table in design mode, right click the title bar, select Properties, and check Description. (If the .mdb file is missing, then double clicking it will display the name of the missing file.) (MS Access 97 SR-2)

This is one of the main differences between Paradox and MS Access - Paradox uses aliases to link tables. Actually, Access allows ODBC links (aliases) to be used EXCEPT when linking to other MS Access databases. Then only explicit links are allowed. This is the best reason I know NOT to use MS Access.

Why is this so important? Well, I want to develop a report (or form or query) using a copy of the live data (you never want to develop anything using a live database). When the database is copied, the links need to be changed to point to the copy. After the report is developed, the link must be changed again to point to the live data. This is accomplished via the MS Access menu. Select

   Tools / Add-Ins / Linked Table Manager
The resulting dialog box will display all the linked tables in the current database and where they are linked to! (Notice that the dialog box is narrow and part of the link name will probably be truncated. Oh well.) To change the link, you simply select the tables, press OK, and select a new database (.mdb file). (Uh, you can't change the link to ODBC or anything else.)


Table Incompatabilities

If you ever make the mistake of upgrading MS Access, then there is an extremely high probability that everyone who wants to access your data will also have to upgrade their systems. Talk about a sales hook - if one person in your corporation makes the mistake of buying the new version of some application (uh, like when a new system is bought), then the entire corporation must also upgrade or else there is no way to share information. Actually, most software companies practice this crap. In the most recent Microsoft case, I concede that this is probably a good business practice.

However, if the government ever allowed a monopoly to do this, it could cripple our economy while making some company very rich.

Actually, there is a work around to this, simply store your data in tables formatted for the older version of Access. However, you will still need separate Forms and Reports for each version on your network.


Defining Tables

Never ever for any reason place a blank in table or field names.
It makes writing code and/or SQL much more painful.
(Actually, I use spaces in table names. It's not too bad unless you convert the tables to a system which does not allow spaces - such as "upgrading" to a client/server system.)

The maximum number of characters allowed for

In order to produce a pick list for a field, set the Field Data type to Text and on the Lookup tab, set

You can also set this using the Lookup Wizard...

You can link tables using Tools / Relationships..., however, only one "Relationship" can be defined. I want several and a way to print it.


To determine the type of a control, use This is from the Control Object help. However, ControlType is not in the list of possible properties; it is also not in the pop-up when me. is typed. Also, this property does not work on forms (I guess that forms are not derived from controls).


Reading and Writing GUI Object Properties

It sounds simple, but there is a catch - a GUI Object must be selected before you can read or write its properties. Of course, this means that hidden fields can not be used to store temporary values because you can not set the focus to any object where Visible=false.


Misc


Pointers (Addresses of Functions)

API: Access/Office and AddressOf Operator

Acheiving Callback Functionality in Access 97 and Office 97 has source code in an Excel spreadsheet.

It was pretty hard to find references on this - most of the relavent search results pointed to missing pages.


Author: Robert Clemenzi - clemenzi@cpcug.org
URL: http:// cpcug.org / user / clemenzi / technical / Languages / MSAccess97.html