Data Access Technologies

Microsoft Access 2000 makes it easier than ever to work with Microsoft SQL Server databases. When working with an Access project file (ADP file), Access provides a variety of tools that you can use to work with SQL Server 7.0 and 6.5 databases. These tools allow you to work with existing databases, create entirely new databases, and to work with the design of database objects. Access also provides the Upsizing Wizard, which helps you convert an existing Access database into an Access project file connected to a SQL Server database.

By isolating all database files under the control of a database server, SQL Server can provide advanced features that cannot be furnished by the Jet database engine. For example:

Online backup    Use an automatic scheduler to back up your database without having to exclude users from the database.

Durable transactions    SQL Server logs transactions so that updates made within a transaction can always be recovered or rolled back if either the client or the server computer fails.

Reliability and data protection    If either a workstation or file server fails while an Access database (MDB file) is being written to, the database might be damaged. You can usually recover a damaged database by compacting and repairing the database, but you must have all users close the database before doing so. This rarely happens with a Microsoft SQL Server database.

Faster query processing    Because an Access database is a file-server system, it must load the Jet database engine locally to process queries on the client workstation. For large databases, this can involve moving a lot of data over the network. In contrast, SQL Server runs queries on the server, which is typically a much more powerful computer than client workstations. Running queries on the server increases the load on the server, but this can reduce the network traffic substantially — especially if the database application is designed so that users can select only a small subset of the data at a time.

Advanced hardware support    Uninterruptible power supplies, hot-swappable disk drives, and multiple processors can all be added to the server with no changes to the client workstations.

See also

For more information about Microsoft SQL Server, see the Microsoft SQL Server Web site and SQL Server Books Online, which is installed with SQL Server 7.0.