ACCESS 2000 (MS Access 2000)

Member Name: Themis

Product:	MS Access 2000
Date:	17/06/01 (1167 review reads)
Rating:

Advantages: .

Disadvantages: .

Like the rest of Office, Access has been reworked with the aims of ease of use, collaborative working on the Net and powerful data analysis. In the case of Access, however, a lot of effort has gone into facilities for power users and developers, probably because Access has such a stranglehold on the desktop database market that Microsoft sees the need to push for the developer database market as more important.
Looked at another way, Access had previously been a product that was weaker on the developer side than on the end-user side, so that was the part that needed most improvement.

CHANGE OF VIEW
The first thing you notice about Access is that the database window has been revamped. It now looks more like the other Office elements - more like Outlook than anything else. There's a split-pane view with all the object types in your database organised in a list in the left-hand pane, and the specific examples of that particular object in the right-hand pane. One addition to the overall facilities to make Office match your way of working is the ability to create groups of objects. For example, in your order-processing database you could set up a Product group, into which you put all the tables, queries, forms and so on that have anything to do with products. The links are really shortcuts - the objects still show up within their 'natural' sets, so that the forms still appear when you're looking at all the forms. However, when you want a quick view of everything to do with products, you can be sure you're not missing anything.

EASE OF USE
While other Office applications focus on AutoCorrect and automatic completion of lists, the Access version of ease of use takes a rather different approach. One excellent change is an automatic name change facility, so if you rename a column in a table, all references to it are changed automatically. You can save a database in the format of a previous version of Access, great for excha
nging data, although you need to check that everything works before blithely assuming that it's OK. You can also drag and drop tables and queries into Excel, and they show up complete with column headings and relevant formatting. If your recipient wants a picture rather than the data, you can take a snapshot of a report, then send it out on disk, to a Web page or as an e-mail attachment, without any particular fuss.

More general improvements can be seen in the wizards, with improvements to the Database, Query and Performance Analyser Wizards. The Query Wizards, and in particular the Simple Query Wizard, now work out which columns you could use as the basis for numeric summaries, and the Cross-tab Wizard lets you drag and drop the columns onto the grid in a more natural way.

Performance analysis has been improved, with a more intelligent analysis of which tables should be split, and where you might want to add an index. The data you can work on now includes SQL Server tables. The Excel PivotTable improvements also come across into Access, with the added charting support and improved handling of large data sets that have appeared in Excel 2000

DATA ACCESS PAGES
Data Access Pages, Web pages that you can use to view and edit data from an Access database or a SQL Server table, are new in this version of Access. While you could export pages from reports and forms in HTML format in Access 97, Data Access Pages let the visitor to the Web page enter and edit the data, and search for specific data. You can create Data Access Pages that show grouped records as they'd appear in your Access reports, and the Web visitor can sort and filter the data, and can expand and collapse the groups. When you're designing data to show on the Web, you can store HTML codes in your database fields, and have those codes obeyed when the data appears in your Web pages, so that you could define a particular entry to appear bold and underlined, for example
. You can set up grouped data access pages to ensure that related information stays together. Data in grouped pages shows in a hierarchal collapsed outline when you're working on the pages. There's a new toolbox for creating the Data Access Pages. This has controls like a drag-and-drop field list and hyperlink editor.

VIEWING THE DATA
There are some small but nice changes to reports and forms in this release, such as the ability to make changes to form design without opening the form designer, easier ways to add conditional formats to your reports and forms, and the ability to group controls on your forms for ease of editing.

Another option lets you send reports to non-users of Access, using a Snapshot format that shows the data in a mini-viewer.

Subdatasheets are new in this release, and as you might guess from the name, give you a way to show your data in a hierarchical view when looking at a datasheet. This gives a simple way to view data tables that are related using a one-to-one or one-to-many relationship, so that if you're viewing a customer table that has a one-to-many relationship to Orders, you see all the orders for a particular customer as you move to their row in the datasheet.

If the Orders table has a one-to-many relationship defined to Order Details, and you move onto the Orders, you'll see all the Order Detail lines for that Order. It's similar to the form and subform, but without the hard work.

BIG DATA
If your aim in life is to use Access as a front end to large corporate databases, there are lots of facilities in there to aid you in your desire, so long as you're thinking Microsoft SQL Server, that is. At the basic level is a new kind of Access database called a project, which gives you native-mode access to SQL Server data tables using OLE DB. You use the same Access tools to create your forms, reports, queries and so on, and unlike earlier versions where you just ha
d a somewhat static view onto your SQL data, Access projects give you the ability to view, create, modify and delete tables, to create stored procedures, to set up views and to manipulate data diagrams.

You can also choose to use the Microsoft Data Engine, an addition that gives you local data storage that's compatible with SQL Server 7. The best analogy for the Data Engine is that it's a client/server alternative to the standard Microsoft Jet database engine. If you decide that what you really need to do is to move to SQL Server, the Access Upsizing Wizard will take your Access database and create either an Access project, or a straightforward SQL Server 6.5 or 7.0 format database with the same data, database objects and data definitions as the original.

ADVANCED OPTIONS
Many of the best improvements to this version of Access are those for the developer or power user. For example, you now have a user-level Security Wizard that gives a simple way to define who can use your database, and which items any user or group of users can view, edit or modify. You can add password protection to your Visual Basic for Applications modules, and even choose to have all the source code removed from the runtime version of your database to prevent any unauthorised editing of the modules. This also prevents anyone from seeing how you achieved your masterpiece.

