Microsoft Access 2002

Microsoft Access is definitely a complex software while being at the same time an highly customizable application aimed to help users manage their data through what we call a database software. Nonetheless Microsoft Access is so versatile you can use it to create a small database of your audio CDs and friends or to manage the stocks of your international corporation where each sales employee can use a laptop PC to connect to the server and check via Access the status of the stocks. Whether you are working with databases to capture sales data within your company or just keeping track of important lists for personal use, working with databases is often not as easy or as intuitive as it could be. 

A key design goal for Access 2002 was to make it easier for users to build and use their databases. This goal was accomplished by providing users with a broad range of tools they need to find and use more of the product. Nonetheless if some areas of the software are indeed more approachable, the whole software remains complex. One of the new feature Access developers were waiting for is the XML support: using import & export feature you can implement XML to create real integrated applications in combination with the build Visual Basic for Applications programming language. The US version of Access 2002 is enabled for the use of speech for both voice dictation and command-and-control scenario: so you can dictate text and navigate menus using speech and voice commands.

Access 2002 comes with a new optional file format that promises faster access and data processing for large databases. According to Microsoft this format seamlessly handles future changes to Access, such as new properties and events, which have caused file format changes in previous releases. When you’re in design mode you can now undo & redo multiple actions that affect the following objects: MDB tables, MDB queries, ADP view, ADP stored procedures, ADP functions, forms, reports, data access pages, macros and modules. In the small enhancements area, Access 2002 provides users with a number of new shortcut keys that are supposed to help users accomplish tasks faster. It includes: in the design view or in the property sheet F7 takes you to the code window, F4 opens the property sheet in design mode, SHIFT+F7 moves the window focus back to the design surface without changing the control focus, and in any table, query, form, report, page, view or stored procedure, pressing CTRL+> or CTRL+PERIOD and CTRL+< or CTRL+COMMA toggles between views.

Users that are with Access since several years know that converting an Access database from a previous version to the latest one can sometimes be problematic despite all Microsoft efforts to enhance this process. If an error occurs when you convert a database from a previous version of Access (only from Access 95 and greater) to Access 2002, a table is created and will list information about each error so you can solve problems more easily. Another thing Access aimed to do was to make it simpler to access and analyze important information wherever it exists. More specifically, Access 2002 improves the ability for users to access information from corporate-level, back-end databases such as Microsoft SQL Server. 

Access 2002 also makes improvements in how users can analyze that data with tools such as PivotTable® dynamic views and PivotChart® dynamic views (that were previously only available in Excel) and Data Access Pages that enable users to extend corporate databases applications to the Web. Any .MDB table or query, or ADP table, view, stored procedure, function, or form can be viewed in either PivotTable or PivotChart view. So you can perform data analysis and build rich PivotTable and PivotChart view solutions quickly and simply. Furthermore, PivotTable and PivotChart views can be saved as Data Access Pages. Those Data Access Pages will be ready to be viewed and manipulated by others via their browser. One cool thing is that the Data Access Pages don’t require the use of Remote Data Objects on the server. This makes it easier for publishing read-only Data Access Pages on the Internet over a firewall.

Access 2002 features full support of the XML technology throughout the product. XML data can be created by exporting from a Jet or SQL Server database and can be imported into a Jet or SQL Server database. Access also makes it easier to import this schema or data documents into either SQL Server or Jet, by giving users the ability to include or exclude data or schema as well as determine whether the data should be appended to overwrite any existing information.

Additional benefits associated with XML support are:

• Users can create parts of or entire relational databases simply by importing an XSD schema from anywhere on the Web.
• Developers can create XSLT (data transformation) documents that allow data of different formats to move between SQL Server sources.
• Developers can use the Access Report Writer to create Web accessible reports.
• Developers can create Web presentations that run from either the server (ASP) or the client (HTM).
• When creating reports that contain data that rarely changes, such as quarterly reports, developers can use XML data documents rather than requiring an active connection to a server.
• Developers can limit active connections to corporate servers by creating “live” reports that use SQL Server 2000 HTTPSQL to return a read-only XML data document.

