Export (0) Print
Expand All
Expand Minimize

What's New for Microsoft Access 2000 Developers

Office 2000
This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.
 

Microsoft Corporation

March 2000

Applies To: Microsoft Access 2000

Summary: This article presents an overview of the new programmability features in Access 2000. (20 printed pages)

Contents

Introduction
New Database Window
Working with Data and Database Design
Web Features
Client/Server Features
Security
Forms and Reports
Replication
Working with Other Data Sources
Macros
The Visual Basic Editor
Access Object Model Changes
ActiveX Data Objects (ADO)

Introduction

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.

New Database Window

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.

Working with Data and Database Design

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.

Web Features

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.

Scripting

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.

Hyperlinks

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.

Web Collaboration

You can use NetMeeting® to collaborate with others on an Access database or Access project over an intranet or the Internet.

Client/Server Features

Microsoft Access 2000 provides many new features that make it much easier to create client/server solutions.

Access Projects

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.

Upsizing Tools

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.

Security

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.

Forms and Reports

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

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.

Working with Other Data Sources

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.

Macros

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 Visual Basic Editor

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.

Access Object Model Changes

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.

New Objects

Objects that were added to Visual Basic in Microsoft Access 2000 are listed in the following table.

ObjectDescription
AccessObjectNew object representing all database objects within the CodeData, CodeProject, CurrentData, and CurrentProject object functionality
AccessObjectProperty, AccessObjectPropertiesNew collections and properties of the AccessObject object within the CodeData, CodeProject, CurrentData, and CurrentProject object functionality
AllDataAccessPages, AllForms, AllMacros, AllModules, AllReportsNew collections of the new CurrentData and CurrentProject object functionality
AllDatabaseDiagrams, AllQueries, AllStoredProcedures, AllTables, AllViewsNew collections of the new CodeData and CodeProject object functionality
CodeData, CodeProjectNew objects for code database functionality
ComAddInsNew COM add-ins functionality
CurrentData, CurrentProjectNew objects for current data functionality
DataAccessPage, DataAccessPagesNew data access page feature
DefaultWebOptionsDefault Web options for the application
FileSearchNew File Search functionality
FormatCondition, FormatConditionsNew conditional format feature
VBENew Visual Basic Editor functionality
WebOptionsWeb 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).

ObjectProperty
AccessObjectFullName

IsLoaded

Properties property (AccessObjectProperties collection)

Type (AccessObject object)

ApplicationAnswerWizard

CodeData

CodeProject

COMAddIns

CurrentData

CurrentProject

DataAccessPages

DefaultWebOptions

FeatureInstall

FileSearch

LanguageSettings

ProductCode

Properties property (Properties collection)

VBE

CodeData,
CurrentData
AllDatabaseDiagrams

AllQueries

AllReports

AllStoredProcedures

AllTables

AllViews

CodeProject,
CurrentProject
AllDataAccessPages

AllForms

AllMacros

AllModules

AllReports

BaseConnectionString

Connection

FullName

IsConnected

ProjectType

Properties property (AccessObjectProperties collection)

DataAccessPageConnectionString

Document

WebOptions

DefaultWebOptionsAlwaysSaveInDefaultEncoding

CheckIfOfficeHTMLEditor

DownloadComponents

Encoding

FolderSuffix

FollowedHyperlinkColor

HyperlinkColor

LocationOfComponents

OrganizeInFolder

UnderlineHyperlinks

UseLongFileNames

FormInputParameters

MaxRecButton

Recordset

ResyncCommand

ServerFilter

ServerFilterByForm

UniqueTable

FormatConditionEnabled (FormatCondition object)

Expression1

Expression2

Operator (FormatCondition object)

Type (FormatCondition object)

HyperlinkAddress

EmailSubject

ScreenTip

SubAddress

TextToDisplay

ScreenActiveDataAccessPage
WebOptionsDownloadComponents

Encoding

FolderSuffix

LocationOfComponents

OrganizeInFolder

UseLongFileNames

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).

ObjectMethod
AccessObjectPropertiesAdd

Remove

ApplicationCreateAccessProject

CreateDataAccessPage

GetHiddenAttribute

NewAccessProject

OpenAccessProject

SetHiddenAttribute

Code Project, Current ProjectCloseConnection

OpenConnection

DataAccessPageApplyTheme
DoCmdAddMenu

OpenDataAccessPage

OpenDiagram

OpenStoredProcedure

OpenView

FormatConditionDelete

Modify

FormatConditionsAdd

Delete

HyperlinkCreateNewDocument
WebOptionsUseDefaultFolderSuffix

New Events

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 MethodObject
FELineBreakTextBox
FuriganaControlTextBox
IMEHold/HoldKanjiConversionModeComboBox, ListBox, TextBox
IMEMode/KanjiConversionModeComboBox, Label, ListBox, TextBox
IMESentenceModeComboBox, Label, ListBox, TextBox
KeyboardLanguageComboBox, TextBox
NumeralShapesComboBox, Label, ListBox, TextBox
OrientationForm
PostalAddressTextBox
ReadingOrderCheckBox, ComboBox, CommandButton, Label, ListBox, OptionButton, TextBox, ToggleButton
ScrollBarAlignComboBox, ListBox, TextBox

Hidden Properties

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.

Hidden PropertyObject Replacement
AllowEditing, DefaultEditingFormAllowAdditions, AllowDeletions, AllowEdits, DataEntry
AllowUpdatingFormRecordsetType
BorderLineStyleBoundObjectFrame, CheckBox, ComboBox, Image, Label, Line, ListBox, ObjectFrame, OptionButton, OptionGroup, Rectangle, SubForm, TextBoxBorderStyle
DynasetFormRecordsetClone
MaxButton, MinButtonForm, ReportMinMaxButtons (Form only)
ShowGridFormDatasheetGridlinesBehavior

ActiveX Data Objects (ADO)

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.
    Note
       
    Although the Microsoft Jet 4.0 OLE DB Provider supports batch updating, there is no need to use batch updating with Access databases to improve performance because the Jet database engine runs locally—you won't see a performance gain when performing batch updates against an Access database. However, you should see a performance gain when performing batch updates against a SQL Server database because SQL Server's query processor can optimize and perform multiple SQL statements in a single operation on the server without requiring additional network round-trips.
  • 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.
    Note
       
    The ADO MaxRecords property of a Recordset object, which is designed to limit the number of returned records, is not supported by the Microsoft Jet 4.0 OLE DB Provider or the Microsoft Access ODBC driver. However, if you require this functionality, you can use the "TOP n" predicate in a Jet SQL statement, or set the TopValues property of a query that is saved in an Access database.
  • Support for multiple sets of records returned from a single stored procedure or batch SQL statement.
    Note
       
    Multiple sets of records can be returned by SQL Server and MSDE databases. Access databases can't return multiple sets of records because Jet SQL statements don't support multiple SELECT statements.
  • 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.

Show:
© 2014 Microsoft