Access 97 for Access 2 users

486 or higher processor
Microsoft Windows 95 operating system or Windows NT
On Windows 95 a minimum of 12 Mb memory to run applications individually- more to run simultaneously
On Windows NT a minimum of 16Mb to run individually
73-191 Mb hard disk space, 121Mb is a typical installation
VGA or higher resolution screen
Publish to Web feature requires Microsoft Internet Information Server for NT station

Keeping an existing version of Access

To keep Access 2 in addition to Access 97, run the Microsoft Office Upgrade Wizard located in \ValuPack\OffClean\OffCln97.exe. In the Upgrade wizard, select the Let Me Decide which Office Applications will be removed option.

You may find that Access2 can no longer draw graphs or look at existing ones. This is because Access 2 tries to use the wrong version of Graph. This certainly happens when installing on Windows 95 because the graph files are overwritten in the Windows directory. This also has the effect of not being able to convert some of the forms with graphs in (in fact only my bar charts seemed to work, all other graphs had to be recreated)
Access 2 will not be able to produce Mailmerge files by pressing the W icon because it will say you are not able to use the mailmerge wizard (belonging to Access97). To overcome this use File/Export on the table or query to produce a mailmerge file and then enter Word and use Tools/Mailmerge

Default Folders

\Program Files\Microsoft Office is the default root folder for an Access installation. All sorts of folders are opened below this, most notably in Office are the executables, Office\Bitmaps the various bitmaps for different applications, Office\Samples for Northwind and Solutions databases, Templates\Access for HTML templates, Templates\Databases for the complete database wizards. Dynamic libraries end up in WindowsNT\System

Features not installed in typical installation

Click on help
Select wizards- installed in setup to find out what features and wizards are not installed in a typical setup.

Converting Access 2 databases to Access 97

Overview

When you open a database in Access 97 created in an earlier version of Access, you normally want to convert it to Access 97. Although you can enable an earlier version database without converting it, you cannot use Access 97 to change the design of any objects, nor take advantage of new features in Access 97 until you convert it.

Once you convert a database to Access 97, you cannot open it with earlier versions of Access, and you cannot convert it back. You must consider when to convert your database, especially if you share it with users not all able to upgrade to Access 97 at the same time. Always make a backup of your database before you convert it.

Most earlier-version databases convert to Access 97 with no difficulty, but sometimes new features can conflict with existing objects and code in the converted database. This document summarises problems you may encounter when you convert your database. Problems associated with less used facilities are listed at the end of this document. For information about database conversion issues, search the Access 97 Help Index for "converting databases," or use Office Assistant. You can also find information about conversion issues in Acread80.wri and network installation in network8.txt files in the Access program folder.

Conversion Process

Any attempt to open an Access 2 database will lead to being asked if you want to convert the database and the conversion process described below will begin:

print a copy of each object and its definition before conversion as a reminder particularly if one has no access to Access 2 later by using Addins from the File menu and choosing Database Documentor.
If your Access 2 database contains modules you must fully compile your database before you convert it. Open the modules and on the Run menu, click on Compile Loaded Modules (Compile All for version 1).
Make a backup copy and place in a different folder, for example Access2, to distinguish between Access2 and Access97 databases
If you are converting a database with attached (linked) tables make sure that these tables are in the folder referred to by the database you are converting
Close the database before you convert it, and make sure no other user has it open if shared
Start Access 97 and in the Startup dialogue box, click Cancel
Select Database Utilities from the Tools menu and click Convert Database
In the dialogue box, select the database you want to convert and click convert
In the Convert Database Into dialogue box, type a new name (without an mdb extension). To call it the same name it must be placed in a new folder
Click on Save
Select the Modules window
Open a module in design view
Select Compile All Modules from the Debug menu. This compiles all modules in the database, whether or not they are currently loaded. And may find more conversion problems.

Additional steps of recreating the workgroup information file must be taken if the database has been secured (see help "Convert a workgroup information file") otherwise security appears to be lost. Multiple versions of Access can share the same workgroup information file (System.mda or System.mdw) as long as the workgroup information database remains in the lowest version format.

Common Conversion and Installation Problems

General

Database with large number of form, report, and module objects increases in size

Access 97 databases store form, report, and standard module objects differently than earlier versions. When you convert an Access 2.0 database with a large number of these objects, the Access 97 database may increase dramatically in size. (The students database decreases in size from 224 Kb to 204Kb).

To minimise increase in size of a database, Access 97 converts forms or reports without class modules associated with them to lightweight objects.

You can also reduce the size of your database by converting it to an MDE file (a database from which all source code has been removed, and only compiled executable code remains) using a tools utility.

Databases with Large Numbers of Modules

When you convert an Access 2.0 database with a large number of forms, reports, and modules, conversion may fail with one of the following error messages:

Can’t create any more class modules or Out of Memory

Access 97 has a limit of 1024 Visual Basic for Applications modules. The use of lightweight forms and reports reduces the impact of this limitation.

If your database fails to convert because it has too many objects, reduce the number of objects in your database. Consider dividing your application into multiple databases or using library databases to store your code.

Tables

Tables with Large Numbers of Indexes or Relationships

When you convert an Access 2.0 database containing a table with more than 32 indexes and relationships combined, conversion fails with a message indicating that the limit was exceeded (Access 2.0 allows a table to have up to 32 indexes and virtually unlimited number of relationships). Reduce the number of indexes or relationships and convert the database again. Version 3.5 of the Jet engine creates indexes on both sides of relationships between tables for efficiency.

Names

Now is a really good time to check the names of tables and fields since any containing square brackets and backquotes will not convert. It is also very bad practise to have field names containing arithmetic operators and any sort of bracket since they cause enough problems in Access 2 let alone Access97. To find references to the old name select Analyze from the Tools menu and click Documenter. Avoid fieldnames which are used by Access internally such as: AddREf, Date, GetIDSOfNames, GetTYpeINfo, GetTypeInfoCount, Invoke, Name, QueryInterface, Release, AddressOf, Assert, Decimal, DefDec, Enum, Event, Friend, Implements, RaiseEvent, WithEvents. These will cause conflicts or unexpected behaviour when referenced in expressions. The message There were compilation errors during the conversion or enabling of this database will be displayed

Try converting the Northwinds database. It will fail because the Showevent sub procedure in the Utility functions module uses Event as a string variable. Change the name of the string variable to strEvent and try again.
If you have a field called date and try to set its default to date() and then try to look at it in a form or report you are likely to end up with an unexpected undefined field but not necessarily immediately- usually when one has created all the reports and forms and has the application working because it can then be very painful to edit all the forms, queries and reports to change the name! It can be fudged by using now() as the default but then time is included in the value so it is harder to do a query based on a particular date. Similarly the field name Name gives unexpected errors

Datatypes

The counter data type is replaced by the autonumber data type.

Importing, linking and attaching

The format of Access 2 and 97 databases and objects are different.

You can import and link Access 2 objects into Access 97 database, but not link or import Access 97 objects into an Access 2 database (results in a message stating that the database is corrupt or not an Access database).

You can export an Access 97 table into an Access 2 database (and vice versa) but fields with the new hyperlink data type will be converted to a memo data type, lookup property settings will be lost, and autonumbers will be set to counter (which can only be incremental- so the new random and replicationid facilities are lost).

Remember that if you are using ftp to get comma-separated text you will need to transfer it as ASCII rather than binary to get the correct linefeeds otherwise it will not load into Access97, Another work-around is to read the data into a Word file, and write it out again as Text with Paragraph Markers. Then it will input OK.

Note that importing from Excel files can have problems: if a field is longer than the field it is being imported into then it will truncate without warning.

Note also that if a record will not import properly you no longer get an Error table identifying which records are incorrect- the record will be loaded with the offending cells left blank. The warning message gives no clue as to which records are faulty, so if your import file contains missing data anyway it is impossible to distinguish the incorrect files.

Since the data files are text files, the file must have filetype csv (comma delimited), asc (ASCII) or txt (plain text file- could be fixed length or delimited) for the wizard to make assumptions about the format. Giving it the filetype dat will not show the name of the data file unless the registry is edited (look for importfilter) or the set up program is run:

Select Add/remove
Select data access
Change option

To import the monthly finance data, the import wizard correctly surmised that the file was fixed width but since it looked for spaces between fields got the specification wrong.

Select advanced
Select specs
Choose the appropriate specification. The specification will be shown visually as a check.

In Access 2.0, the TransferText Command, when embedded in a macro, does not require a Specification to be set. When the database was converted, all attempts to run the import macro failed (the message given re-the failure was not particularly helpful), although one could import the file manually using the Get External Data | Import options.

Two changes were required to get TransferText command to work.

1) The file from which the data is imported needs to be one of the predefined types as described above.

2) A specification must be created for the import file. This involves a dry run of the File | Get External Data, Import option. The documentation refers to the Advanced Wizard. This is confusing as the Advanced button occurs on two successive screens and it is the second of these which is required. Having selected the file for import on the

first import screen, and clicked the Advanced button from the second import screen, a specification is created for the import based on the selected file.

Note- The specification refers to the fields as Field1, Field2,... When the import occurs Access tries to match the fields with the fields in the destination table which obviously have more meaningful names! Before saving the import specification, the field names should be reset to the actual names in the destination table. Finally the specification can be saved. Once the specification has been created, it can be selected for the TransferText command in the macro.

The other problem is that indexes are ignored if TransferText is used rather than Import.

Queries

SQL button

The SQL button has vanished so one has to select SQL from the View menu

Queries with Date Criteria Between 1900 and 1929

If you use a literal date specifying the years 1900 through 1929 as part of your criteria in a Access 2.0 query, the query may return different results after you convert the database to Access 97. This happens because new interpretation of year dates ending in the digits 00 through 29 places them in the next century.

You may even see different results from the same query if it is run on an enabled database.

