Converting to Access 2007

Create a template database to be used any time you create a new database.

Any new database is initialized with whatever you placed in the template - settings, properties, forms, reports, macros, modules, references, error logging, splash form, ...

Seriously useful for individuals and developer teams who want consistency. Solves many problem properties (Name AutoCorrect, default references, etc.)

Formatted data in exports and emails. Requires a download from Microsoft. The PDF add-in can be chained into the runtime installation.

Predefined fields (XSD-based settings for name, type, length) and tables provide consistency and speed in table design.

For the first time, you can format text a text box: bold, italics, bulleted lists, fonts, colors, etc.  Use for comments, merge letter reports, ...

Applies to text boxes that are unbound, bound to an expression, or bound to a Memo field that has the Text Format property set to Rich Text (in table design.)

Note: stores HTML (not RTF.)

Keeps a ColumnHistory of the text in the memo field each time it is changed.

Use for legally sensitive notes (e.g. staff/client case notes.)

Choose between tabbed documents and overlapping windows: Office Button | Access Options | Current Database.

A synchronized Datasheet (for locating the record) and Form view (for editing.)

Design forms and reports while viewing your data.

A Search box in the horizontal scrollbar searches any field in real time, finding matches between keystrokes. No setup: automatically appears in datasheets and forms.

Filter or sort by clicking the column header in a datasheet. No setup.
(Particularly good on date fields, e.g. "last week", "last month".)

In any datasheet, depress Totals on the Records group of Home tab.
At the foot of each column, choose sum, count, average, etc. No setup.

Calendar automatically appears beside date fields/controls. No setup.
(Can be suppressed with Show Date Picker property.)

Set the List Items Edit Form property of the combo box.
Right-click to edit/add to the items to the combo's list.

Shade alternate rows by setting the Alternate Back Color property of the Section. No setup required for Datasheets.

Option to show numbers as #### if column is too narrow (as Excel does.) Configurable: Office Button | Access Options | Current Database | Check for truncated number fields.

In Macro Design, a new column displays the arguments for each action, making it much easier to follow what the macro is doing.

Collection of variables that can be used application-wide and survive code resets. Example: In AutoExec macro, SetTempVar named LicensedTo to "Acme Corporation", and add this text box to each report header:  =[TempVars]![LicensedTo]

The relational structure behind multi-valued fields is not accessible.

Developers will find it harder to handle complex data types. Any generic procedure you write must be capable of handling fields that contain fields. You cannot use a table with a multi-valued field in an IN clause (i.e. in another database.)

Problems with filters - kb 926701

Attachments are complex data, so the comments above apply. Many large attachments may push you towards to 2GB limit.

The events of the report's sections do not fire in this view.

Initial frustration at not knowing where to find things.
Custom ribbons are harder to create than custom toolbars.

Harder to find an object, e.g. you have hundreds of queries.

By default, objects sort by sub-type (e.g. crosstab queries above select queries or action queries.) Configurable.

Once a category is collapsed, the Nav Pane does not remember the most recently used object under each category.

On by default, so users can add, delete, and rename fields in Datasheet view.

Still inadequate for most serious tasks.

Cannot see many rows at once. Shows sorting direction for selected field only.

ACCDBs do not support security, and Access 2007 does not provide the interface to manage security for MDBs.
Use tables attached to a database that has security (SQL Server, MySQL, ...), or attach tables from an MDB and manage them as described in kb 918583, or Security Considerations and Guidance for Access 2007 or What happened to User-Level Security? (In reality, the Access security was never more than a padlock.)

Use attached tables, connected to a database that has replication.

DAPs are no longer supported. Generate your web interface through PHP or ASP. Microsoft's explanation.

Every time you opened your database, Access 2003 tried to convince you that was a bad thing unless it was digitally signed. Access 2007 solves this by introducing Trusted Locations (Office Button | Access Options | Trust Center | Trust Center Settings.)

Developers often coded to disable the mouse wheel so it would not scroll records in Form view. Access 2007 scrolls in Datasheet and Continuous view, but not form view. (Use the Mouse Wheel event if you want the old behavior.)

If you run the Spelling Checker on a rich text field that contains a carriage return, the words are messed up.

In report design view, the Page Setup dialog lets you assign a specific printer to the report. Access 2007 loses this setting, and the report goes to the default printer. (A report assigned to a specific printer in a previous version still works.)

If the source for a form/report is a SQL statement, and you click the Build button beside the RecordSource property, your changes are lost when you return from the query designer (unless you move to another property.)

While a form is being edited, a control's OldValue should indicate the value that will be restored if you undo the edit. The OldValue of a combo bound to a multi-valued field yields the current value, not the old value.

A rich text control in a subform is barely useable. Since the formatting on the ribbon is disabled, and the minibar does not appear, you are limited to keystrokes such as Ctrl+B.

Access 2007 cannot understand some expressions that work in previous versions. For example, this expression in the Control Source of a text box results in #Name:
    =[Form].[RecordsetClone].[RecordCount]
For a workaround, see Avoid #Error in form/report with no records.

There are some problems with the new interface, e.g.:

• View icon (Home tab of ribbon) remains disabled after resetting code.
• Print Preview disappears from ribbon.

If you Shift+Del an object in the Navigation Pane (deletion without confirmation), Access misreads the held-down shift and selects all previous objects. So if you delete the last form, and slip - tapping Del a second time, or not releasing it before the first AutoRepeat -you just deleted ALL forms, without confirmation.

See Errors using multiple versions of Access under Vista.

Add the path where your databases are stored: Office Button | Access Options | Trust Center | Trusted Locations.

Disable for each database: Office Button | Access Options | Current Database | Name AutoCorrect.

Uncheck this option: Office Button | Access Options | Current Database | Enable design changes for tables in Datasheet View.

Important: Do this for every database, including any existing ones that might be used under Access 2007.

Uncheck: Office Button | Access Options | Current Database | Enable Layout View. Alternatively, set AllowLayoutView to No for each form and report.

Note: Apply this setting in each database, even if you had set AllowDesignChanges to No in previous versions.