Access 2000 for Access 97 users

• PC Specification
• Installation
• Conversion problems
  • Compilation Errors
  • Date Problems
  • ODBC Problems
  • DAO Code
• Differences and Problems in use
  • Help
  • Help Index
  • Object Display and new Objects (Pages and Groups
  • New Object Items
  • Object Navigation and Use
  • Changing to use Execmail and Netscape
  • Modules development
  • Table Analyzer
  • Form Properties
  • File Export
  • Publish to Web Wizard
  • Hyperlinks
• Useful New features
  • Save as Access 97 database
  • Printing Relationships
  • Year display
  • Subdatasheets and Subforms
  • Combo wizard includes find record
  • Conditional formatting
  • Control grouping on form
  • More Import formats
  • Drag and Drop to Excel"
  • Unicode
  • Compact database on Close and Repair
  • Groups
  • Report Snapshot
  • Row-level locking
  • Enhanced counters
  • Native OLEDB
  • new SQL syntax
• Not so useful New features (or don't work)
  • Adaptive Menus
  • Automatic change of fields in dabase objects when name change in table
  • Data Access Pages
  • SQL Server Integration
  • Projects
  • Enhanced conflict/error model

Access 2000 users can access Access 97 and 2 databases but cannot modify the design, only the data. Access 97 and 2 users cannot access an Access 2000 database. The solution for users with different versions of Access sharing the same database is to hold the data in the oldest version of Access, and then link to the tables from whichever version of Access is being used if they want to create their own queries etc. The Database Splitter wizard (found in Tools/ Database Utilities and only in Access 2000) will help to split the data from queries, forms, reports etc. by producing two mdb files- one for the data, one for the rest. One should obviously not convert and use the splitter unless one has kept the original version because the data will not be accessible to earlier version users of the Access database unless one exports as a comma delimiterd file (for Access 2 user) or uses the new Tools/ Database Utilities/Convert database (for Access 97 user).

Installing Access 2000 when Access 97 Is Already Installed

• Details about installation issues and problems of having Access 97 and Access 2000 on the same machine. It is far easier to install Access 2000 after Access 97 than vice versa

Changes in use of distinctrow

Compilation Errors

During the conversion of databases I had compile errors (in modules) but the rest of the objects worked as expected. The message was always can't find project or library. Since modules now take you straight into Visual Basic rather than Access Basic, the interface is different.
• Select Debug/Compile to find the errors.
• MsgBox Error (which is in each button code) caused a problem because you can not use the MsgBox action in Visual Basic. One has to use the MsgBox function. Replace MsgBox Error$ by MsgBox Err.Description. MsgBox styles also had to be corrected- eg ac by acritical
• My module:

  Function next_system_ourref()
      Dim Mydb As Database, Mytable As Recordset
      Set Mydb = DBEngine.Workspaces(0).Databases(0)
      Options = DB_DENYWRITE + DB_DENYREAD
      Set Mytable = Mydb.OpenRecordset("Our_refs", DB_OPEN_TABLE, Options)
      ourref = Mytable("system_our_ref")
      ourref = ourref + 1
      Mytable.Edit
      Mytable("system_our_ref") = ourref
      Mytable.Update
      Mytable.Close
      next_system_ourref = ourref
  End Function
  

  refused to compile- objecting to the open recordset line even trying out the new ADO methods of opening a database and table. The following worked in Access2000:

  Function next_system_ourref()
      Dim mydb As Connection
      Dim Mytable As Recordset
      Dim varposition As Variant
      Set mydb = CurrentProject.Connection
      Set Mytable = mydb.OpenRecordset("our_refs", db_opentable)
      ourref = Mytable("system_our_ref")
      ourref = ourref + 1
      Mytable.Edit
      Mytable("system_our_ref") = ourref
      Mytable.Update
      Mytable.Close
      next_system_ourref = ourref
  End Function
  

• Modules and procedures can not have the same name. To correctly cal a procedure that has the same name as a module, the code to call the procedure needs to be written Modulename.procedurename
• local variables had to be declared- eg dim fred as string

Date problems

May have problems using =Date() for a default value, gives an error message of #name?. Using =Now() still works but inconvenient as includes time. I have tried using the microsoft help and Technet but to no avail.I have checked I have the library file "Msofcf.dll".

ODBC Problems

ODBC must use old version of ctl3d32.dll and Oracle ODBC not Access Oracle ODBC ODBC driver notes

DAO Code

Access includes version 4.0 of the Jet database engine and DAO 3.6 (data access objects) object library. Versions of DAO code prior to 3.6 are no longer supported in Access 2000. Examples in the help section DAO Object Library Compatibility demonstrate how to convert code constructs in Access databases created with earlier versions of the Jet database engine and DAO object libraries. (DAO provides the objects, such as tables, queries, relationships, and indexes, that handle data-management tasks (creating and updating table and query structure, security, accessing external data sources) in an Access database.

Microsoft suggest that you use the ADO (ActiveX Data Objects) methods of accessing data in your current database for all new applications and future version compatibility. See Migrating from DAO to ADO. There is very little help on ADO, the user is told to buy The Microsoft Office 2000/Visual Basic Programmer's Guide

In Access 97, there was only DAO, so declaring an object as Recordset was unambiguous. In Access 2000 (and later) there are also ADO recordsets, and to make things even more complicated, new Access 2000 databases have an ADO reference set, but not a DAO reference. So your code was trying to work with the FindFirst method of an ADO recordset, and there is not one, you got an error. To fix this problem, you need to make your declarations unambiguous by declaring DAO objects such as databases and recordsets like this:

Dim dbs as DAO.Database 
Dim rst As DAO.Recordset 

Do this for all your code, using Search and Replace. You must also open the References dialog from the Tools menu in the VBE window, and check the DAO reference (the highest number available, probably either DAO 3.51 or DAO 3.60). You can uncheck the ADO reference if you do not need it, but if you declare your objects as DAO, this is not necessary, since they will not be ambiguous any more. Then compile your code and it should run.

ADO

Dim rst As New ADODB.Recordset
rst.Open "Contacts",CurrentProject.Connection,_

adOpenKeySet,adLockOptimistic

Debug.Print rst!AddNew


DAO

Dim dbs As Database, rst As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Contacts")
Debug.Print rst!AddNew

Help

Help does not match up with the menus eg to create a Web folder from within Microsoft Access, help tells the user that in the Open dialog box (File menu), Save As Data Access Page dialog box. The problem is that this option does not exist. Clicking on the link in the help section on data access page results in Internet Explorer scripting error:
mk:@MSITStore:C:\Program%20Files\Office2000\Office\1033\acmain9.chm::/html/pphowAddFolderToWebServer.htm
due to expecting an object!

Trying to find out more details about features was very hard since often the index showed details of a non-existant topic which then made help fall over or go back to the paper clip without doing anything. eg ANSI SQL showed topic "Comparison of Micosoft Jet Database Engine SQL and ANSI SQL", but it was totally impossible to view it

Another problem was that known new features such as enhanced counters, new functions such as ismissing did not appear in the help files

It was also impossible to get lists of functions, reserved words etc

The help file also included details of Excel worksheet functions- which were totally irrelevent since they were not accessible to Access

Help Index

The help index appears to have vanished so one has to use the annoying Office Assistant. Press the show icon in the assistant to show the index.

Database Objects Display and new Objects (Pages and Groups)

Database Objects displayed vertically as in Access 2.0 and options displayed horizontally. There are two major groups: Objects (default) and Groups. Clicking the Objects button shows all the familiar objects- Tables, Queries etc plus Pages (used to create new Data Access Pages).Clicking on the Groups button (with subgroup favourites) enables one to group objects

New Object Items

Extra items in Table, Query, Form and Report: create using design, and create using wizard. Warning- create using wizard does not display all the wizards available, it is better to press the New button to display all the options. They can be removed from view by
• select Tools/Options/View
• uncheck New object shortcuts

Object Navigation and Use

You can now move to the first object whose name matches the letters you type (similar to combo boxes). This makes it easier when there are alot of objects in the window.

You can also change the selection and opening behaviour from a double click opening to a single click

• select Tools/Options/View
• check Single-click open

Changing to use Execmail and Netscape

By default if you have a hyperlink in your data, the Access will open Microsoft Outlook if you have mailto:Joe.Bloggs, or Internet Explorer if you have a URL. To use the programs of your choice:
• in Execmail, select Tools/Set as default mailer
• Now goto Control Panel
• select Internet Options
• Select Programs
• Choose ExecMail for e-mail
• Choose Netscape for HTML editor

Modules development environment

Selecting Design for Modules goes into Visual Basic with a totally different interface (the office standard VBE- Visual Basic Environment)

Table Analyzer

Does not ask if you want to use the Table Analyzer wizard when importing

Form Properties

Properties may be seen when a form is open as well as in design view (but not table, query or report!). This is to facilitate changing the design of a form since properties can be changed when the form is being viewed

File Export

File/export is now a separate option for export to another application.

Publish to WEb wizard

File/save as html has vanished so harder to find Publish to web wizard. The reason for this is that one is expected to be using Windows 2000, so one can create a web folder. One has to File/export as html.
Creating web pages is now far more obscure than in Access 97

File Save as

File/Save as now asks whether the highlit table/query is to be saved as a copy of the table/query, a new form or new report; form as a copy of the form or a new report; macro as a copy of the macro or new module; module as a module or macro within the database. It does not ask what external format is wanted

Hyperlinks

Insert hyperlink modified to make it easier to find recently accessed files and URL and describe the link (by text to display). The title of the webpage will by default be the description of the link. It can be confusing to find a file because a list of files will only be shown if the filename field is empty.

Downwards database conversion

To convert an Access 2000 database to Access 97:
• select Tools/Database Utilities/Convert database/To Prior database version

Printing Relationships

You can now print a relationship diagram
• look at the relationships diagram
• select File/Print relationships

Year Display

To provide the option to show the year with 4 digits for all databases or just the current database
• select Tools/Options/General

Subdatasheets and Subforms

When looking at a table, Insert/subdatasheet offers the new option to look at related tables from tables and queries. For example,
• open the faculties table
• select Insert/subdatsheet
• choose the students table linked on faculty
  Inserting datasheet students makes a + appear before the first column of the faculties table. Clicking on the + sign displays the students belonging to that faculty

You still insert a subdatasheet into a form by adding a subform control but now one can specify a table or query as well as another form. This avoids having to create a subform before being able to add it to the form. The problem is that the rulers and borders take up quite alot of space and then you can not edit it unlike a saved subform.

Combo wizard includes find record

An extra option allows one to create a combo box on a form which can be used to find a record based on a value in a box. Unfortunately it only be used to find the first record based on selected value shown in the combo box and so does not apply a filter (which is why it asks for the combo box to display unique values).

Conditional Formatting

It is possible to quickly format fields based on the value of the data
• create a default faculties form based on the faculties table
• When in form design (also applies to report design)
• Select the faculty control
• Select Format/Conditional Formatting
• Select the fore-colour icon
• try the following condition:
  Field value is between 1 and 3
  Other options are: expression is and field has focus
• open the form and look at the faculty field for each faculty.

Control grouping on form

One can now group text boxes or other controls on a form or report mking it easier to make changes across all selected controls
• Open a form in Design view or a report in Design view.
• Select the controls you want to include in a group. (Select adjacent controls by drawing a box around them, select nonadjacent or overlapping controls by using the shift key)
• On the Format menu, click Group.
To create a group you must select more than one control.

More Import formats

can import Exchange, Outlook files but not Foxpro ="excel">

Drag and Drop to Excel

can export data from Access to Excel by dragging and dropping Access objects into a spreadsheet
• minimise Access
• open Excel and minimise so that both Access and Excel are visible
• from the main database window showing tables hold the left mouse onto a selected table and drop it into a spreadsheet

Unicode support

Access can now store and display multilingual characters in 30 languages, mathematical symbols and superscripts. There is also a dual-font support to display all the characters in your data. Will also allow multiple-language support in forms and reports. Multi-language support in just the one Access executable (used to be one executable per language).
• Start Character Map from Programs/Accessories
• Select Arial Unicode font
• Select subset Subscripts and Superscripts
• Select the characters you want to copy
• Copy the characters using the Copy button
• switch back to Access
• create a table called unicode with text fields language and specimen
• open the table and select Format/Font Arial Unicode MS for the specimen field
• type subscript in the language field and paste the subscript characters into the specimen field
• switch back to the character map program and select the greek subset
• select and copy some characters
• switch back to Access and paste the greek characters into a new record in the unicode table
• switch back to character map and look at other subsets such as Mathematical Operators and Hangul

If you did not install the Arial Unicode MS font when you installed Office 2000 or your Office program:
• reinstall Office using setup
• choose Add or Remove Features
• click the plus sign (+) next to Office Tools
• click the plus sign next to International Support
• click the icon next to Universal Font
• choose the installation option you want
• reinstall Office.
• Enable editing of multiple languages in Office programs. On the Windows Start menu
  • point to Programs
  • point to Office Tools
  • click Microsoft Office Language Settings
  • On the Enabled Languages tab, select the check boxes next to the languages you want

Compact on Close and Repair

Access 2000 automatically compresses a database when the file is closed if the reduction in disk space is significant. Repair has been combined with Compact
• Select Tools/Options/General Tick Compact on Close

Groups

One may want to group frequently accessed objects, or keep subforms together with forms. Click the Groups bar from the main database window. Right-click anywhere under the Groups bar. On the shortcut menu, click New Group. The data appears in collapsible hierarchical format when creating data access pages
• switch to the Database window.
• Click the Groups bar.
• Right-click anywhere under the Groups bar.
• On the shortcut menu, click New Group
• Type a name for your group in the New Group Name box.
• On the shortcut menu, point to Add to Group, and then click the name of the group you want to add the object to.
Adding an object to a group creates a shortcut to the object in that group, but it doesn't affect the object's actual location. For example, if you add a form to your group, and then click Forms under Objects in the Database window, that form still appears in the list of all forms.

You can also add an object or a shortcut to a group by dragging it from the Object list to the icon for the group you want to add it to.

Report Snapshot

You can view, print, mail, and electronically distribute reports created in Microsoft Access without needing a full or run-time Microsoft Access license.

There are two ways you can publish a report snapshot on the World Wide Web:

• Create a link to the report snapshot by using an HTML anchor tag on a Web page. For example: <A HREF="http://Website/NWTraders/catalog.snp">Catalog report</A> If you are using Internet Explorer version 3.0 or later as your browser, when you click on the link, Internet Explorer does an in-place activation of the report snapshot and Snapshot Viewer in the Internet Explorer window. Other browsers open the report snapshot in a separate Snapshot Viewer window. In either case, Snapshot Viewer must be installed on the same computer as the browser. Therefore, it is a good idea to provide an additional link on the Web page to the software download page for Snapshot Viewer. Users who do not have Snapshot Viewer installed on their computers can then download and install Snapshot Viewer to view the report.

  Embed the Snapshot Viewer control on the Web page by using the HTML Object tag. For example:
  <OBJECT ID="SnapshotViewer" WIDTH=640 HEIGHT=480
  CLASSID="CLSID:F0E42D60-368C-11D0-AD81-00A0C90DC8D9">
  <PARAM NAME="_ExtentX" VALUE="16722">
  <PARAM NAME="_ExtentY" VALUE="11774">
  <PARAM NAME="_Version" VALUE="65536">
  <PARAM NAME="SnapshotPath"
  VALUE="http://Website/NWTraders/catalog.snp">
  <PARAM NAME="Zoom" VALUE="0">
  <PARAM NAME="AllowContextMenu" VALUE="-1">
  <PARAM NAME="ShowNavigationButtons" VALUE="-1">
  </OBJECT>

  You can embed the Snapshot Viewer control by using the Microsoft ActiveX Control Pad application and set the control parameters by using the Control Pad Object Editor.

  You can use this method only with Internet Explorer 3.0 or later. In this case, if Snapshot Viewer is not installed on the same computer as the browser, Internet Explorer automatically downloads the Snapshot Viewer ActiveX control (but not the entire Snapshot Viewer program).

• Sending report snapshots in e-mail: you can send a report snapshot in e-mail by using Snapshot Viewer or Microsoft Access.
Snapshot Viewer is a program that you can use to view, print, and mail a snapshot, such as a report snapshot. Snapshot Viewer version 9.0 consists of a stand-alone executable program, a Snapshot Viewer control (Snapview.ocx), a help file, and other related files. By default, Snapshot Viewer is automatically installed by Microsoft Access 2000 the first time you create a report snapshot. You can also install Snapshot Viewer from the Setup program, or from a World Wide Web software download page located at the Microsoft Access Developer's Web site.

Row-level locking

One can specify the default record-locking strategy used in a multiuser environment in an Access database. (This was also available in Access 97 and in Access 2).
• On the Tools menu, click Options.
• Click the Advanced tab.
• In the Default Record Locking box, click the option you want.
Because an Access 2000 run-time application must use a Windows shortcut to start, it uses only page-level locking. This problem also occurs if the .mdb file is opened in Windows Explorer. To temporarily work around this problem, ensure that Microsoft Access uses the correct record or page locking setting. To do so:
• open Access first
• use either the "Open an existing file" option in the startup Microsoft Access dialog box or the Open command on the File menu to open the database.
  The only current workaround is to modify the locking options for the database as described earlier. Although the database is still opened in page-locking mode, using the "No locks" option reduces the amount of locking contention that occurs in multiuser solutions.

  Page-Level Locking vs. Record-Level Locking

  When a user edits a record in a shared database, you can prevent conflicts with other users by locking the data while it is being edited. When data is locked, any number of users can read it, but only one user can make changes to it.
  • In previous versions of the Jet database engine (version 3.5x and earlier), locking a record locks one page of data- a page is equal to 2K (2048 bytes) of data within the database file
  • for the current version, Jet 4.0, a page is 4K (4096 bytes) of data. (The size was doubled to accommodate storing data as Unicode characters, which occupy 2 bytes instead of the 1 byte used by previous characters.)
  • Locking at the page-level can lock multiple records if the combined length of two or more records is smaller than the size of a page; this prevents other users from editing any records on that page until the user is finished editing the record that caused the entire page to be locked. Page locking generally results in better performance, but can reduce the ability of other users to edit data.
  • A new feature of Jet 4.0 is the ability to lock individual records rather than pages. In Access, this is controlled by the Open databases using record-level locking setting on the Advanced tab of the Options dialog box (Tools menu). 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 (which is not possible when you use pessimistic locking under page-level locking). The locking mode that is in effect is determined by the first user to open a database. If the first user has the Open databases using record-level locking setting selected, all users who subsequently open that database will use record-level locking whether they have the setting selected or not. Conversely, if the first user to open a database has the Open databases using record-level locking setting cleared, all users who subsequently open that database will use page-level locking.
  • 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 DAO OpenRecordset method, and any 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.

Enhanced counters

Autonumber fields now support seed and increment

Native OLE DB

Open Database Connectivity (ODBC), is an international standard for manipulating relational data using SQL query syntax across disparate datasources. ODBC has the advantage of being an international standard that allows you to manipulate a vast array of relational datasources through numerous ODBC Drivers from both Microsoft and third party vendors. The key disadvantage of ODBC is that it is limited to relational, SQL-syntax based data.

OLE DB is Microsoft's strategic low-level interface to data across the organization. OLE DB is an open specification designed to build on the success of ODBC by providing an open standard for accessing all kinds of data. OLE DB imposes no specific limitation on either the query syntax, or the structure of the data exposed as long as it can be retrieved in tabular form. An OLE DB Data Provider is analogous to an ODBC Driver, exposing a DataSource to an OLE DB Consumer, such as ADO. An increasing variety of OLE DB Data Providers are being released by both Microsoft and third party vendors. Microsoft OLE DB Provider for ODBC Drivers enables you to expose any ODBC DataSource to an OLE DB consumer.

new SQL syntax

Optional ANSI SQL92 compliant SQL queries
• create new query
• close show table box without selecting any tables or queries
• On the Query menu, point to SQL Specific, and then click Data Definition. Enter the SQL statement. See Introduction to Access SQL. New SQL statements are as follows
• grant permissions eg grant select on faculties to fred
• revoke eg revoke select on faculties from fred
• decimal data type when using create table
• declarative referential integity eg
  CREATE TABLE Friends ([FriendID] integer references students(studentnumber), [LastName] text, [FirstName] text, [Birthdate] date, [Phone] text, [Notes] memo, CONSTRAINT [Index1] PRIMARY KEY ([FriendID]));

Adaptive Menus

As with other Office applications, this feature adapts Access menus to your usage patterns, so you only see menus you actually use. However, because some commands do not show on menus initially, you never try them out or forget they are there. It also makes support difficult since peoples' menus are different. Turn it off by
• Select Tools/Customise/Options Tick Menus show recently used commands first

Name Autocorrect

should automatically repair errors caused by renaming objects. To accomplish this, Access stores a unique identifier with each object created and name mapping information to allow Access to correct binding errors when they occur. When Access detects that an object has been changed it will perform a complete name fix. In previous versions of Access, queries, forms and reports would fall over if the name of a field or table was changed. I could not get this to work, whether it was a new Access 2000 database or a converted one. Even if it did work, it would not correct command buttons as it does not change Module code, or graphs and combo boxes as it does not change SQL.
• On the Tools menu, click Options.
• On the General tab, do any of the following (default none if converted, on if new Access 2000 database):
  • select the Track name AutoCorrect info check box for Access to maintain the information it needs to run Name AutoCorrect, but not take any action
  • select the Perform name AutoCorrect check box for Access to run Name AutoCorrect as name changes occur in the database
  • select the Log name AutoCorrect changes check box for Access to keep a log of the changes it makes each time it runs Name AutoCorrect. Access saves the changes in a table called Name AutoCorrect Log. Each change is a record in the Name AutoCorrect Log table.

Pages

A data access page is a special type of Web page designed for viewing and working with data from an Internet or intranet — data that is stored in an Access database or SQL Server database. The data access page may also include data from other sources, such as Excel. A toolbox is provided in the design environment to create controls which can be dragged and dropped at will.
• To view and work with the data access page on the Internet or an intranet, users need Internet Explorer 5 and a Microsoft Office 2000 license.
• Scripts are developed using MSE (Microsoft Scripting Editor) but no help could be found despite being told to select Tools/Macro/Microft SElect Editor which did not exist. XML is used.
• Data Access Pages do not have a rich set of design tools. There is a separate toolbar for each section of the form. There is no Undo. The wizard does not produce very attractive looking pages- so it requires alot of work in an unfriendly environment
• Opening up the page in Netscape 4.6 resulted in a blank page.
• Create by selecting the Pages button from the main database window. The page is a separate file that is stored outside Access. The problem is that the source of the database is stored in the htm file, so that the database needs to be stored on a web. Even then, one gets warnings that the page access data on another domain and you are told to add a secure web site to the trusted sites zone on the security tab of the Internet options tab on Internet Explorer. Consequently if one sends a database with Data Access Pages to another user one gets messages about the html page having been moved. This even happens when one opens the Northwinds database!
• When you create the file, Access automatically adds a shortcut to the file in the Database window. Designing a data access page is similar to designing forms and reports — you use a field list, the toolbox, controls, the Sorting and Grouping dialog box, and so on. However, there are some significant differences in the way that you design and interact with data access pages as opposed to forms and reports. How you design the page depends on what it will be used for:
  • Interactive reporting: This type of data access page is often used to consolidate and group information that is stored in the database, and then publish summaries of the data. While the data access page may also provide toolbar buttons for sorting and filtering the data, you can not edit data on this type of page.
  • Data entry: This type of data access page is used to view, add, and edit records.
  • Data analysis: This type of data access page may include a PivotTable list, similar to an Access PivotTable form or Excel PivotTable report, that lets you reorganize the data to analyze it in different ways. The page might contain a chart that you can use to analyze trends, detect patterns, and compare data in your database. Or the page might contain a spreadsheet in which you can enter and edit data, and use formulas to calculate as you do in Excel.

SQL Server integration features

Common SQL Server administrative tasks such as replication, backup and security built into Access 2000.

Access 2000 allows connection to SQL Server directly through OLE DB rather than linking tables via the Jet Engine (ODBC). One can use OLEDB to connect to another Access database (MSDE). One should use the standard jet engine for simple stand-alone databases and we use Oracle for backend databases when we need to rollback transactions, have more than 25 simultaneous users (preferably with only one user updating) but Oracle is harder to use

Projects

Only used with SQL Server. A new file type (.adp) connects directly to the integrated data store. Simpler to create client/server appications using the Access interface. New wizards have been provided

Enhanced conflict/error model

In Access 95 and 97, there were synchronization conflicts and synchronization errors. Synchronization conflicts occurred when two users updated the same record in two different databases in a replica set. Synchronizing the two databases would succeed, but only one of the two sets of changes would be applied to both databases. Synchronization errors occurred when a change to data in one database in a replica set could not be applied to another database in the replica set because it would violate referential integrity or some other constraint.

In Access 2000, the events that cause synchronization conflicts and errors are both viewed simply as synchronization conflicts. A single mechanism is used to record and resolve the conflicts, making the process much easier. Whenever a conflict occurs, a winning change is selected and applied in all replicas, and the losing change is recorded as a conflict at all replicas. The Conflict Viewer, the default tool in Access, is used to reconcile and resolve synchronization conflicts.