For example, a literal date criteria of #01/01/15# in Access 2.0 represents January 1, 1915; the same date criteria in Access 97 represents January 1, 2015.

Create a new query
Add the students table
Select dob
Give the criteria as >1/1/15
Watch it turn to #1/1/2015# in front of your eyes

To work around this difference, modify the criteria to specify the correct century, for example #01/01/1915#.

One could also use the cdate function to force conversion to a date

Give the criteria for dob as >cdate(1/1/15)
Add an extra column cdate(dob) to show what the date looks like.

Cdate will show the current century

It is recommended to always show the 4-digit year by changing date format in Regional Settings from the Control Panel.

Forms

Datasheet View

The datasheet view button has vanished- only obtainable from the view menu

Control Names

A control name differing only by a space or symbol is not allowed. Thus if one has a control called [last_name], then you can not have a control called [last name] or [last+name]

Combo Boxes

Access 97 combo boxes now always accept null values when LimitToList is set to true whether or not the list contains null values.

Open the student form
Try giving a blank subject to a student
Set the Required property to yes to make sure null values are not accepted

Access 2 would have given the error message The text you enter must match a value in the list

Wizard-Generated Code

Access Basic code in your database generated by a Wizard may need to be modified when you convert your database, specifically those calling another application. The Access 2.0 Command Button Wizard generates code for buttons shelling to Microsoft Office applications that does not work in Access 97. Remove these buttons in your converted database and recreate them using the Access 97 Command Button Wizard. The code may reference functions in Utility.mda which are not in Wzmain80.mde library databases.

Command Buttons

Some of my buttons had to be made bigger since the spacing was changed so the button was not large enough to show the text. This was confusing since the font was still System 10 so should have stayed the same. The default font for new buttons is now Haettenschweiler 12

Some of the button pictures have changed, so if for example if you want to create an exit button it will no longer have the closing door icon but a stop symbol. This could make applications look inconsistent if new forms are added later. Existing button images do not change because they are stored in the Picture property of the control.

Open the student form in design view
Use the wizard from the toolbox to create a button to close a form. The image will be a big red stop sign
Look at the picture properties of the button by clicking on the three dots by the side of Bitmap
Select the Exit picture from the picture builder. The image will change to the familiar half closed door

Field widths

I had to make some of my fields bigger since the format changed from Sans Serif 8 to Ariel 8.

Select Options from the Tools menu
Select Datasheet and look at the font setting and colours etc

Hidden columns

An extra step has to be taken to hide columns in a form.

Open up a form in design view and make a field invisible by changing its visible property to no
Look at the form and see that the field is still visible
Select Hide Columns from the Format menu. Note that one can only hide columns when opening the form and not in design view. One can also set ColumnHidden property in a macro

Graphs

I had to recreate many graphs especially those that were not bar charts, and ones that were linked to each data record so it is a good idea to print a copy of each type of graph and its definition before conversion as a reminder particularly if one has no access to Access 2 later.

Form and Report Templates

To use a template from Access97, the form or report must be converted to Access97 format. To specify a template for forms and reports (specifies which sections to have, dimensions, default property settings, does not create any controls):

Select Options from the Tools menu
Click on Forms/Reports tab
Type the name of the template in the Form Template or Report Template box (default normal)

Reports

Forms or Reports with Large Numbers of Controls

When you convert an Access 2.0 database that contains a form or report with a large number of controls, the conversion may fail with the following error message:

The form or report ‘ObjectName’ has too many controls.

You must reduce the number of controls on the form or report and then convert the database again. Visual Basic for Applications, imposes the limit on the total number of controls, fields and sections on a form or report to 754. This limit does not exist in Access 2.0 .

16- and 32-bit ActiveX Controls (OLE Custom Controls)

When you convert a database that uses 16-bit ActiveX controls (formerly known as OLE controls) eg Word, Excel, Graph, Paintbrush designed for Access 2.0, Access 97 does not convert them to their new 32-bit equivalents. You must manually insert an equivalent 32-bit ActiveX control and recreate its event procedures after you convert the database.

Create a new faculty form with just the faculty name and faculty code
From the Insert menu select Active X control
Select Calendar Control 8.0
Set Control source of the properties to RegDate. The date will now be displayed as a calendar

Conversion Fails Without Default Printer

If you attempt to convert a Access 2.0 database that contains form or report objects, and your computer does not have a default printer installed, you receive the following error message, and the database fails to convert:

You must set a default printer before you design, print, or preview.

To resolve the error, set up a default printer on your computer and convert the database again.

Report Margins Adjust to Printable Region

If you have a report in an Access 2.0 database with top, bottom, left, or right margins set to zero inches, after you convert the database, you may see the margins changed to the minimum default margin defined by your printer driver resulting in split pages. Reduce the width of the report to avoid it splitting.

Select Options from the Tools menu
Select general to see the print margins
Open Word
Create a new document
Select Page Setup from the File menu and look at the printer setup to see the margins for your printer
Change the print margins for Access

Saving Report as RTF file

When one saves a report in RTF format (using Officelinks icon or save as from the File menu) so that it can be opened in Word, the appearance in Word vastly different from the Access printout due to the fact that a different algorithm is used to calculate the row height and field length in Print Preview and Output. Thus, a times roman 10 field that was specified 1.0083" long and .375" from top of page in the report design and with a vertical space of .192" from the next field will in an rtf file from Access 2 be printed as 1 line .935" long and 1 blank line. In Access97 rtf, part will be printed .5625" on the first line, and the remainder on the next line.

Open subject listings in print preview mode. The course name is not truncated
Press the Word icon
Note that the course name is truncated
Change the design of the form to stretch and look at the report. The course name is on one line
Press the Word icon and course name now appears on two separate lines
Change the design of the form to clip and set the properties of coursename to be able to shrink and grow. The course name will now appear on two separate lines
Press the Word icon and the course name is over two lines so looks similar but still not as nice as coursename on one line

If one uses "format to fit" in the vain hope that the problem will cured, the remainder will be truncated. Rules for "successful" rtf documents:

make sure that the vertical distance between controls should be in multiples of .1667";
canshrink and cangrow must be set to no else too many blank lines will be generated
use align from the format menu for correct alignment
avoid overlapping controls

The only solution to get the field printing without being split over 2 lines was to increase the size of the field but it is difficult to know what to set it except by trial and error and it is not always practical to increase the width anyway. The other solution is to not output to Word!

Northwind Reports need Arial Narrow font

When the Catalog, Invoice and Sales By Category reports are previewd or printed, some of the text will be truncated if Arial Narrow font is not installed. This can be installed from the Office 97 ValuPack

ForceNewPage Property in Group Section

In Access 2.0, if you set the ForceNewPage property to Before Section in a Group Header section on a report, the setting does not force a page break before the first group section, whereas in Access 97 it forces a page break before every section, including the first group section encountered in the report.

Modules

Visual Basic for Applications replaces Access Basic. In most respects it is identical and most of the necessary conversions are made automatically. Some of the changes you need to be aware of are listed below, others are outlined at the end of this document.

Conflicts with Reserved Words

In Access 97, module names cannot be any of the following reserved words:

Application

Assistant

CommandBars

DoCmd

Forms

Modules

References

Reports

Screen, DAO, Access, VBA

Similarly, if you have set a reference to a type library such as Excel, a module can not be called Excel.

An attempt to convert an Access 2.0 database containing a module with one of these names, may cause conversion to fail with a message indicating an invalidmodule name. To correct the error, rename the module and convert again. If the conversion does not fail. the original name will have an underscore appended to it., e.g. Forms will have the name Forms_ so if called by another object, then that object will have to be altered to reflect the new name. To find references to the old name select Analyze from the Tools menu and click Documenter

Standard /Class Module Name Conflicts

In Access 97, form and report (class) modules share the same name space with standard code modules which are available to other procedures. For example, for a form named Form1, Visual Basic for Applications creates a class module Form_Form1, which means that a standard code module can not be named Form_Form1. Similarly avoid Report_ prefix for report modules.

An attempt to convert an Access 2.0 database with a standard code module name that conflicts with a class module name, causes conversion to fail with a message indicating that the module name is invalid. To correct the error, rename the standard module and convert the database again. It is a good idea to always prefix a procedure by its module name.

Module /Procedure Name Conflicts

If the Access 2.0 database contains a procedure and a module with the same name, the procedure fails in Access 97 with the following error:

Expected variable or procedure, not module

Resolve this error either by changing the name of the module so it does not conflict with any procedure names, or change the converted database to call the procedure using ModuleName.ProcedureName syntax.

International dates

Visual Basic treats dates inconsistently, ignoring the regional settings if a literal date makes sense in American format. For example, with a British date setting (dd/mm/yy) in Control Panel, enter these two calculations in the debug window:

? #23/12/96# - #20/12/96#

? #13/12/96# - #10/12/96#

The first yields 3 days, but the second 62! Visual Basic interprets #13/12/96# as 13-Dec-96, but #10/12/96# as 12-Oct-96. Access Basic (ver. 1, 2) always respected the Control Panel setting and did not have this inconsistency. The solution is to state the month explicitly eg #13 dec 96#. This problem only applies when using Visual Basic not to a query.

Web Browser

If Microsoft Internet Explorer is removed, and Netscape Navigator installed, then the message:

Unable to open file. No program registered to open this file

Run Windows Explorer,
select an HTML file
right click on it to show its properties
click on the Open With command to select Netscape. Select Always Use this Program to open this type of file

Connection Timeouts

If you are using ODBC to link tables, Access tends to fall over since it closes connections to the external data after 10 minutes even if the datasheets and forms that display remote data are still open. It then tries to automatically reconnect, but forgets the password. General protection faults and strange messages can then occur.

Run regedit from the start button and perform a find for timeout
alter the registry setting of mycomputer\hkey_local_machine\software\microsoft\jet\3.5\Engines\ODBC\ConnectionTimeout to 6000 seconds from 600 seconds (choose the decimal setting)