Developer will be glad to learn that with Access 2002, data can be quickly published to the Web using Internet standard XML/XSL. Developer can choose to export an Access report, form, table, or query to an XML Document that includes an associated XSL file for presentation. So forms and reports created in Access can be viewed with any Internet browser that supports HTML 4.0. Always in the XML chapter, developers can create their own custom XSL data transformations to be used when exporting data to an XML Document. That way, developers can change the format of the exported data or to create their own presentation of the data. This provides a simple mechanism to translate XML Documents from one format to another. For example, data exported from Access could be formatted into a structure understood by a SAP or custom corporate system.

When you publish data on the web, you need power and flexibility. The Data Access Page HTML Designer of Access 2002 provides users with a variety of new and improved enhancements so you can design Data Access Pages more effectively. Examples of just some of these new and improved enhancements include:

• Extended properties from both Microsoft Jet and Microsoft SQL Server™ 2000 databases. This means that lookups are dropped as lookups, and label properties are appropriately set.
• Improved hyperlink handling, making it easier to create pages that are linked together.
• With improved control sizing, users can see the actual size of the control while sizing, allowing better snap-to-grid support.
• Multi-select support through the keyboard and mouse enables users to apply sizing, horizontal and vertical spacing, alignment, and property settings to data access pages.
• Auto Sum now makes it easier than ever to create totals.
• With the Data Outline, developers can view and set properties on recordset.
• New connection properties make it simple to build applications that use the same connection string. This makes it easy to move applications from test to live.

To quickly move an existing Access solution to the Web you can save your existing forms and reports as Data Access Pages. Instead of creating new Data Access Pages, you just have to perform a Save As to create Web versions of your Forms and Reports. What a time earning! In addition, deployment of Data Access Pages can be simplified using an Access Jet database by specifying a relative path to the database or by using a common connection for all Data Access Pages using the new ConnectionFile property.

If users work with an Access Data Project, it remains possible to create and modify simple SQL Server stored procedures (aka Action Queries) using the Stored Procedure Designer: stored procedures can be created without learning Transact SQL. When you use an Access Form in an Access Data Project, Access 2002 lets you specify that any updates made to the records are saved and then sent to the server in a single batch. This was previously only possible by the developer writing code in the form. Now this functionality is expected by setting properties associated with the form. In addition, there are new properties, methods, and events to manage the commit and rollback process when the batch is submitted to the server.

One of the design goals of the Microsoft Access team was to give developers the tools they need to build powerful, sophisticated database solutions that seamlessly integrate with enterprise-wide data while ensuring forward and backwards compatibility with new and existing database solutions. Access 2002 now provides the tools to build solutions that integrate and leverage Internet-standards, such as XML, XSL, and dynamic Web pages, to better allow for the sharing and presentation of data across the intranet and Internet.

Extended Methods and Properties - Access 2002 provides a variety of new methods and properties including:

• Being able to set up lookup relationships, validation text, formatting, and subdatasheets against tables, views, and functions.
• Programmatically control printing properties via a new Printer object and Printers collection.
• Being able to get the DateCreated and DateModified properties for any Access object by using the AccessObject object.
• New methods and properties such as CompactRepair, ConvertAccessProject, AddItem, and RemoveItem methods anda new BrokenReference property that makes it easy to detect if a project has a broken reference.
• Access 2002 provides a variety of new methods and properties including:
• Being able to set up lookup relationships, validation text, formatting, and subdatasheets against tables, views, and functions.
• Programmatically control printing properties via a new Printer object and Printers collection.
• Being able to get the DateCreated and DateModified properties for any Access object by using the AccessObject object.
• New methods and properties such as CompactRepair, ConvertAccessProject, AddItem, and RemoveItem methods anda new BrokenReference property that makes it easy to detect if a project has a broken reference.