What's New for Microsoft Access 2000 Developers
Applies To: Microsoft Access 2000
Summary: This article presents an overview of the new programmability features in Access 2000. (20 printed pages)
New Database Window
Working with Data and Database Design
Forms and Reports
Working with Other Data Sources
The Visual Basic Editor
Access Object Model Changes
ActiveX Data Objects (ADO)
Microsoft® Access 2000 provides developers with many new features. These include improvements to the user interface that make it simpler to rapidly develop database solutions, as well as additions and changes to the programming environment and object models used to work with Access and data sources. This article provides an overview of new Access 2000 features that will be of interest to developers.
The Database window in Microsoft Access 2000 provides a variety of options for viewing and manipulating database objects.
- Use the Database window toolbar.
Quickly find commands for creating, opening, or managing database objects.
- Use the Objects toolbar.
View database objects in the Objects toolbar—its vertical orientation makes it easier to use.
- Organize database objects into groups.
To view your groups, click the Groups toolbar, which can contain shortcuts to database objects of different types.
- Use new object shortcuts.
In the Database window, quickly create a new database object by using a wizard, or open a new database object in Design view.
- Customize how you select and open objects in the Database window.
If you choose, change the default behavior so that you select a database object by resting the pointer over it, and open an object by single-clicking it.
- Select an object by typing its name.
For example, select the Shippers table while viewing the list of table objects by typing Sh.
Microsoft Access 2000 provides many new features that make working with data and designing a database even easier.
- Use record-level locking.
An Access database (.mdb file) now supports record-level locking, in addition to page-level locking (which locks all records on a 4K page). You enable the locking level with the new database option, Open databases using record-level locking (Tools menu, Options command, Advanced Tab). By default, this setting is selected (on), which means two users can update or delete two different records that are located on the same page within the database. (This isn't possible when you use pessimistic locking under page-level locking.) The locking mode that is in effect is determined by the setting in use for the first user to open a database.
When record-level locking is on, data edited through Access forms and datasheets will use record-level locking. Also, Recordset objects opened by using the Data Access Objects (DAO) OpenRecordset method, and any ActiveX® Data Objects (ADO) methods (when you use the Microsoft Jet 4.0 OLE DB provider) that open or return a Recordset object will use record-level locking. However, any SQL Data Manipulation Language (DML) queries—that is, queries that add, delete, or modify records—that are run from ADO (when you use the Microsoft Jet 4.0 OLE DB Provider), DAO, or the Access query user interface will use page-level locking. Page-level locking is used for SQL DML statements to improve performance when you are working with many records. However, even when record-level locking is turned on, it is not used for updates to values in memo fields and values in fields that are indexed—they still require page-level locking.
- Find and Replace dialog boxes are no longer modal.
You can now move freely between the Find and Replace dialog boxes and the data in the view or window.
- View related data in a subdatasheet.
Use a subdatasheet (a datasheet that is nested within another datasheet) to view and edit related or joined data in a table, query, form, or subform, all from the same view. For example, in the Northwind sample database the Suppliers table has a one-to-many relationship with the Products table; so for each row of the Suppliers table in Datasheet view, you can view and edit the related rows of the Products table in a subdatasheet.
- Automatically fix some errors caused by renaming objects in your database.
Name AutoCorrect automatically corrects many of side effects that occur when you rename forms, reports, tables, queries, fields, text boxes, or other controls. However, Name AutoCorrect can't repair references to renamed objects in the following situations:
- Change references in an invalid SQL statement.
- Fix references in your Visual Basic® code.
- Fix references in a linked table when the table or fields have been renamed in the back end of a front-end/back-end database.
- Work in a replicated database. When you replicate your database, Access turns Name AutoCorrect off. You can't turn on Name AutoCorrect in a database Design Master or replica.
- Repair references to macros in a toolbar or menu, including menu and toolbar properties and event properties that are set to macro names.
- Work in a Microsoft Access project (.adp file).
- Take advantage of Unicode support.
Use the characters of any language that Unicode supports in your data. Use Unicode compression to offset the effect of Unicode's increased storage space requirements. Take advantage of dual-font support—specify a substitute font that you can use in addition to your default font to properly display all of the characters in your data.
- Work with the euro.
To easily display euro amounts with other currencies, you can use the euro setting (€ #,###.##) of the Format property to indicate a euro amount. Alternatively, you can enter the euro symbol (€) by pressing ALT+0128 on the numeric keypad with NUM LOCK on. When you paste or import data that contains the euro symbol from Microsoft Excel 2000 into Microsoft Access, Access stores the euro symbol regardless of the currency symbol defined in the Regional Settings in Windows Control Panel. Finally, you can use the EuroConvert function to convert one currency to another by using the euro as an intermediary.
- Print relationships.
You can print a report of the relationships in your Access database as they appear in the Relationships window.
- Use the keyboard to manage relationships.
You can use the keyboard to create, edit, and delete relationships and joins.
Microsoft Access 2000 provides new features designed to help you create database solutions that take advantage of the Internet or a corporate intranet.
Data Access Pages
A data access page is a Web page that you can use to add, edit, view, or manipulate current data in an Access database or a Microsoft SQL Server™ database. You can create pages that are used to enter and edit data, similar to Access forms. You can also create pages that display records grouped hierarchically, similar to Access reports.
- Collect and distribute current data in several ways.
You can use pages to add, edit, and view data within an Access database or Access project; you can use them on the Internet or on an intranet; and you can send them in e-mail.
- View grouped records interactively.
On grouped pages, you can view just the details you want by expanding and collapsing group headers. You can also sort and filter records.
- Analyze data and make projections.
You can organize data in different ways by using a PivotTable® list, make projections and do complex calculations by using a spreadsheet control, and view data graphically in a chart.
- Display HTML text.
You can store HTML code in fields in your database and display it as formatted HTML text on the page. For example, if a value in a field includes the HTML tag that formats text as italic, <I>Text</I>, you can use a bound HTML control on the page to display the value in italic text.
- Use familiar design tools.
In Design view, create pages by using toolbars, the toolbox, themes, and other features that are similar to the tools you use to create forms and reports.
To help developers work with script in data access pages, Access 2000 provides the Microsoft Script Editor. The Script Editor is a new and powerful integrated development tool that allows you to do the following:
- Edit HTML in a document.
- Add script, ActiveX, and HTML intrinsic controls.
- View a document as a Web page.
To open the Script Editor, open a data access page, and then on the Tools menu point to Macro and click Microsoft Script Editor.
The Script Editor supports working with the scripting languages provided by the scripting engines that are installed on a user's computer. Office 2000 installs Microsoft Internet Explorer 5, which installs scripting engines for the Microsoft Visual Basic Scripting Edition (VBScript) version 5.0, and Microsoft JScript® version 5.0 scripting languages.
In Access 2000, you can now assign a hyperlink to a toolbar button or menu command for easy access to a location on your computer, a network, an intranet, or the Internet.
You can use NetMeeting® to collaborate with others on an Access database or Access project over an intranet or the Internet.
Microsoft Access 2000 provides many new features that make it much easier to create client/server solutions.
In earlier versions of Access, the only way to create a client/server solution is to create an .mdb file with linked tables that use an ODBC driver to link to a database server such as SQL Server. This kind of client/server solution also requires Access to load the Microsoft Jet database engine to open the database and open the linked tables, which creates additional memory overhead. Although Access 2000 continues to support client/server solutions that use linked tables, it also supports a new file format and data access architecture. The new technology allows you to create a client application that connects to a SQL Server 6.5 (with Service Pack 5), SQL Server 7.0, or Microsoft Data Engine (MSDE) database through OLE DB without loading the Jet database engine. To do this, you create an Access project file, which is saved with an .adp extension. An Access project can store forms, reports, macros, and Visual Basic for Applications (VBA) modules locally in your client solution file and use the OLE DB connection to display and work with the tables, views, relationships, and stored procedures that are stored on SQL Server. You create the forms, reports, macros, and VBA modules in an Access project by using most of the same tools and wizards you use to create these objects in Access databases. This allows you to quickly develop client/server solutions that work directly against a SQL Server back end.
Access also allows you to create new SQL Server databases, and provides a variety of visual tools to create and modify the design of tables, views, stored procedures, triggers, and database diagrams on your database server. The tables, views, and stored procedures you create, as well as SQL SELECT statements, are all valid data sources for Access forms, reports, and data access pages.
Microsoft Data Engine (MSDE)
Microsoft Access 2000 ships with a new database engine called Microsoft Data Engine (MSDE). MSDE is a new technology that provides local data storage compatible with Microsoft SQL Server 7.0. You can also use MSDE as a remote data storage solution. You can think of MSDE as a client/server database engine alternative to the file-server Microsoft Jet database engine. MSDE runs under Windows NT® 4.0 or later and Windows® 95 or later. It is designed and optimized for use on smaller computer systems, such as a single-user computer or small-workgroup server.
MSDE doesn't limit the number of users who can connect to its database, but is optimized for five users. For a larger numbers of users, you should use SQL Server 7.0. Databases created with MSDE are 100 percent compatible with SQL Server 7.0 and support many of the features of SQL Server 7.0, including most Transact-SQL commands. MSDE also logs transactions, which means that if anything should go wrong during a write to an MSDE database, such as a disk error, network failure, or power failure, MSDE will recover from its transaction log and revert to its last consistent state. This gives MSDE databases greater reliability than Microsoft Jet (.mdb) databases, which don't log transactions.
Because MSDE is based on the same database engine as SQL Server, most Access projects or client/server applications can run unchanged on either version. However, unlike SQL Server 7.0, MSDE has a 2-gigabyte database size limit, supports up to 2 processors for Symmetrical Multiprocessing (SMP), and in a replicated database environment cannot be a replication publisher for transactional replication (although it can act as a replication subscriber for both transactional and merge replication, and as a replication publisher for merge replication).
Compared with using Access with a Microsoft Jet database (.mdb), using MSDE does require more memory. The minimum supported configuration for running MSDE is a Pentium 166 with 32 MB of RAM. MSDE does manage its memory usage dynamically, so that it will react to operating system pressure on memory resources to allocate as much memory as it can effectively use, but will stop allocating memory and even, if needed, give back memory to ensure that other applications have memory available. However, if your solution requires the minimum usage of memory resources, you should use Access with a Microsoft Jet database.
The Upsizing Wizard included with Microsoft Access 2000 allows you to convert an existing Access database (.mdb) to a client/server solution. The Upsizing Wizard creates a new SQL Server database structure by re-creating the structure of your Access tables (including indexes, validation rules, defaults, and relationships), and then copying your data into the new SQL Server database. Additionally, the Upsizing Wizard will attempt to re-create your queries as SQL Server views and stored procedures.
You can choose to upsize only your database structure and data, or, after creating the SQL Server back-end database, you can choose to create an Access front-end client application. The Upsizing Wizard can create the front-end client application in either of two ways:
- By keeping the current Access database file (.mdb) and adding linked tables that connect to the upsized tables on SQL Server. Your existing forms, reports, and data access pages will use the new linked tables as their data sources.
- By creating a new Access project file (.adp) and copying the forms, reports, data access pages, macros, and modules from the current Access database, and then connecting that Access project file to the upsized tables on SQL Server. The copied forms, reports, and data access pages that refer to the local database will be converted to use the new upsized SQL Server tables, views, and stored procedures as their data sources through the .adp file's connection to the server. Data access pages that refer to databases other than the current database will be unchanged after upsizing.
Microsoft Access 2000 provides new features to make it easier to secure your database solutions.
- Secure your Access database with the User-level Security Wizard.
The User-level Security Wizard is now much easier to use and is the preferred method for defining user-level security on a Microsoft Access database for most common security schemes.
- Protect your code with Visual Basic for Applications password protection.
Modules and modules behind forms and reports are now protected by a Visual Basic for Applications (VBA) password that you create in the Visual Basic Editor; they are no longer protected by user-level security.
Microsoft Access 2000 provides new features to make it faster and easier to create great-looking forms and reports.
- Group text boxes and other controls.
Use the Group command on the Format menu to group related text boxes and other controls on a form or report.
- Define conditional formatting rules for text boxes and other controls.
Use the Conditional Formatting command on the Format menu to define the font color, font size, control background color, and other visual information that gives feedback to users as they enter data on a form.
- Create forms and reports for SQL Server databases.
In a Microsoft Access project, create forms and reports for a SQL Server database with the same tools that you use to create them in an Access database.
- Distribute reports to users who don't have Microsoft Access.
Export Access reports to report snapshot (.snp) file format. You can use Snapshot Viewer to view, print, and mail report snapshots.
Replication commands on the Access Tools menu in an Access database (.mdb) and in an Access project (.adp) allow you to create replicas and synchronize them on demand as you work in Microsoft Access.
- Replication in an Access project
You can replicate data in an Access project by creating publications and subscriptions.
- Jet and Replication Objects (JRO)
The JRO object model includes methods and properties that developers can use to programmatically replicate and synchronize databases and Access projects.
- Web server replication
You can synchronize with a replica located on a Web server. (You must use Replication Manager, which can be installed as part of the Microsoft Office 2000 Developer product to configure Internet synchronization.)
- Replica priority
A replica in a replica set is assigned a priority when it is created. The highest-priority replica wins in the case of a synchronization conflict.
- Prevent deletes option
This option prevents users from deleting records in a replica.
- Local and anonymous replicas
You can create local replicas and anonymous replicas. Local and anonymous replicas can synchronize only with their parent, global replica. Anonymous replicas are recommended for Internet applications if you need a large number of replicas.
- Conflict Viewer
The Conflict Viewer is the default tool in Access to reconcile and resolve synchronization conflicts.
- Row Level Tracking property
When this table property is set to True, it indicates that conflicts are tracked based on the row level of a table. Conflicts are tracked at the column level by default.
- ConflictFunction property
This property is used to replace the Microsoft Access Conflict Viewer with a customized procedure that assists users in resolving synchronization conflicts.
- Retention Period
This setting controls the number of days nonsynchronized records are retained in the system tables of a replica set's Design Master. The retention period can be changed by using Replication Manager, or in code by using the Jet Replication Objects (JRO) RetentionPeriod property.
- Synchronization conflicts
A single mechanism is now used to record and resolve conflicts and errors, making it easier to resolve conflicts. Whenever a conflict occurs, a winning change is selected and applied in all replicas and the losing change is recorded as a conflict in all replicas.
- Column-level conflict resolution
Conflicts are evaluated at the column level. Changes to the same record in two different replicas cause a synchronization conflict only if the same column or field is changed.
Microsoft Access 2000 provides new features for working with other data sources.
- Work with Microsoft SQL Server.
Create an Access project that is easy to connect to a Microsoft SQL Server database, or use the SQL Server Database Wizard to quickly create a SQL Server database and an Access project at the same time. Working with an Access project is similar to working with an Access database—the process of creating forms, reports, data access pages, macros, and modules is the same. Once you connect to a SQL Server database, you can view, create, modify, and delete tables, views, stored procedures, and database diagrams by using the Microsoft SQL Server Design Tools.
- Create a new Access database from data in another file format.
Just open a file that is in another file format—such as text, dBASE, Paradox, or spreadsheet format—in Access; Microsoft Access automatically creates an Access database and links the file for you.
- Import or link data from Microsoft Outlook or Microsoft Exchange.
Use the Exchange/Outlook Wizard to import or link data from Microsoft Outlook® and Microsoft Exchange Server. For example, you might want to link to your Microsoft Outlook Contacts folder and then create form letters and mailing labels by merging the data with the Microsoft Word Mail Merge Wizard.
Microsoft Access 2000 provides new macro actions you can use to open data access pages and the new database objects, diagrams, stored procedures, and views, in an Access project (.adp).
- Use the OpenDataAccessPage action to open a data access page in the current database in either Design view or Page view.
- Use the OpenDiagram action to open a database diagram in the current Access project.
- Use the OpenStoredProcedure action to open a stored procedure in the current Access project. You can open the stored procedure in Datasheet view, Design view, or Print Preview.
- Use the OpenView action to open a view in the current Access project.
These new macro actions have also been exposed as new methods of the DoCmd object to allow you to perform these actions from VBA code. Also, a number of the other methods of the DoCmd object have different behaviors and arguments to accommodate the differences in Access project objects.
The Microsoft Visual Basic Editor is the development environment in which you create and edit VBA code that is contained in files that you have created in Microsoft Office applications—including Access databases and Access projects. The Visual Basic Editor features include the Project Explorer, the Properties window, the Object Browser, and the Code window, as well as debugging tools.
Extensive changes have been made to the Microsoft Access 2000 Visual Basic object model to support new and improved features in the application. Many objects, properties, and methods have been replaced. To provide backward compatibility, most of the replaced components have been hidden rather than removed. This means that they don't show up in the Object Browser by default, but old code that uses the hidden components will still work correctly without modification. When you write new code, however, you should use the new objects, properties, and methods.
Objects that were added to Visual Basic in Microsoft Access 2000 are listed in the following table.
|AccessObject||New object representing all database objects within the CodeData, CodeProject, CurrentData, and CurrentProject object functionality|
|AccessObjectProperty, AccessObjectProperties||New collections and properties of the AccessObject object within the CodeData, CodeProject, CurrentData, and CurrentProject object functionality|
|AllDataAccessPages, AllForms, AllMacros, AllModules, AllReports||New collections of the new CurrentData and CurrentProject object functionality|
|AllDatabaseDiagrams, AllQueries, AllStoredProcedures, AllTables, AllViews||New collections of the new CodeData and CodeProject object functionality|
|CodeData, CodeProject||New objects for code database functionality|
|ComAddIns||New COM add-ins functionality|
|CurrentData, CurrentProject||New objects for current data functionality|
|DataAccessPage, DataAccessPages||New data access page feature|
|DefaultWebOptions||Default Web options for the application|
|FileSearch||New File Search functionality|
|FormatCondition, FormatConditions||New conditional format feature|
|VBE||New Visual Basic Editor functionality|
|WebOptions||Web options for data access pages|
New Properties (By Object)
Properties that were added to existing objects in Microsoft Access 2000 are listed in the following table (sorted by object name).
Properties property (AccessObjectProperties collection)
Type (AccessObject object)
Properties property (Properties collection)
Properties property (AccessObjectProperties collection)
|FormatCondition||Enabled (FormatCondition object)
Operator (FormatCondition object)
Type (FormatCondition object)
New Methods (by Object)
Methods that have been added to existing objects in Microsoft Access 2000 are listed in the following table (sorted by object name).
|Code Project, Current Project||CloseConnection
The Dirty event was added to Visual Basic in Microsoft Access 2000. The Dirty event occurs when the contents of a form or the text portion of a combo box changes. It also occurs when you move from one page to another page in a tab control. Examples of this event include entering a character directly in the text box or combo box or changing the control's Text property setting by using a macro or Visual Basic.
Language-Specific Properties and Methods
The Microsoft Access 2000 Visual Basic object model has new language-specific keywords for use with Asian and right-to-left languages. The availability of these language-specific keywords depends on the language support you have selected or installed. Properties and methods that are available only in Asian or right-to-left languages are listed in the following table.
|Property or Method||Object|
|IMEHold/HoldKanjiConversionMode||ComboBox, ListBox, TextBox|
|IMEMode/KanjiConversionMode||ComboBox, Label, ListBox, TextBox|
|IMESentenceMode||ComboBox, Label, ListBox, TextBox|
|NumeralShapes||ComboBox, Label, ListBox, TextBox|
|ReadingOrder||CheckBox, ComboBox, CommandButton, Label, ListBox, OptionButton, TextBox, ToggleButton|
|ScrollBarAlign||ComboBox, ListBox, TextBox|
Properties that have been hidden in the Microsoft Access 2000 Visual Basic object model because their functionality has been replaced by new language elements are listed in the following table. These properties are supported only for backward compatibility; for new code, you should use the replacement functionality provided in Access 2000. To view hidden objects in the Object Browser, right-click in the Object Browser window and click Show Hidden Members on the shortcut menu.
|AllowEditing, DefaultEditing||Form||AllowAdditions, AllowDeletions, AllowEdits, DataEntry|
|BorderLineStyle||BoundObjectFrame, CheckBox, ComboBox, Image, Label, Line, ListBox, ObjectFrame, OptionButton, OptionGroup, Rectangle, SubForm, TextBox||BorderStyle|
|MaxButton, MinButton||Form, Report||MinMaxButtons (Form only)|
Microsoft Access 2000 includes a new data access programming model called ActiveX Data Objects (ADO).
What Is ADO?
If it's necessary to upgrade your system, Microsoft Access 2000 installs the components required to use what was the latest version of ActiveX Data Objects at the time of its publication, ADO 2.1. (Microsoft Windows 2000 includes a later version of ADO, ADO 2.5, and thus does not require upgrading.) ADO supports a broader array of data sources than the Data Access Objects (DAO) programming model that is provided in earlier versions of Office. However, Office 2000 applications continue to provide support for DAO through the Microsoft DAO 3.6 Object Library, so you can run existing solutions that use DAO, or create new solutions that use DAO as well. You can also use both ADO and DAO code in your solution if you want.
The ADO programming model supports key features for building desktop, client/server, and Web-based applications, including the following:
- Independently created objects. Unlike DAO or Remote Data Objects (RDO), you no longer have to navigate through a hierarchy to create objects, because most ADO objects can be independently created. This allows you to create and track only the objects you need, and also results in fewer ADO objects and thus a smaller memory footprint.
- Batch updating, which helps improve performance by locally caching changes to data, then writing them all to the server in a single update.
- Support for stored procedures with in/out parameters and return values against a SQL Server database.
- Different cursor types, including the potential for support of back-end–specific cursors.
- Support for limits on the number of returned records and other query goals for performance tuning.
- Support for multiple sets of records returned from a single stored procedure or batch SQL statement.
- Free-threaded objects for efficient Web server applications.
Choosing When to Use ADO
Although ADO provides access to a broader variety of data sources than DAO, and even exposes some features of the Jet 4.0 database engine that aren't available from DAO, there are some limitations to using ADO against Access databases that require you to continue to use DAO.
- It's not possible to exchange information between ADO and DAO code. For example, if a DAO procedure returns a Recordset object, there is no way to translate or pass that DAO Recordset object to ADO code, and vice versa—an ADO Recordset object can't be read by or translated to DAO. However, this doesn't mean that ADO can't work with saved database objects, such as tables and queries, that were created with DAO, and vice versa. But it does mean that although ADO and DAO can coexist in the same project, you can't use ADO code to work with objects returned by preexisting DAO code. You must either continue to use DAO code, or rewrite those procedures by using ADO code.
- In the Access object model, the new Recordset property of a Form object can be used to request or specify a Recordset object for the data being browsed in a form. If you request the Recordset object for the current form in an Access database, Access always returns a DAO Recordset object. Therefore, you must continue to use DAO code to work with the Recordset object that is returned.
- When you use the Recordset property to set the Recordset object of a Form object to a Recordset object you created, the data will be read-only if you set the Form object to an ADO Recordset object. If you want the data to be writable, you must set the Form object to a DAO Recordset object.
- To read and set database properties in an Access database, and to read and set certain table properties, such as the Description and Filter properties, you must continue to use DAO code.
If you are updating an existing DAO data access component, or developing new data access components that will only be working with Access databases or other data sources supported by the installable Indexed Sequential Access Method (I-ISAM) drivers of the Jet database engine, you can continue to use DAO by establishing a reference to the Microsoft DAO 3.6 Object Library. All DAO code written for DAO 3.5 (with the exception of code that defines user-level security for code modules in Access 2000 databases) will continue to work with DAO 3.6.
Only the following new Microsoft Jet 4.0 database engine features require ADO:
- New Jet SQL commands and syntax
Additional SQL commands and syntax were added to make Jet SQL conform more closely to the ANSI SQL-92 specification.
- Connection control
This is a setting that allows you to exclude all new connections and exclude current users after they close the database.
- User list
This feature programmatically displays a list of information about all the users who are currently logged on to the database.
- Programmatic control over page-level or record-level locking
You can use ADO to control whether the Jet database engine uses page-level or record-level locking when records are being added, deleted, or modified from VBA code.
If you do not require access to these Jet database engine features and do not require other ADO-specific features, you can continue to write code that uses DAO until you encounter these requirements.
If you are creating new data access components, you should consider using ADO for its advanced features, simplified object model, and support for multiple data sources. In particular, ADO is a good choice if you are developing an Access database solution that will later be upgraded to SQL Server—you can write your ADO code to minimize the number of changes that will be required to work against a SQL Server database. In addition, ADO is a good choice for developing new data access components that work with SQL Server, multidimensional data, and Web applications.