Network Installation

Make sure you have your own copies of the wizards and system file:

System.mdw (holds information about security, last databases accessed)
Utility.mda (special library database holds tools addins and code no longer held in wizards. If set up wrongly results in error message Microsoft Access Utility Add-in missing for example when trying to zoom a control field)
Wizard.slg
Wzcnf80.mda (synchronisation conflict resolver add-in)
Wzdat.mdt
Wzlib80.mde
Wzmain80.mde (library database)
Wztool80.mde

Copy them in an Access work directory and make sure the registry points to them rather than the network files.

What is confusing in Access 97 for the Access 2 user

It can be difficult to find your way around since so many menus have been changed, and icons removed and added. An appendix at the end lists the menu changes.

The archive-query wizard is no longer available.

Attaching tables now known as linking tables

The graph wizard is now known as the chart wizard

The groups/Totals wizard is now the Report wizard

The Word Mailmerge wizard is now found using Office Links

The options for creating single column or tabular forms and reports are now incorporated in the Form and Report wizard, instead of having separate wizards. There is more emphasis on autoreports and forms.

If you enter 1/12/ into a date field, an error message is now returned. Use 1/12 instead and the current year will be entered by default. (At least 2 values for a date field must be given- thus 3/98 will be entered as 1 March 1998 providing the date f ormat is set up correctly from the control panel)

Filters are totally different. The former method of using filters is to be found in Records/Advanced Filters and can now be used on tables and queries as well as forms. The last filter used is saved with the object it was last used with, so one is ask ed if one wants to save a change to the object each time a different filter is used- which is very disconcerting when one is not ware of changing an object

When one wants to create a new object, one is no longer offered the choice of selecting a wizard or not, the wizards are all shown and one has to determine what to select to create an object from scratch.

When creating a new table one is offered the choice of Design view, table wizard, datasheet view, link table, Import table. Datasheet view is very misleading- use Design View

Query wizards have been extended to include a simple query wizard so one has to select Design view to choose the familiar way.

The master subform wizard has been removed so it is less intuitive to know how to create one (use subform from the toolbox- this can now create a new form based on a table or query)

When the form and report wizard ask for a title, it is actually asking for the name of the form or report, so if a report exists with that name you are asked if you want to overwrite it. This has the repercussion that if one wants two reports with the same heading one has to change the heading later. Forms now no longer get a heading so one would have to add one later

When you rename a database, compiled code is decompiled. To recompile and save all modules in compiled state:

open the database

open a module in design view

click on Compile and Save All Modules

OLE (Object linking and embedding) controls used to display separate files or run applications from Access now called ActiveX controls. The OLE server or object application (Word, Paintbrush, Excel etc) is known as the ActiveX component. The help files contain a glorious mixture of the two terminologies.

Look at help about ActiveX components

Look at Help for converting database objects

What is new in Access 97 for the Access 2 user

Introduction

Access for Windows 95 introduced Table and Performance Analyzers and Replication. Access 97 introduces web facilities, easier filters, more wizards, drag and drop facilities. Performance should be better (provided there is enough RAM) due to being a 32-bit application. Start-up options are improved. Record locking is still performed by 2K page but multiple users can add new records at once. Passwords are now displayed as stars.

Office Assistant

Office Assistant is an animated character that allows a user to access help easily and to receive tips and suggestions about using Access 97. It allows you to type keywords for your search and will respond with the topics covering those topics. It can get annoying. If you close the Assistant you can open it again by clicking the question mark icon at the right of the toolbar. Every now and then a light bulb will flash to impart a good tip.

Click on Options to change the assistant or to check the setup of the assistant.
Click on the question mark icon
Click on the Assistant title bar
Type 'when are tips shown'

Relevant topics about tips are shown that you can select by pressing the blue button by the side of each topic

Click on Reports and Design and you will see relevant topics displayed about design
Click on Tip of the day and you will see numerous tips

Context sensitive help is still available with the F1 key, but the question mark that used to show screen tips in Access 2 is now found on the top right corner of dialog boxes. Press this question mark and then click the item to get a brief screen tip.

New Help menu commands

Select Contents and Index to display the Access Help.
Select What’s This? To click any area of the screen (such as a toolbar or menu command) to see a brief explanation in a ScreenTip.
Select Microsoft on the Web to connect to the Microsoft web home pages to get updates, free software etc

Database Wizard

The Database Wizard in Access 97 includes more than 20 customisable templates (with filetype mdz) to create full-featured applications with a few mouse clicks. There are templates for addressbook; asset tracking; book collection; contact management; donations; event management; expenses; household inventory; inventory control; ledger; membership; music collection; order entry; picture library, recipes; resource scheduling; students and classes; time and billing; video collection; wine list; workout. Users can choose what information they want the application to track, and the wizard will build custom applications, creating all the tables, queries, forms, reports and modules needed.

Click New database from the file menu
Click on the Databases tab
Double click a database

This may sound a good idea, but the applications are often far more complicated than needed (but pretty!), and extensive modifications may be needed. For example, addresses have zip codes; telephones have masks that are not suitable. If data is already available it may not be possible to import it because the database structure is far more complicated than one's own data structure. One can also spend ages looking at each object and trying to figure out what it is for, or why certain things magically happen. It is easier to start from scratch

Spell Check Wizard

Ensure the data stored in Access tables is spelled correctly by using the Spell Check Wizard. The spelling dictionary is shared across all Microsoft Office applications so that user additions need only be defined once, regardless of which Microsoft Office application is being used.

Open the faculties table
Select the RegPlace column
Click on the ABC button to spell check, or select Spelling from the Tools menu

AutoCorrect

Use AutoCorrect to automatically correct text that you frequently mistype, and to automatically replace abbreviations with long names. For example, you can create an entry "UOB" for "University of Bristol". Whenever you type UOB followed by a space or punctuation mark, Access automatically replaces "UOB" with University of Bristol. AutoCorrect definitions are shared with the other Microsoft Office applications so pairs need only be defined once.

On the Tools menu, click AutoCorrect
Select the Replace Text As You Type check box.
In the Replace box, type the text that you want corrected automatically i.e. UOB.
In the With box, type the corrected text University of Bristol.
Click Add.
Try it out by opening a form or a table

AutoCorrect will not correct text that was typed before you selected the Replace Text As You Type check box.

If you actually want "UOB" to appear, clear the Replace Text As You Type box, type UOB in a field and save the field by moving to another record. Then select the Replace Text As You Type box again.

Drag and Drop

Select Tile horizontally from the Windows menu
make the forms window visible showing the forms available
Click on macros and select new macro
Drag and drop a form name into the new macro. All the correct actions and parameters will be filled in automatically to open that form

Moving information to other Microsoft Office applications is now as easy. Copy and Paste may be used from the Edit menu