One issue with previous versions of Access was the fact that only page-level locking was supported. This meant that if you opened a record for editing, all the other records on the same 4K page were also locked, and other users couldn't use those records until you'd finished your editing. You can now choose to use row-level locking, so that only the record you're actually working on is locked - much more colleague friendly.

REPLICATION
The need to replicate databases increases with the rise of remote users, and this version has a range of improvements to the Acce
ss replication facilities. You can create replicas of both MDBs and the new ADPs, with on-demand synchronisation while you're working. You can also replicate using publish and subscribe, and new in this version is support from the Jet database engine in terms of extra methods and properties that developers can use to write code to handle the synchronisation and replication of the data. A conflict viewer lets you see any cases where more than one change has been made to a record. Access 2000 has a choice of looking for conflicts at row or column level - essentially, the older-style row-level tracking worries if two people have made changes to the same record. Column-level tracking only reports problems if two or more people have changed data in the same column in the same record. On a larger scale, you can set up replica priorities to say whose changes should be used if there's a synchronisation conflict, and you can set a deletion prevention option to stop your users ditching important data.

Access 2000 has some good facilities for the beginner, but most of the improvements will be experienced by power users and developers, especially users who want to work with SQL data.

DEVELOPER FACILITIES
There are two aspects to the developer facilities in Office 2000. First of all, there are those that you get whichever version of Office you choose, courtesy of Visual Basic for Applications and the forms designer in Outlook. If you're looking on a more professional basis, however, you can go for Office Premium Edition. This has extras, such as a Com

ADD-IN DESIGNER, ERROR-HANDLING AND SUPPORT FOR
Visual SourceSafe.

Many developers will be working with the Standard or Professional editions of Office, however, so what are the improvements on offer to those users? By now you should be able to guess that the changes start with new objects added to VBA for handling the Web; you can find out and set who is the author, work w
ith e-mail messages and Web pages. There are also facilities for handling multiple languages, so your macros and modules can deal just as transparently with your multi-lingual users as does the rest of Office. More general changes include support for modeless user forms, and better handling of ActiveX controls. ADO support has been added to Excel and Access, with the intention that you use it in preference to the old DAO and RDO data formats. You can also work with OLAP data sources via OLE DB. VBA is more standard across the applications, although there are still some annoying differences between the versions.

One excellent addition is that of digital signatures on VBA projects, so you can pass your modules to other users who have security in place that requires checks on who created the code - Melissa has made many IT managers a tad nervous.

PREMIUM EDITION
If you need more than the basics of VBA, then Premium Edition is what you need. This has a COM add-in designer so you can create your own extensions to Office. As you might guess, VBA now supports COM add-ins, so your work won't be wasted. The COM add-in will appear in Visual Basic as well as any other VBA host applications on your PC, so your wonderful COM objects would show up in Visio, for example. You can execute your Add-in code for testing and debugging without the need to build the DLL each time, and edit the code in the Add-in while running it, and run individual procedures without the need to restart the entire Add-in.

One of the greatest benefits to developing solutions with VBA is the ability to debug working code inside the host application. VBA 6.0 provides a new feature - Run Project Mode - to enable code to execute for debugging and testing, without requiring a DLL to be built.

Visual Sourcesafe is integrated in Premium Edition, so you can check code in and out when working with other developers, and there's a code librarian for modules and code sn
ippets you want to re-use. It comes with a set of code samples to get you started, and you can customise them or add your own as necessary.

There's a code commentator that adds structured headings to your procedures, telling other developers what's going on. The way it works is to merge a user-supplied template with the code, so it doesn't actually write the comments for you, it just makes the process a little more formalised.

An error handler has been added, so you can design and use a standard error handler for use throughout a project. There's an HTML Help Workshop that you can use to create Help systems written in HTML, complete with screenshots and graphics. You also get access to the Answer Wizard SDK, so you can write your own extensions to the Answer Wizard to handle users' requests for help about your application. Even more frightening, you get access to programming the Assistants via the Agent SDK. This means you can animate the character to look at the part of your application where the user should be paying attention, get them to ask questions of your users, and, in the words of the developer's guide, provide a social interface to your application so that users find it fun to use. Fun? Office applications shouldn't be fun. They'll want colour next.

DATA SUPPORT
As mentioned earlier, Universal Data Access support means you can use OLE DB and ODBC, via ADO. New in the Premium Edition are a Data Environment Designer and a Data Report Designer. The Data Environment Designer gives you a design time interface to your ADO-based COM objects, and can be used to visually create your data access components - no need to get to grips with the complexities of SQL. You can use the Data Environment Designer to define hierarchical views showing how your database objects fit in terms of parent-child relations, grouped recordsets and so on, thanks to the FlexiGrid control that's integrated with the Data En
vironment Designer.

The Data Report Designer lets you create reports outside Access, based on the fields from the Data Environment Object. You can drag and drop data fields or entire recordsets to create the report, and use code to manage formatting, calculations and so on if you want a closer level of control. You can also make use of Data-bound ActiveX controls to display your hierarchical data from the Data Environment Designer on the screen. There's also a new Data Repeater control that gives a simple way to repeat any data-aware ActiveX controls so your user has an easy way to scroll through records and data, no matter what control is used to display them.

Overall, the Developer version gives you simpler control over your data sources, but more importantly, the facilities to create really polished, professional-looking applications. Well worth the money for the time and trouble it'll save you in user support.

Summary: