| Microsoft Office 2000/Visual Basic Programmer's Guide | |
In previous 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 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 that allows you to create a client application that connects to a SQL Server 6.5 (with Service Pack 5) or SQL Server 7.0 database through OLE DB without loading the Jet database engine. To do this, you create an Access project file, which is saved by using an .adp extension. An Access project can store forms, reports, macros, and 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.
Note Even though an Access project file uses an OLE DB connection to connect to a database, it can't use just any OLE DB provider to make this connection. It can only use the Microsoft OLE DB Provider for SQL Server, and can only connect to SQL Server 6.5 (with Service Pack 5) and SQL Server 7.0 databases. This is because the database creation and design tools in Access 2000 can only support SQL Server 6.5 or 7.0 databases.
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.
In addition to providing you with the ability to create and design client/server solutions from scratch, Access 2000 also includes the Upsizing Wizard, which allows you to convert an existing Access database to a client/server solution by creating a new SQL Server back-end database linked to an Access client front-end application.