Open Word
Minimise Access to see both Access and Word (by pressing the double rectangles top right corner
Drag and drop an Access table into Microsoft Word. In Word, the table is created and the formatting is preserved.
Close Word

Open an Excel spreadsheet
Highlight a range of cells and drag from Excel into Access
Close Excel
Open Word and create a new document
Close Word
Open Explorer and copy the document
Open a form in design mode
Select Paste Special from the Edit menu and choose Paste Link (so that the current version of the word document is always shown) (creates a text field with OLE class Microsoft Word 97)
Select Paste as Hyperlink from the Edit menu (creates a label field)
Select Microsoft Word Document from the Insert menu (creates a text field with OLE class Microsoft Word)- this is equivalent to the Paste Special method
Select Hyperlink from the Insert menu and type http://www.bristol.ac.uk in the link to file or URL field
Look at the form

Right Mouse

Clicking on the right mouse button displays a menu of the most commonly needed options for the item clicked on such as copying, pasting, sorting, filtering, properties. It can be used in design or display view. When you can not see a menu for what you want to do, it is worth right-clicking.

Open a form
Right click a field
Right click a background
Open a form in design view
Right click a field

View menu

The View button on the toolbar makes it easier to find out more about database objects, and switch to other views of a database object

Zoom

If you are entering an expression that is longer than the standard input area in a property sheet, a design grid, or a macro action argument, you can type the expression in the Zoom box. To open the Zoom box, press SHIFT+F2 when the focus is where you want to enter the expression.

Compacting and Repairing

You can now use the compact and repair database commands (from the Tools/Database Utilities menu) on the currently open database

Desktop Shortcut

Create a shortcut to individual database objects by dragging and dropping, or by right clicking an object. Double click on the shortcut to open the database and object. The icon will not be the Access icon but the object icon

Tables

Datatypes

Counter replaced by Autonumber- can be Long Integer (as in Access 2), replicationID, sequential or random (fora random increment.

Date representation- new interpretation assumes that dates entered in 2 digit format in range 00-29 indicate 21st century

New hyperlink data type to hold web address

Lookup Wizard

Used to create a field that looks up data from another table in Design view. Useful for building lookup properties for foreign key fields

Open the Students table in Design view.
In the Faculty Data Type column, click the arrow and select Lookup Wizard.
Click the option that indicates you want the Lookup field to look up the values in a table or query.
Click Next and choose the faculties table. Note that it will not let you choose the same table or query based on the same table
Click next and select the faculty field as a lookup column
follow the directions in the remaining Lookup Wizard dialog boxes.

When you click the Finish button, Access creates the Lookup field and sets certain field properties based on the choices you made in the wizard. For information on the properties that the Lookup Wizard sets:

select look up wizard from the help index
select create a field that looks up or lists values
click on properties information arrows in section 5

Once you have created a Lookup list field, if you add the field to a form, Microsoft Access copies its definition into the form so combo or list boxes will be created automatically.

Open the students table and put the cursor against the faculty field of a student. Note that a lookup box now appears

Look at the design of the Student table again and note that the data type shows as text
Select the lookup tab and see the definition of the combo box

If you change the definition of a Lookup or value list field in the table after adding it to a form, those changes will not be reflected in that form. To correct this, delete the field from the form and then add it again.

Lookup Display Control

Fields have a property (shown on the lookup tab) to define the default control type (text box, list box or combo box), used in datasheets and new forms. It is also possible to add a Lookup field to a table that displays values from the same table that contains the Lookup field (the lookup wizard does not allow this).

Open the Students table in Design view.
Change the lookup tab to combo box for the lastname field
Type select distinct lastname from students order by lastname in the row source. This will list the existing surnames in alphabetical order without duplication

Sort Order

Tables have a property that specifies the default sort order to use when the table is browsed in a form or datasheet.

Relationships Window

Right click Design on any table in the relationships window to modify the structure of the tables without leaving the window
Drag fields from either side of a one to many relationship and Access will get the join the right way round.

Table Analyzer Wizard

The Table Analyzer Wizard can decipher flat-file data from a wide variety of data formats and create a relational database that stores the original data but eliminates the problems associated with nonrelational data. It automates the "normalisation" process and makes a recommendation for splitting the original table into relational tables that users can accept or customise. It describes problems such as mis-spelled names and wasted space due to repeated information

Select Analyzer from the Tools menu
Select Table
Demonstration screens appear to show what the problem is and how to solve it, click next until the wizard asks which table is to be analysed.
Click on Subjects
Click to let the wizard decide

The wizard recommends not splitting the table

Import Table wizard

The new wizard analyzes the target data and allows the user to decide what and how the data should be imported into the database. It determines what format the data is in (but does ask for confirmation).

Access 97 does not ask if the fieldnames are the first line of the file for Word for Windows mail merge data files because the first row must contain the field names.

When you export an Access table or select query to a delimited or fixed-width text file, Microsoft Access inserts the field names of your table or select query into the first row of the text file if you have selected Yes for this argument.

Select Get External Data from the File menu
Select Text files as the file type
Select the file faculty.txt and click on Import
Choose Delimited as the format- guessed correctly
Make sure First Row Contains field names is not selected, and accept comma delimiter
Append into the Faculties table

Intelligent? Table Design

Access now allows users to define new columns or delete old columns while working with their data in datasheet view. For new tables defined this way, appropriate datatypes are determined based on the information that is entered into each column. In addition, Access will add indexes automatically where appropriate.

Open the students table
Click in the column next to where you want to add the new column
Select column from the insert menu
A new column called field1 appears to the left of the selected field
Type a value in the field
Check the design- the correct datatype will have been set hopefully, but note that field length will be set to 50 if it is a text field

In practise, I think that if you know you are adding a new column, then you should go into design and do it yourself to enable correct field lengths to be set, and the correct fieldname

Application Splitter Wizard

The Application Splitter Wizard separates an Access application from its tables and creates a shared database containing the tables for a multi-user application. The data is then shared, and each user can set up their own applications for that data. This also avoids network traffic.

Click on Tools
Select Addins
Select Database Splitter
Follow the instructions in the dialogue boxes

Incidentally, for a shared database to be updatable, its properties should be set to Archive

Background Joins

Background Joins enable users to simply select the fields they want on their forms, queries and reports. Even if relationships have not been created between tables, Access automatically creates joins if you add two tables to a query and the tables each have a field with the same or compatible data type and if one of the join fields is a primary key. Other tables may be included automatically, if they are needed to relate the desired data. You can enable or disable these automatic joins.

On the Tools menu, click Options.
Click the Tables/Queries tab.
Select the Enable AutoJoin check box.

(If automatic joins disabled, you can still create joins yourself. This setting applies to new queries only.)

Make a copy of the faculties table
Create a new query with the new faculties table and the original faculties table.

A relationship is automatically drawn between the 2 tables

Filters

Access 97 makes it easier to find information by using a form or datasheet to formulate the filter visually. All forms now have a Filter by Form mode wherein users can type the information they seek; Access builds the underlying query to deliver the data, in a form view. This means that Access filters can now be used from tables, queries, forms and subforms whereas before they could only be used from forms. The filter is saved when the form, query or table is closed, so one can open that object again and apply (the third filter icon) the last filter used -which is why it asks if the last changes are to be saved when one has not apparently made any changes to the design.

The ApplyFilter and Filter event have been added to forms and reports

Filter by Form

Access 97 makes it easier for users to find information by using a form or datasheet to formulate the filter visually with drop down boxes

Open a table or form
Click on the filter icon with the form (or select Filter by form from the Records/Filter menu)
Click on any field, for example the sex field

A drop down box appears

Click on the drop down box and the values for sex are shown.
Select a value and press the Apply filter icon

Filter by Selection

Access for Windows 97 allows users to locate information quickly on forms or datasheets by highlighting a selection and filtering the underlying data based on that selection, instead of having to develop a query to execute the task.

Open a table or form
Click on a field, for example the subject field
Click on the filter icon with the streak of lightening (or select Filter by selection from the Records/Filter menu)

Only the records with that value for that field are shown

Filter by Exclusion

Open a table or form
Click on a field, for example the subject field
select Filter excluding selection from the Records/Filter menu

Only the records without that value for that field are shown

Advanced Filter Method

The original way of filtering from a form is now classified as an advanced filter. It does however have the advantages of being able to sort the results at the same time; is easier when setting more than one criteria, or complicated criteria and a familiar way of performing queries.

Open a table or form
select Advanced Filter/Sort from the Records/Filter menu)

Only the records with that value for that field are shown

Queries

Design Grid

The design grid now shows tablename which saves confusion about which table a field is coming from

Simple Query Wizard

Similar to Form Wizard. Allows users to locate desired information. Users choose desired data from a drop-down box and the wizard handles the inclusion of related tables if necessary.

Click on Queries
Click on New
Select Simple Query wizard
Select table Subjects
Select all fields by using the >> button
Select table Students
Select all fields by using the >> button
Deselect Students.Subject by pressing <
Press Next
Choose Detail to show all student records together with the details of the subject they are studying, or choose summary to group the records- note that if summary is chosen then the wizard will fall over since too many fields have been chosen. The summary wizard is very temperamental so it is better to use View/Totals from query design.
Press Next
You are asked for a title, by default Student Query- this is actually the name of the query

You can now sort your query results quickly by right clicking the field and selecting sort or using the toolbar sort buttons. This was previously only available for tables

Nz Function

This can be used as an alternative to the isnull function

Open the student table
Blank out the sex of a student
Create a new query

Type as a field expression IIf(IsNull(sex), "Sex not known", sex)
Type as another field expression Nz(sex, "Sex not known")

Other new functions are: Array, Cbool, Cbyte, Cdate, ChrB, CVErr, FileDateTime, FileLen, GetAllSettings, GetAttr, GetSetting, GUIDFromString, HyperlinkPart, InputB, IsArray, IsError, IsMissing, IsObject, LeftB, LoadPicture, MidB, RightB, StringFromGUID, TypeName

Forms

Look at help for Form Design, whats new, to find new events, properties etc

Form and Report Wizards

The Form Wizard and Report Wizard have been improved to allow users greater flexibility in creating the exact view required, regardless of underlying tables or queries. After selecting the desired data for the form or report, users are presented with different layout choices based on the data selected. For example, if a user chooses data on both sides of a one-to-many relationship, the Form Wizard now offers to build a main form or subform. The main-subform wizard option has now vanished, and instead there is a bigger choice of autoforms and reports. 9 styles are now available. In practise I do not think many forms with clouds will be chosen!

On the Insert Menu: AutoForm will automatically create a form for the table or query selected in the Database window; AutoReport will automatically create a report for the table or query selected in the Database window.

Chart Wizard

There are now 20 chart styles (Access 2 has 12). New styles include cone bar charts, 3-D cylinder bar charts and bubble charts (variable sized bubbles). I found it a lot less intuitive to change the design of a graph. For example to add titles one had to go to Chart options and select options on the Data Labels and Titles tabs rather use Insert.

One can add a chart to a form by selecting Chart from the Insert menu. The graph icon is no longer on the toolbox.

New Navigation button

To the right of the navigation bar is an extra button identified by a star which can be pressed to create a new record

Drag and Drop

Open a form in design view
Move the form design window so that you can see the main database window
Place the mouse on the name of a form and drag it onto the open form
Place the mouse on the name of a table and drag it onto the open form. The subform wizard will appear.

Lookup fields

In the interests of performance, data in a lookup field, list box or combo box that comes from another table does not automatically refresh when the other table is updated. You can refresh the data manually by pressing F9.

Form Properties

AllowFilters, AllowEdits, AllowAdditions, AllowDeletions are yes/no values and replace properties for Default Editing, Allow Editing, Allow Updating which each had various options. New data properties Filter, Order by have been added. New events properties Filter and ApplyFilter have been added.

Colour palette icon replaced with toolbar icons Fill/Back colour, Font/Fore colour, Line/border colour.

The SpecialEffect property now includes etched, shadowed or chiseled in addition to Nornal, Raised and Sunken.

Shortcut keys for data entry

Many more short cut keys are available for example CTRL+SEMICOLON to enter current date

Look at shortcut keys on the Help index

Toolbox

Open a form in design mode and display the toolbox. There is an extra toolbox icon which shows more toolbox controls half of which are not usable unless other software is installed.

Picture Backgrounds

One can add picture backgrounds to forms and reports

Open a form in design mode
Show the properties
Click on the format tab and scroll down to picture
Click on the three dots and find a picture
Open the form in form view and admire the effect

Cycled Properties

Double-clicking on property definitions in the Tabbed Property dialog box will cycle through the available options.

double-click successively on the LimitToList property of a combo box to toggle the value between yes and no.

Changing Control Types

Can change control types on their forms and reports "on the fly" without rebuilding the control from scratch.

Open a student form in design mode
right-click the sex text box
select change to
change the text box to a combo box (note that a combo box can only be changed to a list box and vice versa)

Can also specify the control type of a field in table design

Open students table in design mode
Select the subject field
Select the lookup tab at the bottom and click on it
Click on the display type field and a down arrow appears. One can choose the display type to be text box, combo box or list box

Multiselect List Boxes

Native list-box controls can be selected either singly or multiply. When a user closes a main form that displays multiple instances of a second form, you may want the user to be able to choose instances of the second form to keep open. For example, when the CustomerPhoneList form closes, the CustomersDialog form displays a list box showing the customer records currently displayed in instances of the Customers form. The user can select one or more customer records to keep open. To view this form, open the Developer Solutions sample application (which will need to be converted!). In the opening form, click the entry in the upper list box that reads: Get more mileage from combo boxes, list boxes, subforms, and subreports. In the lower list box, click the entry that reads: Create a list box that allows you to select and manipulate multiple objects. The Customers Dialog form uses event procedures. You can copy these event procedures to your database and customize them to fit your needs. Problem is that it falls over with compilation errors because a utility add-in has not been added.

ControlTips

Can add own ControlTips to controls on forms to show the purpose of the control. When the user hovers the cursor over a control with a custom ControlTip defined, the text is displayed in a small text box beneath the control. Look at the help under controls, help tips.

Open the Student form in design mode
On the View menu, point to Toolbars, and then click Customize.
Show the toolbar you want to work with, if it is not already displayed.
With the Customize dialog box open, right-click the last name search button you want to specify a tip for, and then click Properties on the shortcut menu.
In the ToolTip property box, type the tip that you want to display.
Open the form and pass the mouse over the lastname search button and watch the tool tip

Notes

If you do not specify a custom ToolTip, Access displays the text specified in the Caption property.
If the ToolTip does not appear when the mouse pointer rests over the button or combo box, verify that you have selected the Show ScreenTips On Toolbars box on the Options tab (Customize dialog box).

Format Painter

Like other Office applications, Access has the Format Painter (brush icon) feature. The Format Painter is used on forms and reports to copy formatting from one control to others.

To copy formatting characteristics from one control to another:

Open a form in Design view or a report in Design view.
Select a field and change its colour
Press the format painter. A paintbrush pointer appears
Select another field (You can click the same type of control, or you can select another type of control. For example, you can copy formatting characteristics from a text box to a list box) and the latest field will achieve the same colour. Double clicking the paintbrush holds it down and enables several fields to be changed (press esc to stop using it).

Access copies any of the following properties: SpecialEffect, BorderStyle, BorderColor, BorderWidth, BackColor, BackStyle, LineSlant, Visible, DisplayWhen, FontName, FontSize, FontWeight, ForeColor, FontItalic, FontUnderline, LabelAlign, TextAlign, LabelX, and LabelY.

You can not copy properties to or from ActiveX Controls.

AutoFormat

The AutoFormat feature is used to choose from a list of approximately 30 predefined styles to format forms and reports, as with the AutoFormat feature in the other Microsoft Office applications. If users define a form or report style they like, the style can be saved as a custom style and applied to new or existing objects within the database.

Format a form, report, or control using predefined formats

Open a form in Design view or a report in Design view.
Do one of the following:

If you want to format the entire form or report, click the form selector or the report selector (top left square of the form or report).
If you want to format an individual section, click the section selector.
If you want to format one or more controls, select the controls.

Click AutoFormat (the icon with the screen with the wand by it) on the toolbar, or select Autoformat from the format menu.
Click one of the formats in the list.
If you want to specify which attributes to apply (font, colour, or border), click Options.

Note

You must select the entire form or report to apply a background picture.

Calendar Control (example of an ActiveX control)

A 32-bit Calendar Control can be used to select dates quickly, without any code. The user simply drops this control onto a form for an easy way to select dates. Changes made to data using the calendar are then stored in the underlying table.

Create a form for faculties with at least one date field
From the Insert menu select ActiveX control
Select Calendar Control 8
Look at the properties and change the control source to RegDate
Look at the Form. A calendar for the month appears for Regdate

Custom Shortcut Menus

Developers and users can add their own right-click shortcut menus to controls on forms.

On the View menu, point to Toolbars
click Customize.
On the Toolbars tab, click New.
In the Toolbar Name box, type the name you want, and then click OK.
On the Toolbars tab, click Properties.
In the Type list, click Popup.
Set the AllowCustomizing property the way you want, and then click Close.

Access adds the shortcut menu to the Shortcut Menus toolbar, from which you customise all shortcut menus.

In the Toolbars box on the Toolbars tab, click Shortcut Menus.
On the Shortcut Menus toolbar, click the Custom category.
Add commands from the Customize dialog box.
Move or copy commands from other menus.

Page number and Date/Time controls

It is now easier to add controls to display page numbers and dates since location and format are asked for

Pen a form in design view
Using the Insert menu select date and time and note the different options
Using the Insert menu select page numbers and note the different options

You will probably have to change the page/ date control colour since it seems to take characteristics of the text fields rather than the header fields

Image Control

An image control provides a simple way to include graphical information on forms or reports and improves the display performance of the image. Previously, images were displayed using OLE, which included unnecessary steps. When you insert clip art, you can convert the image to drawing objects and then use options on the Drawing toolbar to edit the objects or example, to change the fill or line colours, rearrange parts, or combine images.

Open the Student Form in design view
Have a look at the properties of the University badge. It is currently an unbound object.
Select Change To from the format menu.
Click Image. This converts an unbound object object frame to a bitmap image. This image can no longer be edited. The properties of the badge are now changed
Show the tool box. Note that there is a new tool depicted as a mountain with a sun, in addition to the bound and unbound object frame tools
Add a picture from the clipart gallery. When you select a picture, the Picture toolbar appears with tools you can use to crop the picture, add a border to it, or adjust its brightness and contrast. If the Picture toolbar does not appear, right-click the picture.

PivotTable Wizard

A pivottable is like a crosstab query but you can switch the row and column headings dynamically to see a different view of the data. This wizard walks through the creation of Excel PivotTables based on an Access table or query so that you can perform Excel analysis directly within Access. You must have Excel 95 or later installed. It can be very slow because it needs Excel and Access loaded at the same time and is far harder to edit and more restrictive than using the crosstab query wizard.

Create a new form
Select Pivottable Wizard with the students table
Select the fields to summarize, for example sex and subject (Note that if one chose a third column it would have to be numeric since by default the wizard will choose to sum it)
Drag the sex field to the column heading
Drag the subject to the column heading. A warning about wanting a numeric field will appear because it wants to sum it- but ignore it
Drag the sex field to the data area. The wizard realises a count is required
Click on Next to see the result. The table design can be edited by clicking on the Edit Pivot Table button at the bottom of the screen
Save the form

The table can only be edited in Excel

Open the PivotTable in design mode and double click the Pivot, or select Worksheet and then edit from the Edit menu.

The PivotTable opens in a separate window with the Excel commands and toolbars displayed. Press ! to refresh the data. Only rows and columns can be swapped around since one can not edit summary data.

Right click on a row or column heading to change rank, grouping etc (a date field can be grouped by month, year etc)

Macro Conversion

There is now an option to convert a forms macros to Visual Basic

Open the student form in design view
Click on the lastname search button and look at the design properties of the click event
Select Macros from the Tools menu
Select Convert Forms macros to Visual Basic
Have another look at the design properties of the click event

Memo Fields

Memo fields automatically get a vertical scroll bar and EnterKeyBehaviour setting (this is used to specify what happens when you press ENTER in a text box control in Form view or Datasheet view). This makes entering multiple-line text easier. If you do not set this property to New Line In Field, you must press CTRL+ENTER to enter a new line in the text box.

Change Event

The Change event occurs when the contents of a text box or the text portion of a combo box changes on a form by typing the data directly. It also occurs when you move from one page to another page in a tab control. Setting the value of a control by using a macro or Visual Basic does not trigger this event for the control.

To run a macro or event procedure when this event occurs, set the OnChange property to the name of the macro or Event Procedure. You can coordinate data display among controls and display data or a formula in one control and the results in another control.

The Change event does not occur when a value changes in a calculated control or when you select an item from the combo box list.

BeforeInsert event occurs before the Change event, BeforeUpdate and AfterUpdate events for the text box or combo box control occur after you have entered the new or changed data in the control and moved to another control (or clicked Save Record on the Records menu), and therefore after all of the Change events for the control.

Triple State Check boxes, radio buttons

You can use the TripleState property to specify how a check box, toggle button, or option button will display Null values. It does not apply to the specified controls when they are used inside an option group control.

The TripleState property uses the following settings:

Yes The control will cycle through states for Yes, No, and Null values. The control appears dimmed (grayed) when its Value property is set to Null.

No (Default) The control will cycle through states for Yes and No values. Null values display as if they were No values.

Reports

Multipage Print Preview

Right click when in print preview to adjust level of zooming

Print Preview now supports the viewing of several pages at once. Users can decide how many pages they want to preview as well as how pages are organised on the screen in common with other Office applications.

Open Student table
Click on Print Preview
Look at the 3 preview icons the first enabling viewing of 1 page at a time, the second 2 at a time, the third has 6 windows allowing anything from 1 page at a time upto 6 pages at a time

Select Pages from the View menu

Grouping Properties

Use the RepeatSection property to specify whether a group header is repeated on the next page or column when a group spans more than one page or column. The RepeatSection property only applies to group headers on a report.

Look at the design of Student Report at the group header properties

Use the HasContinued property to determine if part of the current section begins on the previous page.

Use the WillContinue property to determine if the current section will continue on the following page.

Trapping reports with no data

Use the NoData event

Open a report in design view
Look at the event properties for the report
Click On No data to create a macro

Filter Reports

Filter the report to match the current filter in a form using the Filter and FilterOn properties

Print Icon

Prints immediately without confirmation/cancellation question

Mailing Label Wizard

Mailing Label wizard easier to use

Sharing Information in Access 97 on the Web

Access 97 has integrated hyperlinks throughout the application.

Adding and Deleting Hyperlinks

Access 97 supports the storage of hyperlinks as a native datatype. A hyperlink, when clicked, opens the linked Microsoft Office document at a specific location, or URL address, whether it is located on the World Wide Web, an intranet, corporate LAN, or local machine. Hyperlink targets enable you to jump to an Access97 database, or from within other Office documents such as a Word document or Excel spreadsheet.

Access 97 command buttons with hyperlink properties can now link to an external Web site. Supporting hyperlinks as a native datatype has further implications for databases created in Access 97 since they also allow connectivity between objects within an Access 97 database. Labels and images can serve as navigation controls to sources both inside and outside the database itself.

Look at the student table design
Create a field called webpage with datatype hyperlink
Open the student table
Select hyperlink from the Insert menu
You can now add a reference to a file on the PC e.g. c:\test\file.doc, the Internet e.g. http:\\eis.bris.ac.uk\~ccmjs or a file on the network e.g. \\csent\test\test.doc. A named location in the file can be specified e.g. c:\test\file.htm#part1

Access recognises hyperlink protocols and automatically translates the text into a valid address.

To specify a UNC path or a URL use the Link To File Or URL box, and click the Browse button to navigate to a file on your hard drive, a local area network, or on an FTP server that you have registered. For example, a bookmark name for a Word document; a slide number for a PowerPoint presentation; a sheet range for Excel (sheet 1!A2 to jump to the A2 cell in sheet 1); a Name tag for an HTML document

To jump to a Microsoft Access object, enter the object type and object name (for example, Table Students), or click the Browse button next to the Named Location In File box. The Browse button opens the Database window for the current or specified database, where you can select the object you want to open. Note If you omit the object type, and there are multiple objects with the same name, Access uses the following order to determine which one to reference: forms, reports, tables/queries, macros, modules.

Click the OK button in the Insert Hyperlink box.

To test the link:

click the label with the right mouse button, point to Hyperlink on the shortcut menu, and click Open. When you create a label this way, Microsoft Access sets the HyperlinkAddress property of the label to the value you specified in the Link To File Or URL box, and the HyperlinkSubAddress property to the value (if any) you specified in the Named Location In File box. Microsoft Access uses the Caption property for the display text that you see in the label itself. You can change any of these properties to modify the hyperlink:
click Edit Hyperlink on the submenu

To add a static hyperlink field to a form:

open a form based on the students table in design mode
click Insert Hyperlink (world with the chain icon) -only visible when form in design mode, or select Hyperlink from the Insert menu.
add the webpage field to the form
Run Netscape
Look at the form and look at the links. Note that you will get a system error if you try to look at a web URL when Netscape is not open

To change the appearance of a hyperlink

Select Options from the Tools menu
Select the Hyperlinks/HTML tab

To remove a hyperlink

Right click the hyperlink
Click Hyperlink on the shortcut menu
Click Edit Hyperlink
Remove Link

Web Toolbar

The web toolbar contains buttons to go forward, backward, stop, refresh, go to the start page, search the web etc. Note that the favourites menu will look for url and lnk files in the Favorites/Personal folder unless directed to look at the bookmarks.htm file in the Netscape folder.

To make it easier to see more of the file you have just opened, click the Show Only Web Toolbar button, which hides the application's toolbars.

If the web toolbar is not visible:

Select Toolbars from the View menu
Click Web. Any files you create can be opened by selecting Open Page from the File menu

Publish to the Web Wizard

The Publish to the Web Wizard is a flexible tool that allows users to publish any object in their database either statically or dynamically. It allows for custom HTML formatting using templates, and remembers all of the settings used to output the objects in the form of a configuration.

Users may choose to output some objects statically and some dynamically and choose a different template for every object in the database if they desire. A home page linking all the selected objects can be generated automatically. The Publish to the Web Wizard integrates with the Microsoft WebPost Wizard to automatically move the published objects to the Web server of choice

Select Save as HTML from the File menu

Static Data HTML Output

The output of static data from a database is extremely useful, especially when the information seldom changes. Users can share static views of their data via the Web, by:

select Save As HTML from the File menu (which will use the Publish to Web Wizard)
Click on Next (do not have a tick beside Web publication profile)
Select a table, query, form and report to appear on the Web page
Use the suggested template file
Select Static HTML
Give the name of a folder in which to place the html documents
Tick yes to create a home page and give a name for it (default name is default)
Not much point in saving the Web publication profile
Use explorer to check the names of the HTML files created . Note that a 6 page report will produce 6 HTML files, one for each page
Double click on the home page file to open Netscape and look at the result. Note that the form does not look like the form so there are no buttons or images etc but instead is shown as the data that the form was based on. If the query had a parameter then Access will ask for the value and only the data relating to that parameter will be shown on the web table. The report will display the data in the correct layout but will omit images and lines. Bold font will be retained

select Save as/Export from the File menu and choose type HTML. This will only produce HTML for one object. If you select the Save Formatted check box to preserve formatting of the datasheet.

Access outputs table, query, and form datasheets, and formatted reports directly to the HTML format. The formatting options employed are identical to those chosen for the object inside Access 97. Datasheet font colour, size, and face are maintained, along with datasheet cell background colour and sizing. If a report consists of more than one page it will create multiple pages of output connected by navigation hyperlinks, allowing the viewer to see the report on the Web as intended.

Hyperlinks stored in a database remain intact during the output process.

Dynamic Data Output to IDC/HTX

Users can also publish information from a dynamic datasource, so that web users receive the latest information by querying the database. Internet Database Connector (IDC) provides a way for users to share structured data in a workgroup, or via the Internet. HTX/IDC supports datasheets. ASP (Active Server page) supports datasheets and forms. Neither support reports. The database must reside on a Microsoft Internet server or Personal Web server.

select Save As HTML from the File menu (which will use the Publish to Web Wizard)
Click on Next (do not have a tick beside Web publication profile)
Select a table, query, form and report to appear on the Web page
Use the suggested template file
Select Dynamic HTX/IDC
Specify the data source

Access creates the internet database connector query file(s) (*.idc) containing information about the view of data to publish, and the HTML extension template(s) (*.htx) which contains information about how to format the information being returned.

Parameterised Web Queries

Parameterised queries created in Access 97 can also be published. This type of query requires the user to specify the criteria that is used in selecting the appropriate set of records. Thus one can present a datasource that is not only dynamic, but also interactive.

Templates

The Publish to the Web Wizard employs the use of templates to achieve formatting. A template is an HTML document that contains formatting attributes, images, and other elements that are applied the document being output. The usage of a pre-designed template in preparing HTML output, allows a user to define what the output will look like in a WYSIWIG fashion and reuse that design for later use. This enables any data residing in an Access 97 database to easily be formatted.

When you output a datasheet, form, or report, or use the Publish to the Web Wizard, and you specify an HTML template file, Access merges the HTML template file with the output files by replacing template tokens

HTML template token Replacement
 The object name (placed in the title bar of the Web browser)
 The object output
 An anchor tag to the first page
 An anchor tag to the previous page
 An anchor tag to the next page
 An anchor tag to the last document page
 The current page number

HTML template file default:

By default, Access looks for a template file in the \Program Files\Microsoft Office\Templates\Access folder. The default folder can be changed by entering a different folder in the HTML Template box located in the Hyperlinks/HTML tab of the Options dialog box.

HTML Importing, and Linking

The Import/Export Wizard can now import data from HTML tables in an HTML document directly into a new table, or append the records to an existing one. If there are multiple HTML tables or lists in a single page, Access 97 recognises these and allows the user to select the appropriate one to import.

Select Get External data from File menu
Choose Import
Select filetype HTML. (Choose one of the files you saved earlier from the static save)

If the information residing in an HTML page changes quite regularly, Access 97 provides the user with the ability to attach to an HTML table on any Web page. This is a read-only link.

Select Get External data from File menu
Choose Link
Select filetype HTML
One should be able to specify the filename as a URL eg http://eis.bris.ac.uk/~ccmjs/faculties_web.html but in practise this does not seem to work as it is in an incorrect format even though it was created by exporting a table!

The attached file is shown with a symbol looking like a yellow page with a world in the middle in the table listing

Connecting Databases over the Internet

The installable drivers that allow for the linking of databases in formats other than Access have all been enhanced, thus now allowing for connectivity via the Internet. A user simply selects the appropriate ftp site from the linking dialog, and then chooses any compatible database object. The ability to connect databases across the Internet allows users easy access to the information they need, regardless of its location.

Select Get External data from File menu
Choose Link
In the Look in window, select Internet Locations (FTP)
Select Add/Modify FTP locations and define a link eg ftp.bris.ac.uk (but there are no unzipped database files here) and press Link when a location has been specified

Other Features with Excel, Word

Retain Text Formatting

When you cut or copy data from Access forms and datasheets, the settings for font, alignment, and number formatting for headings and data are retained when pasted into Excel spreadsheets. Access supports Binary Interchange File Format (BIFF) used in Excel.

New Access features when using Excel

Click Access Report on the Data menu to start the Access report wizard
Click Access Form on the Data menu to start the Access form wizard
Click Convert to Access in the Data menu to create an Access database from the spreadsheet (alternative to importing)
Manipulate Access objects from Excel (and Word) by selecting Get External Data from the Data menu

General Features

Database Properties

Can be used to locate databases with the Find File feature

Select database properties from the File Menu
Select Database Contents to list the objects contained in the database
Select Summary information to better identify a database both from within Access and from other programs such as the Windows Find Files program. The Hyperlink Base setting on the Summary tab is used to create the base hyperlink path that is appended to the beginning of relative HyperlinkAddress property settings.

Other new Access 97 Edit menu commands

Select Create Shortcut to create a shortcut for selected object in the Database window so can quickly open it by clicking an icon.
Select Paste As Hyperlink to paste and format the Clipboard contents as a hyperlink. Edit the hyperlink using the Hyperlink command (Insert menu) to specify a file or URL.

Other new Access 97 View menu commands

Select Definition to display the procedure code of the procedure name at the insertion point in the Module window. If the procedure is defined in a DLL, Access displays the Declare statement used to define the DLL entry point.
Select Customize (Toolbars submenu) to display and hide toolbars; create and customize toolbars and menu bars.

Other new Insert menu commands

Select ActiveX Control to add an ActiveX control (formerly a custom control or OLE control) to a form or report. ActiveX controls are stored as separate files and must be entered in the Windows registry.
Select Tab Control Page (in form Design view) to insert a new page to the right of existing pages on a tab control and place it first in the page order sequence.
Select Hyperlink Column (in table Datasheet view) to create a new column (field) and set its data type to Hyperlink.
Select Class Module (in the Module window) to insert a class module not associated with a form or report into the current database and display its Declarations section.

Other new Tools menu command

Select Macro to create, run, and manage macros; convert macros to Visual Basic; create menu bars, toolbars, shortcut menus from macros.

Find and Open Databases

There are several icons to show creation details, properties etc when opening a database. In theory if one sets keywords, subjects etc for the database property, one should be able to search for these words when trying to find a database but this did not seem to work very well. The search seems to be restricted to filenames and modification dates.

Before opening a database, look at the tips for each icon

Linking Access to Excel

A new, updateable, ISAM driver allows users to create new tables in Access linked to Excel worksheets, allowing Access forms, reports and queries to work against native Excel data.

Click on Get External data from the File menu
Select Link Tables
In the link dialogue box, select Excel
Click the arrow to the right of the look in box and locate a spreadsheet file
Follow the instructions in the Import spreadsheet wizard

The linked spreadsheet now can be accessed in the same way as an Access table but the spreadsheet values will be changed if the data is changed from Access

Start-up Properties

Start-up properties that control how a database is opened, eliminate the need for Autoexec macros. Application icons, titles, startup form, menu and toolbar defaults, and system hot keys can be controlled.

Select Startup from the Tools menu to look at the options

Auto Conversion

In Access 97, upon opening a database created in an earlier version of Access, the user is prompted with the option to convert to the current format, or enable the database so that users running either Access 97 or an earlier Access version can still use the database.

Database Replication

Briefcase Replication is a convenient tool for keeping a database file up to date. Using the Briefcase method, you can drag a Microsoft Access database from a shared folder on the network which is converted to a Design Master, to My Briefcase on your own PC, and a replica is created in your Briefcase. Then when you reconnect the PC to the network, you can synchronize changes between your PC replica and the Design Master on the network. Both will receive changes to data from the other.

Briefcase Replication

Create a new Briefcase folder by opening Windows NT Explorer.
Open the folder you want to create the new My Briefcase icon in.
On the File menu, click New, and then click Briefcase.
Make a copy of the students database before you start
To keep files synchronized by using a floppy disk
Copy Studentscopy.mdb to My Briefcase. (You can drag the files or folders to the My Briefcase icon on your desktop). A Design master will be created from the original Studentscopy and a replica database placed in my briefcase
Edit the database in My Briefcase.
On the Briefcase menu, click Update All.

Replication Menu Commands

Replication commands on the Tools menu allow you to create replicas (full or partial) and synchronize them as you work in Microsoft Access. Replication commands enable you to create a replica of the design master, recover design master, resolve conflicts and synchronize. If you are running Access97 from a network, you must make sure that you have your own version of system.mdw, and that the registry points to your copy (using regedit) else you get Microsoft can't start a Microsoft Jet database session. To convert a database to a Design Master and create a replica:

Take off password security before you start because you will not be able to synchronise replicas otherwise.
Make a copy of the database first because a replication database can not be converted back
Select Replication from the Tools menu
Select Create replica. Access then asks if it can close the database and suggests that the database is backed up to student.bak for example. The design master database is now created and is bigger than the original (students expanded from 710Kb to 1014Kb).
Open the replication master database. Note that all the objects have a round yellow symbol by their side to indicate that if changes are made then they will be made in the replicas
Select Replication from the Tools menu. Note that all the menu options are available for use now.
Select Create replica. Access now creates a new database with default name 'Replica of Students.mdb'.
The master database is now closed down to create the replica and then reopened.
Open the replica database and make a change to the contact name in the Faculty table
Open the master database and make a change to the contact name of the Faculty table but different record
Select Replication from the Tools menu
Select Synchronise now
Examine the faculties table to see that the changes made to both databases appear
Conflicts if the same record had been changed can be resolved by the Conflict Manager

Partial Table Replication Programming

Partial replicas are replicas that contain only a subset of records in a full replica. To create a partial replica, you specify a filter that restricts the data that's synchronised to a subset of the full database. By using partial replicas, you can synchronise a replica with only the data that you need, rather than with an entire database. Access 97 now provides extensions for partial table replication via the Data Access Objects (DAO) which include methods and properties developers can use to programmatically replicate and synchronise databases.

Internet Replication

Using Internet Replication, changes to data in either the master or replica databases are transferred via ftp (file transfer protocol) to the appropriate location (client or server). The synchroniser will merge the appropriate database changes, ensuring that both databases have identical information. This allows users to keep their data up-to-date regardless of current location.

Enhanced Performance

Lightweight Objects

Access 97 now creates forms and reports that are "lightweight" (objects do not have a Visual Basic module behind them) by default. Thus, when opened, they will load faster than traditional objects containing code. However, the instant you add code to a form or report, it will become a traditional form or report object. A new property, called HasModule, has also been added to forms and reports, which toggles between a traditional or lightweight form.

Databases created using the Database Wizard in Access 97 create lightweight forms whenever possible. If code is required only for navigation purposes behind a command button, the Database Wizard will use a hyperlink as the means to launch that form instead of creating a new code module.

Remove Source Code (MDE files)

"Make *.MDE" allows developers to build and distribute more secure Access applications and improve performance. It strips the Visual Basic source code from an Access application and converts the file to a database with an *.MDE extension. The MDE database can be opened and used in Access, however users can not change the design or create new objects.

Select Database Utilities from the Tools Menu
Select Make MDE File

Performance Analyzer Wizard

The Performance Analyzer Wizard examines existing databases and recommends changes to improve application performance. It can automatically complete some performance enhancements; other enhancements are recommended or suggested by the wizard. It also offers a manual mode for more sophisticated users.

Select Analyze from the Tools Menu
Select Performance
Select Tables
Select All

Demand Loading

Access now shares Visual Basic with other Office applications so it can load library code into memory only when needed.

Visual Basic for Applications

The Access 97 development environment now supports Visual Basic for Applications 5.0 as well as IntelliSense™ features to make it easier for programmers to develop custom solutions.

New commands on Edit Menu and View menu

Select Outdent and Indent to structure layout
Select Complete Word to display a list of functions, methods and properties to choose from
Select Quick Info to display the syntax for a function, method or procedure based on cursor position
Select Definition from the View menu to enable you to select a keyword in the Module window and view its definition in the Object browser
Select Object Browser to show which members are globally accessible, function return types, parameter names and types, user-defined types and constants. It differentiates between built-in properties, custom properties, methods, event handlers and user defined procedures.

New commands on Debug Menu

Select Step Out to run all lines of code and nested procedures in Break mode, beginning with the current executable statement, and then return execution to the preceding procedure in the call tree. Use to avoid stepping through each line of code that calls multiple nested procedures.
Select Compile and Save All Modules to compile and save all modules in the database in a single operation.

Drag-and-drop code

Developing application code can be a time-consuming process so one tends to reuse as much code as is appropriate. Access 97 makes it easier to restructure and reorganise the code in procedures with the added ability to drag and drop code segments across procedures and modules.

Data Types

Boolean, Byte and Date have been added. Functions can now have arrays for parameters.

Colour-coded syntax

The integrated development environment within Access 97 supports colour-coded syntax to highlight items such as comments, commands and breakpoints in Visual Basic for Applications code. Users are able to set the font size, format and colour for viewing their codes.

Full module/procedure view

Users now have the ability to switch between Full Module view, and Individual Procedure view just by clicking the persistent button in the lower left-hand corner of the code window

Improved Watch window

The Watch window provides an enhanced debugging environment, including the ability to add watch points, and inspect any application variables at run-time, including all system variables. When you suspend execution of Visual Basic for Applications code, you can observe the values of watch expressions in the Watch pane of the Debug window by pressing the spectacle icon. You can also perform an instant watch to see the value of an expression that has not been specified as a watch expression.

QuickTips and drop-down menus

QuickTips and drop-down menus within the code window greatly simplify the process of writing code by allowing users to select from a list of valid Visual Basic commands as they write code.

 QuickTips display the syntax for a function, method, or procedure based on cursor position.

 Drop-down menus appear for properties and methods available for the object that precedes a period (.) and for constants which are valid for a property that precedes an equals sign (=).

Hierarchical Object Browser

Access 97 includes an Object Browser in Module design that allows developers to quickly explore an object hierarchy to find specific information. The browser differentiates between built-in properties, custom properties, methods, event handlers and user defined procedures. It indicates which members are globally accessible and which are not. The browser shows function return types, parameter names and types, and user defined types and constants. Since the object browser is a persistent window, users can continue editing, while using the Object browser as a reference tool. The Object Browser can also be used to insert precise object references into code without the developer having to re-enter them.

Class Modules (Scoping)

New Class Modules in Access 97 are modules that can contain the definition for a new object. When you create a new class instance, you create the new object. Any procedures defined in the module become the properties and methods of the object. This gives greater control of the application code and it is easier to create reusable components of code. Additionally because modules are objects with methods, properties and events developers have programmatic control over application code components. In Access 2 you can only call a procedure defined in a report or form from that object. In Access97 you can call a public procedure from in a report or form from any procedure in the database providing it is qualified by the class name of the form or report module. For example to call a procedure called test defined in the Student form, use the syntax Form_Student.test. It is better to place often used procedures in a standard module.

Multi-Instance Forms

In Visual Basic one can programmatically create multiple instances of the same form, which enables better branching within applications. Each form maintains its own underlying record set and can update data independent of other data. For example, a user can pull up several student forms simultaneously. The user can then move freely between forms and Access maintains the working environment correctly for each.

Conditional Compilation

Developers can set compilation flags within their codes to control the resulting application's behaviour. This enables developers to create debug and retail compilations of their applications simply by setting flags in their codes.

Source-Code Control

Access 97 supports integration with various source-code control providers, including Microsoft Visual Source Safe. This is available as an add-in in the Office Developers Edition. The add-in is compliant with the standard source-code control interface, thus any source-code control provider that supports the interface, including PVCS or Visual Source Safe, can now integrate with Access 97.

By integrating source-code control in Access, teams of developers can now work collectively on a single Access database application and maintain code integrity, for example by visually comparing differences between versions of an object.

OLE Automation Object and Controller

In order to automate common database tasks, Access 97 can drive or be driven from any application that supports OLE Automation, including Excel, Visual Basic and Microsoft Project. This means that developers will be able to take programmatic control of product functionality, such as the reporting engine, from outside of Access.

Menu changes from Access2 to Access 97

The following sections summarise the Access 2.0 commands that have changed location or functionality in Access 97.

File Menu

Access 2.0 command	Changed to this in Access 97
Compact Database (when no database open)	Moved to Tools/Database Utilities submenu
Convert Database (when no database open)	Moved to Tools/Database Utilities submenu
Encrypt/Decrypt Database (when no database open)	Moved to Tools/Security submenu
Repair Database (when no database open)	Moved to Tools/Database Utilities submenu
Toolbars (when no database is open)	Moved to View menu.
Unhide (when no database is open)	Moved to Window menu.
Run Macro (when no database is open)	Moved to Tools/Macro submenu
Add-ins (when no database is open)	Moved to Tools menu, available only when a database open.
Close Database	Renamed Close.
New submenu commands	Moved to Insert menu.
Rename	Moved to Edit menu.
Output To and Export	Consolidated in Save As/Export.
Import	Moved to Get External Data submenu.
Attach Table	Renamed Link Tables (Get External Data submenu).
Imp/Exp Setup	Removed. Click the Advanced button in the Import Text Wizard or Export Text Wizard to work with Import/Export specifications.
Print Setup	Renamed Page Setup.
Print Definition	Replaced by Documenter (Tools/Analyze submenu)
Save Layout (in the Relationships window)	Renamed Save.
Save As and Output To (in table and query Design view)	Consolidated in Save As/Export.
Save Table (in table Datasheet view)	Renamed Save.
Save Query (in query Datasheet view)	Renamed Save.
Save Form (in Form view and form Datasheet view)	Renamed Save.
Save Query As (in query Datasheet view)	Renamed Save As/Export.
Save Form As (in Form view and form Datasheet view)	Renamed Save As/Export.
Save Record (in Form and Datasheet view)	Moved to Records menu.
Save As Report (in form Design view)	Removed. Right-click a form in the Database window to save a form as a report.
Sample Preview (in report Design view)	Renamed Layout Preview (View menu).
Load Text (in the Module window)	Replaced by Import/Get External Data submenu
Save Text (in the Module window)	Renamed Save As Text.

Edit Menu

Access 2.0 command	Changed to this in Access 97
Relationships	Moved to Tools menu.
Insert Row (in table, query Design view)	Renamed Rows (Insert menu).
Set Primary Key (in table Design view)	Renamed Primary Key.
Undo All (in query Design view)	Removed.
Insert Column (in query Design view)	Renamed Column (Insert menu).
Undo Current Field (in table, query, form Datasheet view)	Renamed Undo Current Field/Record.
Insert Object (in table, query, form Datasheet view)	Renamed Object (Insert menu).
Links (in table, query, form Datasheet view)	Renamed OLE/DDE Links.
Tab Order (in form and report Design view)	Moved to View menu.
Find Previous (in the Module window)	Removed. Use Find to search backwards.
New Procedure (in the Module window)	Renamed Procedure (Insert menu).

View Menu

Access 2.0 command	Changed to this in Access 97
Tables	Moved to Database Objects submenu.
Queries	Moved to Database Objects submenu.
Forms	Moved to Database Objects submenu.
Reports	Moved to Database Objects submenu.
Macros	Moved to Database Objects submenu.
Modules	Moved to Database Objects submenu.
Table Properties (in table Design view)	Renamed Properties.
Palette (in form, report Design view)	Removed. Use the Formatting toolbar to perform palette functions.
Control Wizards (in form, report Design view)	Functionality moved to the Control Wizards button (Toolbox toolbar).
Split Window (in the Module window)	Renamed Split (Window menu).
Procedures (in the Module window)	Replaced by Object Browser.
Next Procedure (in the Module window)	Removed.
Previous Procedure (in the Module window)	Removed.
Immediate Window (in the Module window)	Renamed Debug Window.
Calls (in the Module window)	Renamed Call Stack.
Toolbars	Moved to the Toolbars submenu.

Tools Menu

The Tools menu is new in Access 97. It contains many commands that were on other menus in Access 2.0. New commands have been added to the Tools menu.

Access 2.0 command	Changed to this in Access 97
Security	Tools/Security
File/convert	Tools/Database Utilities/convert
File/repair	Tools/Database Utilities/repair
File/compact	Tools/Database Utilities/compact
File/add-ins/database Documentor	Tools/Analyze/Documentor
File/add-ins/attachment manager	Tools/Add-ins/Linked Table manager
File/output to	Tools/OfficeLinks
Edit/relationships	Tools/Relationships

Relationships Menu

Access 2.0 command	Changed to this in Access 97
Add Table	Renamed Show Table.
Remove Table	Replaced by Hide Table.
Create Relationship	Removed.

Query Menu

Access 2.0 command	Changed to this in Access 97
Add Table	Renamed Show Table.
Join Table	Removed.

Format Menu

Access 2.0 command	Changed to this in Access 97
Gridlines (in Datasheet view)	Removed. Use the Cells command to format gridlines.
Apply Default (in form Design view)	Replaced by AutoFormat.
Change Default	Renamed Set Control Defaults.
Page Header/Footer	Moved to View menu.
Form Header/Footer	Moved to View menu.

Records Menu

Access 2.0 command	Changed to this in Access 97
Go To	Moved to Edit menu.
Quick Sort	Renamed Sort.
Edit Filter/Sort	Renamed Filter.
Allow Editing	Removed.

Run Menu

(was Macro Menu in module Design view)

Access 2.0 command	Changed to this in Access 97
Compile Loaded Modules	Renamed Compile All Modules (Debug menu).
Continue	Renamed Go/Continue.
Step Into	Moved to Debug menu.
Step Over	Moved to Debug menu.
Set Next Statement	Moved to Debug menu.
Show Next Statement	Moved to Debug menu.
Toggle Breakpoint	Moved to Debug menu.
Clear All Breakpoints	Moved to Debug menu.
Modify Command$	Removed.

Window Menu

Access 2.0 command	Changed to this in Access 97
Tile	Replaced by Tile Horizontally and Tile Vertically.

Help Menu

Access 2.0 command	Changed to this in Access 97
Contents	Renamed Contents and Index.
Search and Cue Cards	Removed. Use Access Help to display the Office Assistant and view Help.
Technical Support	Functionality moved to Tech Support button in the About Access dialog box.

16-bit Windows DLL	32-bit Windows DLL
User.dll	User32.dll
Kernel.dll	Kernel32.dll
GDI.dll	GDI32.dll

Access 97 for Access 2 users

Contents

Minimum System Requirements for Microsoft Office 97 Professional

Keeping an existing version of Access

Default Folders

Features not installed in typical installation

Converting Access 2 databases to Access 97

Overview

Conversion Process

Common Conversion and Installation Problems

General

Database with large number of form, report, and module objects increases in size

Databases with Large Numbers of Modules

Tables

Tables with Large Numbers of Indexes or Relationships

Names

Datatypes

Importing, linking and attaching

Queries

SQL button

Queries with Date Criteria Between 1900 and 1929

Forms

Datasheet View

Control Names

Combo Boxes

Wizard-Generated Code

Command Buttons

Field widths

Hidden columns

Graphs

Form and Report Templates

Reports

Forms or Reports with Large Numbers of Controls

16- and 32-bit ActiveX Controls (OLE Custom Controls)

Conversion Fails Without Default Printer

Report Margins Adjust to Printable Region

Saving Report as RTF file

Northwind Reports need Arial Narrow font

ForceNewPage Property in Group Section

Modules

Conflicts with Reserved Words

Standard /Class Module Name Conflicts

Module /Procedure Name Conflicts

International dates

Web Browser

Connection Timeouts

Network Installation

What is confusing in Access 97 for the Access 2 user

What is new in Access 97 for the Access 2 user

Introduction

Office Assistant

New Help menu commands

Database Wizard

Spell Check Wizard

AutoCorrect

Drag and Drop

Right Mouse

View menu

Zoom

Compacting and Repairing

Desktop Shortcut

Tables

Datatypes

Lookup Wizard

Lookup Display Control

Sort Order

Relationships Window

Table Analyzer Wizard

Import Table wizard

Intelligent? Table Design

Application Splitter Wizard

Background Joins

Filters

Filter by Form

Filter by Selection

Filter by Exclusion

Advanced Filter Method

Queries

Design Grid

Simple Query Wizard