Chapter 1: Understanding Microsoft Access 2000 Client/Server DevelopmentThis content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.
Applies To: Microsoft® Access 2000
Summary: This article is the first in a series of three articles about developing client/server solutions by using Microsoft Access 2000 project files and tools. This article provides an overview of the different multiuser application architectures that are available to Access 2000 developers, and provides an introduction to the features available in Access project files (.adp). It also describes Microsoft Data Engine (MSDE), discusses the advantages and costs of using Microsoft SQL Server or MSDE as a back-end database, and shows how to install the Northwind Traders sample Microsoft Access project (NorthwindCS.adp). (23 printed pages)
What Is an Access Client/Server Project?
What Is an Access 2000 Client/Server Solution?
Getting Started with Access Project Files
Access 2000 Client/Server Features and Differences
This is the first in a series of three articles that describe how to develop client/server solutions by using Microsoft® Access 2000. This article discusses the advantages of client/server development and how it differs from other types of Access development. It also provides an overview of new features in Access 2000 that make developing client/server solutions easier and more powerful than ever.
Later articles in this series will focus on the specific examples illustrated by the development of a new sample client/server application called State University, which is based on the scenario of a fictitious computer-training company that needs to track its courses, students, instructors, materials, and enrollment.
While it may not be obvious at first glance, Microsoft Access 2000 is actually two products in one:
- The latest and greatest version of a time-tested product for developing desktop and small workgroup database solutions that use .mdb files and the Microsoft Jet database engine to manage data.
- An all-new product for developing client front-end applications that use OLE DB native-mode access to connect to SQL Server 6.5, SQL Server 7.0, or Microsoft Data Engine (MSDE) to manage a back-end database. MSDE is a new, fully SQL Server 7.0 compatible data engine for building desktop, mobile, and shared solutions that easily migrate to SQL Server 7.0. I'll cover this in more detail later on.
The Access 2000 user interface and online documentation refer to these two types of database solution development by using two somewhat vague terms. The traditional mode of development that uses the .mdb file format and the Jet database engine is referred to as developing an "Access database," and the new front-end client mode of development that uses the new .adp file format to connect to SQL Server or MSDE data is referred to as developing an "Access project." The essential idea behind the name "Access project" is that an .adp file stores only project or application objects, such as forms and modules, not data objects, which must reside on a separate database server.
If you've worked with Access much in the past, you may be thinking that this new mode of developing client/server solutions doesn't sound much different from what is available in previous versions of Access. In previous versions of Access, you can create a client/server solution by creating a front-end .mdb file that uses linked tables (also known as "attached tables") that connect to tables on a database server by using an Open Database Connectivity (ODBC) driver such as the Microsoft SQL Server ODBC Driver. An .mdb-based client/server solution requires Access to load the Jet database engine and ODBC components to open the database and manage the linked tables, which creates additional memory overhead. Additionally, an .mdb with linked tables doesn't allow you to work with the design of the database other than through code or the SQL Server Enterprise Manager.
Although Access 2000 continues to support client/server solutions that use .mdb files with linked tables, it adds support for the new .adp file format and a data-access architecture that allows you to create a client application that connects to a SQL Server 6.5 (with Service Pack 5), SQL Server 7.0, or MSDE database through OLE DB components without loading the Jet database engine. Access project (.adp) files store only the forms, reports, links to data access pages, macros, and Visual Basic® for Applications (VBA) modules that make up the client portion of your solution. Access uses an OLE DB connection to display and work directly with the tables, views, stored procedures, and database diagrams that are stored in the SQL Server or MSDE back-end database. To create the forms, reports, and modules in an Access project, you use many of the same tools and wizards used to create these objects in standard Access databases. Access 2000 also provides a new set of visual tools that allow you to create and modify SQL Server or MSDE objects directly from the Access user interface. These features allow you to quickly develop powerful client/server solutions that work directly against a SQL Server or MSDE 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 use only the Microsoft OLE DB Provider for SQL Server (SQLOLEDB), and can connect only to SQL Server 6.5 (with Service Pack 5), SQL Server 7.0, or MSDE databases. However, you can work programmatically with other data sources by using other OLE DB providers from ActiveX® Data Objects (ADO) code running in an Access project.
Broadly speaking, a client/server solution is any application that has a "front-end" client component installed on each user's computer that works with data in a server component—a "back-end" database located on a network server. Using this broad definition, Microsoft Access provides a variety of ways to share databases between multiple users, each of which can be seen as some form of client/server solution. However, for the purposes of this series of articles, we'll use the term "Access client/server solution" to refer specifically to using Access project files (.adp) as the client component connected to Microsoft SQL Server or MSDE as the server component. Note that while it is still possible to use Access 2000 to develop client/server solutions that use linked tables in an .mdb file, this series of articles will focus on using Access projects for client/server solution development.
Access 2000 Multiuser Database Application Architectures
The following list helps clarify the distinctions between each of the four main ways that Microsoft Access provides for developing database solutions that are shared among multiple users on a network:
- File-server database solutions
The simplest form of file-server solution is implemented by copying an Access database (.mdb) up to a network share so that it can be opened and shared by multiple users. A more sophisticated approach is to create a "front-end/back-end solution" that uses a front-end Access database (.mdb) running on each user's computer that contains linked tables that connect to a back-end Access database (.mdb) on a network file server. A front-end/back-end solution is similar to a client/server solution in that it has both a client (front-end) component and a server (back-end) component, but it is important to note that the back-end component is simply an .mdb file residing on a network file server, and that the database engine that is managing communication with the back end actually consists of multiple copies of the Jet database engine, each of which is running from a client workstation.
Either kind of file-server solution is easy to implement by using Access tools and the Data Access Objects (DAO) or ADO programming models. However, there are some limitations to using a file-server solution:
- Although a file-server solution can handle up to 255 simultaneous users, if users of your solution will be frequently adding and updating data, an Access file-server solution is generally best for no more than 25 to 50 users.
- Maintaining a database on a file server is inherently less reliable than using a database server such as SQL Server, because the database file can become damaged or left in an inconsistent state if the user's workstation, the network connection, or the file server fails while data is being written.
- Regardless of which kind of file-server solution is being used, additional network traffic is created by multiple requests being sent from the database engine running on each user's workstation to the network file server.
- Database replication solutions
Database replication solutions are typically used to share an Access database (.mdb) among multiple users who may not be continuously connected to the network (such as laptop users), or who are connected over slow wide area network (WAN) connections. Database replication is the process of distributing only changed data or changes to the database's design between all copies of an Access database in different locations. Replication involves producing one or more copies, called replicas, of a single original database, called the Design Master. Together, the Design Master and its replicas are called a replica set. By performing a process called synchronization, changes to objects and data are distributed to all members of the replica set.
The currency of data in database replication solutions is limited by the frequency of synchronization and the speed of the connections between users. Database replication solutions are often used in situations where slow or intermittent connections are the rule. As a result, database replication works best when users are less likely to conflict with each other, such as mobile sales representatives who work with their own set of rows in an assigned region. Access/Jet replication solutions can also be used as a form of load balancing across a network, by placing members of a replica set on various file servers on a local area network (LAN).
- Web-based database solutions
Web-based solutions use one or more Web pages as the front-end client application connected to a shared Access or SQL Server database. There are a variety of ways to develop Web pages that work with data in a back-end database, such as developing Active Server Pages (.asp files), which you can create by using any text or scripting editor, or tools such as Microsoft Visual InterDev®. Access 2000 introduces a new form of Web page called a data access page that can connect to an Access or SQL Server database. Data access pages use the Microsoft Data Source Control (MSODSC) and other ActiveX controls that are part of a suite of controls call the Microsoft Office Web Components. These components can be installed by licensed users of Office 2000. Note that data access pages require Microsoft Internet Explorer 5 or later to run.
Web-based database solutions are a good choice for users who don't have Microsoft Access installed, or who don't require features available in Access, such as creating new queries or reports.
- Client/server database solutions
Client/server solutions use a client application developed by using an Access 2000 project file (.adp) that is distributed to each user so that he or she can access a SQL Server or MSDE database from his or her own computer. You can also use the Visual Basic programming environment and other Visual Studio® tools to create client/server database solutions that work with SQL Server or MSDE databases, but discussing how to implement client/server applications by using those tools is beyond the scope of this article. However, it is worth noting that server-side objects created in a SQL Server database by using Access 2000 can also be used from client/server applications developed by using other tools.
The primary advantage that distinguishes the client/server mode of solution development is that all data is under the control of a dedicated database server. The following section provides additional details about the advantages of using a database server such as SQL Server.
Database Server Advantages
The decision to use a client/server solution is driven primarily by two factors: scalability and reliability. If your solution needs to scale to serve more than 25 to 50 users, you should consider using a client/server solution instead of a file-server solution. A file-server database also has inherent reliability limitations because it is maintained as a file in the file system, which can easily become damaged if either the client or server computer (or the connection between them) fails during a transaction or other operation that writes to the database file. By isolating all database files under the control of a database server such as SQL Server, the client/server architecture can provide greater reliability and other advanced features that can't be furnished by the file-server architecture, such as the following:
- Online backup
When you are using a database server, you can use an automatic scheduler to back up your database without having to exclude users from the database.
- Durable transactions
SQL Server and MSDE log transactions so that updates made within a transaction can always be recovered or rolled back to the last consistent state if either the client or the server computer fails. Although the Microsoft Jet database engine and .mdb files also provide transactions, the transactions in .mdb files aren't managed by a separate transaction log and can fail without recovery if the database file becomes damaged.
- Better reliability and data protection
If either a workstation or file server fails while an .mdb file is being written to, the database may 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 server database such as Microsoft SQL Server or MSDE.
- Faster query processing
Using an .mdb file, regardless of where it is located, requires your solution to load the Jet database engine locally to process queries on the client. 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 the client workstations. Running queries on the server increases the load on the server more than would happen with an Access file-server solution, but it can reduce the network traffic substantially—especially if users are selecting a small subset of the data.
- 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.
- Integrated Windows NT security
Both SQL Server and MSDE support using Windows NT® security accounts to authenticate users who are logging on to a database. This means that, unlike security for Access/Jet databases, if users are already logging on to a Windows NT network, you can use their existing Windows NT security groups and accounts to define permissions in your database, as an alternative to creating and maintaining accounts on the database server yourself.
- Advanced server-side programming and support for business rules
Microsoft SQL Server and MSDE support a very feature-rich SQL dialect called Transact-SQL. Used in conjunction with features such as constraints and triggers, Transact-SQL allows you to define business and security rules on the server that are enforced equally among all users of the database. You can also use Transact-SQL to create stored procedures that run on your server to provide flexible and secure access to your data from any client.
To get the most from this article, and the rest of the articles in this series, you should have Access 2000 and the Northwind Traders sample Microsoft Access project (NorthwindCS.adp) installed. To use the NorthwindCS.adp file, you must also have access to a computer running Microsoft SQL Server 6.5 (with SQL Server Service Pack 5 or later), Microsoft SQL Server 7.0 (either the Small Business Server, Standard, Enterprise, or Desktop Edition), or Microsoft Data Engine (MSDE) 1.0.
What Is the Microsoft Data Engine?
Microsoft Data Engine (MSDE) is a new database engine that provides local data storage that is fully compatible with Microsoft SQL Server 7.0. You can also use MSDE to share data as a small workgroup database server. MSDE runs under Windows NT 4.0 or later and Windows® 95 or later. It is designed and optimized for use on smaller computer systems, such as a single-user computer or small-workgroup server.
MSDE doesn't limit the number of users who can connect to its database, but it is optimized for five users. For a larger numbers of users, you should use SQL Server 7.0. Databases created with MSDE are fully compatible with SQL Server 7.0 and support many of the features of SQL Server 7.0, including most Transact-SQL commands. Like SQL Server, MSDE logs transactions, which means that if anything should go wrong during a write to an MSDE database, such as a disk error, network failure, or power failure, MSDE will use its transaction log to revert to its last consistent state. This gives MSDE databases greater reliability than Microsoft Jet (.mdb) databases, which don't log transactions.
Because MSDE is based on the same database engine as SQL Server, most Access projects or client/server applications can run unchanged on either SQL Server or MSDE. However, unlike SQL Server 7.0, MSDE has a 2-gigabyte (GB) database size limit, supports no more than two processors for symmetric multiprocessing (SMP), and in a replicated database environment cannot be a replication publisher for transactional replication (although it can act as a replication subscriber for both transactional and merge replication, and as a replication publisher for merge replication).
Compared to using Access with a Microsoft Jet database (.mdb), using MSDE does require a faster processor and more memory. The minimum supported configuration for running MSDE is a Pentium 166 with 32 MB of RAM. However, MSDE does manage its memory usage dynamically, so it will allocate as much memory as it can effectively use, but will stop allocating memory and, if needed, give back memory to ensure that other applications have memory available. If your solution requires the minimum usage of memory resources, you should use Access with a Microsoft Jet database instead.
Consider using MSDE as a desktop database alternative to an Access (Jet) database in the following ways:
- As a small-workgroup server database. You can develop your solutions by using an Access project connected to MSDE, anticipating that your workgroup and its business requirements may grow over time, eventually needing the full functionality of SQL Server 7.0 running on a larger network server.
- As a replication subscriber in a SQL Server replicated database environment. For example, you may have mobile users working in the field who access and update MSDE on laptops or disconnected computers, but then periodically connect to the master SQL Server database to reconcile changes.
- To easily develop and test an Access project or client/server application on a personal computer or workstation. You would then modify the Access project connection information to connect to a SQL Server database on a remote server for final testing and deployment.
The simplest way to work with a database server that is compatible with Access 2000 project files is to install MSDE 1.0, which is included with Microsoft Office 2000 (all editions) as well as the stand-alone edition of Microsoft Access 2000. MSDE isn't installed as part of Office 2000 or Access 2000 setup; you have to run a separate setup program to install it. MSDE can be installed on any computer running Microsoft Windows 95 or later, or Microsoft Windows NT Workstation 4.0 or later. Before installing MSDE on Microsoft Windows NT Workstation 4.0, you should also have Service Pack 4 or later installed. Disk space requirements for MSDE vary depending on what required components are already installed. A typical installation requires about 55 MB of available disk space.
To install MSDE 1.0
- Insert Microsoft Office 2000 Disc 1 or the Microsoft Access 2000 disc in your CD-ROM drive.
- Open the \SQL\x86\Setup folder.
- Double-click Setupsql.exe. You should be able to use the default selections in the MSDE setup program; if you need to, you can reconfigure MSDE after installing it.
Once MSDE is installed on your computer, you may need to start the server component by using the SQL Server Service Manager. To do this, double-click the SQL Server Service Manager icon on the taskbar, make sure MSSQLServer is selected in the Services list, and then click Start/Continue. By default, MSDE starts the MSSQLServer service automatically on Windows NT 4.0 or later; you might want to automatically start MSDE on Windows 95 or later by selecting the Auto-start service when OS starts check box in the SQL Server Service Manager window. If the SQL Server Service Manager icon isn't available on the taskbar, you can start it from the Windows Start menu by pointing to Programs, then pointing to MSDE, and clicking Service Manager.
By default, security on MSDE consists of a single "system administrator" (sa) account which is assigned a blank password. If you haven't set a password for the sa account or reconfigured MSDE security by using the Database Security command (Tools menu, Security submenu) in Access 2000 to display the SQL Server Security dialog box, you can use this account to log on to the database, create database objects, and perform other operations. Microsoft Access Help and the Help button on the SQL Server Security dialog box provide only minimal information about SQL Server security. For detailed information about SQL Server security, see SQL Server Books Online. (Note that the material in SQL Server Books Online applies to MSDE as well as SQL Server, even though SQL Server Books Online only occasionally mentions MSDE.)
Note If you are installing MSDE on Windows 95 or 98, even though you are not prompted to do so, you must reboot your computer to make all installed components available.
Note Computers running Microsoft Windows 95 or Windows 98 can't accept incoming named pipes connections, which is the default net library used by many applications. If you install MSDE on one of these computers, clients trying to connect to the server by using named pipes must be redirected to use an alternate net library. For information about how to address this problem, see Knowledge Base article Q198977 at http://support.microsoft.com/support/kb/articles/q198/9/77.asp.
Note If you use the Transact SQLSELECT @@VERSIONstatement or run the xp_msver system stored procedure against MSDE 1.0, it will return the product name "Microsoft SQL Server" and version number 7.00.xxx. The release of MSDE 1.0 installed from the \SQL\x86\Setup folder of the Office 2000 or Access 2000 CD-ROM is version 7.00.623. It is recommended that you apply the latest Service Pack to receive the latest set of fixes to MSDE, which at the time of this writing is Service Pack 1. Applying Service Pack 1 updates MSDE to version 7.00.699. For information about downloading and installing Service Pack 1, see Knowledge Base article Q232570 at http://support.microsoft.com/support/kb/articles/q232/5/70.asp.
Installing MSDE also installs the Data Transformation Services Wizard, which allows you to import and export data by using OLE DB providers or ODBC drivers to transform and validate data. To run the Data Transformation Services Wizard after installing MSDE, click Programs, on the Start menu, point to MSDE, and then click Import and Export Data. You can also use the Data Transformation Services Wizard to transfer objects and data between MSDE and SQL Server 7.0. For example, if you use Access and MSDE to create a prototype of a client/server solution, you can use the Data Transformation Services (DTS) Wizard to transfer the back-end objects and data from MSDE to SQL Server 7.0. For an example of how to use the DTS Wizard, see "Importing Data with the Data Transformations Services Wizard" in "Chapter 2: Implementing Microsoft Access 2000 Client/Server Databases." For additional information about using the Data Transformation Services Wizard, see the DTS Import and Export Wizards Help file (Dtswiz70.chm), which is installed by default in your C:\Windows\Help folder.
Installing the Northwind Traders Sample Microsoft Access Project
To get a quick look at many of the features available in an Access project file client/server solution, you should install the Northwind Traders sample Microsoft Access project. The sample Access project isn't installed by default because it depends on the availability of a back-end server such as SQL Server 7.0 or MSDE. As long as you have a local or remote database server available and have sufficient permissions to create objects on that server, you can install and open the sample Access project by starting Access, clicking Open an existing file in the Startup dialog box, and then double-clicking Northwind SQL Project File in the list. If Northwind SQL Project File isn't available in the Startup dialog box, you can install it by rerunning Microsoft Office 2000 or Microsoft Access 2000 Setup as described in the following procedure.
To install the Northwind SQL Project File from Office 2000 or Access 2000 Setup
- In Control Panel, double-click Add/Remove Programs.
- On the Install/Uninstall tab, click Microsoft Office 2000 Professional, Microsoft Office 2000 Premium, or Microsoft Access 2000, and then click Add/Remove.
- In the Maintenance Mode dialog box, click Add or Remove Features.
- Under Microsoft Access for Windows, expand Sample Databases, set Northwind SQL Project File to Run from My Computer, and then click Update Now.
The Northwind SQL Project file is named NorthwindCS.adp, and is installed in the C:\Program Files\Microsoft\Office\Samples folder by default.
The first time you open the NorthwindCS.adp, code in the Startup module attempts to locate a copy of SQL Server or MSDE to use for the back-end database. Depending on which server you are using and whether it is installed locally, one of the following will occur:
- If you have SQL Server 7.0 running locally, the NorthwindCS.adp file attempts to log on to the server (using the sa account and a blank password) and automatically connects the project to the Northwind database (tables and other server-side objects) that is installed with SQL Server 7.0.
- If you have SQL Server 6.5 or MSDE running locally, you are asked if you want to install the sample database. Click Yes to create the database, tables, and other server-side objects required for the NorthwindCS.adp file on your database server. This will take a few minutes, because code in the Startup module uses ADO code to create a new database and then run the NorthwindCS.sql script file to create and populate server-side database objects.
- If you don't have a copy of SQL Server or MSDE running locally, the Data Link Properties dialog box is displayed so that you can select a remote computer that is running SQL Server or MSDE. Select a remote server, enter the information required to log on to the server (you may need to contact the server administrator for this information), and select any database on the server. If you select a computer running SQL Server 7.0, code in the Startup module will automatically connect to the back-end Northwind sample database, which is installed with SQL Server 7.0; otherwise, you will be prompted to create a new sample database.
Note The structure of the Northwind sample database that is installed with SQL Server 7.0 differs slightly from the one that is created from the code in the Startup module for MSDE and SQL Server 6.5. For example, the Northwind database installed with SQL Server 7.0 contains some additional tables and stored procedures. Additionally, the SQL Server 7.0 version of the sample database is named "Northwind," and the version of the sample database created for SQL Server 6.5 and MSDE is named "NorthwindCS." These differences don't affect the functioning of the Northwind SQL Server project file; however, you can change the behavior of the code in the Startup module so that it will also create the NorthwindCS database on SQL Server 7.0. To do this, open the Startup module in the NorthwindCS project file, and in the Declarations section, type the following line of code:
Public Const NwindSQL = ""
Important The first time you log on to the server to create the NorthwindCS back-end database objects, if you need to enter a SQL Server user name and password in the Data Link Properties dialog box to log on to the server, you must also select the Allow saving password check box. This is required for the code in the Startup module to successfully connect to the server and create the NorthwindCS sample database. After the NorthwindCS sample database is created, you can clear the Allow saving password check box if you want. This step is not required if you use Windows NT Integrated security to log on to the server.
Once you have the Northwind Traders sample Microsoft Access project installed and running, you can take a look at its features to get a better idea of how client/server solutions created with Access projects look and work. To read an online overview of how the objects in the sample Access project were created, click Show Me on the menu bar after opening it. Later articles in this series will discuss most of the development issues illustrated by the sample Access project and many others in the context of creating the new State University sample application.
This section provides an overview of the features and data access architecture of Access project files and highlights some of the differences from traditional Access/Jet development. The next two articles in this series will provide in-depth information and specific examples, as illustrated by the State University sample application.
Connecting to Data Sources
A Microsoft Access project file provides efficient, native-mode access to a Microsoft SQL Server or MSDE database through the OLE DB component architecture. OLE DB is a COM-based database architecture that implements efficient network and Internet access to many types of data sources, including relational data, mail files, flat files, and spreadsheets. In the OLE DB architecture, the application that accesses the data is called a data consumer (for example, Microsoft Access 2000 or a Microsoft Visual Basic 6.0 program), and the component that enables native access to the data is called a database provider (for example, the Microsoft OLE DB Provider for SQL Server or the Microsoft Jet 4.0 OLE DB Provider). Access project files utilize the Microsoft OLE DB Provider for SQL Server and other OLE DB components that are installed with Office 2000 or Access 2000 to connect to SQL Server and MSDE databases.
Microsoft Access project files can be connected to only the following data sources:
- Microsoft SQL Server 6.5 (with SQL Server Service Pack 5 or later)
- Microsoft SQL Server 7.0 (either the Small Business Server, Standard, Enterprise, or Desktop Edition)
- Microsoft Data Engine (MSDE) 1.0 (which is functionally compatible with SQL Server 7.0)
When an Access project is open in the Microsoft Access user interface, you can use the Connection command on the File menu to display the Data Link Properties dialog box (shown in Figure 1.1), which is used to define the connection information for the Access project.
Figure 1.1. Data Link Properties dialog box
Note The Attach a database file as a database name option in the Data Link Properties dialog box works only with the .mdf database file format used by SQL Server 7.0 and MSDE databases. An .mdf file is the single-file database format used by these servers. In SQL Server 7.0 and MSDE, databases reside on operating-system files instead of on SQL Server logical devices as was done in previous versions of SQL Server. By using .mdf files, SQL Server 7.0 and MSDE support the ability to move a database by moving, copying, or e-mailing the database files from one server to another and attaching them to the target server. Using the Attach a database file as a database name option actually performs two operations: (1) It attaches the .mdf file to the target server; and (2) It establishes a connection between your Access project file (.adp) and the newly attached database. For more information about the features of .mdf files, see SQL Server Books Online and search for CREATE DATABASE on the Index tab. For an example of attaching an .mdf to a server by using the Data Link Properties dialog box, see "Installing the Sample Application" in "Chapter 2: Implementing Microsoft Access 2000 Client/Server Databases."
The Data Link Properties dialog box is also displayed automatically when you create a new Access project that will be connected to an existing SQL Server or MSDE database. Information about how use the Data Link Properties dialog box when creating Access projects is described in the next section of this article.
Creating and Connecting Access Project Files
When you create a new Access project file, you can choose to connect it to an existing SQL Server or MSDE database, or you can run a wizard to create a new database on your server. If you are using a database server that is administered by someone else, you'll need to work with the database administrator to make sure you have sufficient security permissions to open, and possibly create, new databases and objects on the server. If you have installed MSDE as described earlier in this article, you can log on as the default sa account with a blank password to gain sufficient permissions to create databases and objects on the server.
When you click New on the File menu in Access 2000, the General tab displays the options shown in Figure 1.2.
Figure 1.2. New dialog box with General tab
The Project (Existing Database) and Project (New Database) icons can be used to create Access projects, as described in the following procedures.
Creating a new Access project and connecting it to an existing server database
To create and connect an Access project to an existing server database, you use the New command on the File menu, as described in the following procedure.
To create an Access project and connect it to an existing database
- On the File menu, click New.
- In the New dialog box, click the General tab, and then double-click Project (Existing Database).
- In the File New Database dialog box, specify a name and location for the project file, and then click Create.
- On the Connection tab in the Data Link Properties dialog box, enter a server name and the information necessary to log on to the server, and then select the database you want to connect to. To make sure that the information you specified works, click Test Connection. Click OK to save the connection information and open your new Access project file.
Note If you are running under Windows 95 or 98, to select a server you must type the exact server name in the Select or enter a server name box. If you are running under Windows NT 4.0 or later, you can either click the arrow on the right end of the box to display a list of all servers that are available in your second-tier domain, or you can type a server's name.If you are using a local installation of MSDE on your personal computer, the default server name is the network name of your computer (which you can view by opening Control Panel, double-clicking Network, and then clicking the Identification tab). Additionally, if you are running both Access 2000 and MSDE or SQL Server on the same computer, you can type (local) in the Select or enter a server name box to create or connect to databases on your local server; however, keep in mind that if you move the Access project file (.adp) to another computer, you must update the connection information to refer to the exact server name. If you haven't changed the default security settings for MSDE, you can log on by using the sa logon ID with a blank password.
When you are finished with this procedure, you will have a new Access project connected to the database you specified in step 4. In the Access Database window, you can view any tables, views, stored procedures, and database diagrams that were previously created for this database. No Access-specific application objects (forms, reports, data access pages, macros, or modules) will be available; you must create these objects yourself.Caution Do not confuse the table objects exposed in Access projects with the linked tables that can be created in .mdb files. The table objects in Access projects function as a direct interface with the tables in Microsoft SQL Server 6.5, 7.0, and MSDE databases. The table objects in Access projects have significant differences from linked tables. For instance, deleting a table in the Access project interface actually deletes the table on the server, and similarly, changing the design of tables and other server-side objects in the Access project interface makes direct changes to those objects on the server.
Creating a new server database and connecting a new Access project to it
To create an entirely new (empty) server database and connect a new Access project to it, you use the New command on the File menu as described in the following procedure.
To create a new SQL Server or MSDE database and connect an Access project to it
- On the File menu, click New.
- In the New dialog box, click the General tab, and then double-click Project (New Database).
- In the File New Database dialog box, specify a name and location for the project file, and then click Create. This will create a new Access project and then start the Microsoft SQL Server Database Wizard. This wizard isn't installed by default, so you may need to have your installation CD-ROM available.
- In the Microsoft SQL Server Database Wizard, enter the name of the server you would like to use, the logon ID and password of an account with CREATE DATABASE privileges on the server (you can omit logon information if the server is using Windows NT integrated security and you are currently logged on as an account that has CREATE DATABASE permissions), and the name of the new SQL Server database. Click Next.
Note See the note for step 4 in the previous procedure. The same restrictions and information apply to the Microsoft SQL Server Database Wizard.
- If you are creating a Microsoft SQL Server 6.5 database, enter the database device and size, the transaction log device and size, the database size, and the log size.
Note For information about how to specify this information, open Microsoft Access Help. In the Contents pane, open the "Working with Access Projects" book, then open the "Upsizing from an Access Database" book, and then open the "Enter database and transaction log devices and sizes" topic.
- Click Finish to create the new database.
When you are finished with this procedure, you will have a new Access project connected to an empty database on the server you specified in step 4. No database objects (tables, views, stored procedures, and database diagrams) or Access-specific application objects (forms, reports, data access pages, macros, or modules) will be visible in the Access Database window, since you must create all objects yourself when using this procedure. The next chapter, "Chapter 2: Implementing Microsoft Access 2000 Client/Server Databases," discusses this same procedure as it relates to the State University sample application.
Changing the connection information for an Access project
You can also use the Data Link Properties dialog box to redefine the connection information for an Access project file. You might do this to switch from a test to a production database or from a primary to a secondary server. To do this, you use the Connection command on the File menu, as described in the following procedure.
To change the connection information for an Access project
- Open the Access project you want to work with.
- On the File menu, click Connection.
- On the Connection tab in the Data Link Properties dialog box, enter a new server name and the information necessary to log on to that server, and then select the database you want to connect to. To make sure that the information you specified works, click Test Connection. Click OK to save the connection information.
Working with Server-Side Database Objects
Once you've created an Access project and successfully connected to a database on your server, you can create new database objects as well as modify existing objects. As shown in Figure 1.3, the Database window in Access projects exposes server-side SQL Server and MSDE database objects, some of which may be unfamiliar to you if you've never worked with SQL Server before.
Figure 1.3. Database window showing objects in NorthwindCS.adp
The following sections briefly describe some of the features of SQL Server and MSDE tables, views, stored procedures, and database diagrams, and discuss the significant differences between these and Access database objects. As long as you have sufficient permissions on the server you are working with, you can create and modify all of these objects from their corresponding Design views in the Access user interface. Considerably more detail and specific examples (as illustrated by the State University application) will be provided in the next two articles in this series.
When an Access project is open in Access 2000, table Design view (shown in Figure 1.4) provides a way to work with the server tables.
Figure 1.4. NorthwindCS.adp Products table in Design view with Properties dialog box displayed
Tables in SQL Server and MSDE databases perform the same essential functions as tables in .mdb databases, but they support a somewhat different set of data types and provide additional features, such as CHECK constraints and triggers.
For example, CHECK constraints function much like Access field or table validation rules by defining a logical expression that limits the values that can be entered into a column (field) or row (record). The expression that defines a CHECK constraint can refer to an individual column or to multiple columns in the same table, but can't refer to columns in other tables. However, unlike Access field or table validation rules, SQL Server CHECK constraints are all created at the table level (and are applied in the order of their creation) and don't allow you to define a ValidationText message. To define CHECK constraints, display a table in Design view, and then click the Properties button on the toolbar. For more information about constraints and how to use them, see "Creating Constraints on Tables" in "Chapter 2: Implementing Microsoft Access 2000 Client/Server Databases."
Triggers provide a more general-purpose mechanism for enforcing business rules and data integrity at the table level. Triggers are actions that are performed whenever a row in the table is added, updated, or deleted. Tables in Access databases have built-in features such as cascading deletions and updates that can perform similar functions to triggers, but no comparable feature that is as flexible and powerful. By using Transact-SQL code, triggers can perform functions such as cascading deletions and updates and can contain business rules that support customized messages and error handling, and data integrity rules that refer to columns across more than one table. To add a trigger to a table, right-click the table in the Database window, and then click Triggers. For more information about triggers and how to use them, see "Creating Triggers" in "Chapter 2: Implementing Microsoft Access 2000 Client/Server Databases."
Views and stored procedures
Much of the same functionality that is available from the single query object type in an Access database is divided between two types of objects in SQL Server and MSDE databases: views and stored procedures.
A view is a virtual table generated by a SQL SELECT statement whose definition is stored in the server database. Like Access database select queries, views can be treated as tables for most database operations, and any operations performed on views affect the data in the table or tables on which the view is based. Unlike Access select queries, you can't sort the rows in a view because they don't support the ORDER BY clause. Unlike other forms of Access database queries, views don't support bulk actions such as deleting, appending, and updating groups of rows. In SQL Server and MSDE databases, these actions and other functions are provided by stored procedures. In the Query Designer (shown in Figure 1.5), you create or modify views in Access projects by using visual tools that are similar to those used to create and modify select queries in .mdb files.
Figure 1.5. The Query Designer
The SQL pane at the bottom of the Query Designer lets you view the Transact-SQL commands generated for your view, or define views by entering Transact-SQL commands directly. For more information about views and how to use them, see "Additional Record Sources" in "Chapter 3: Building Microsoft Access 2000 Client/Server Applications."
A stored procedure is a precompiled collection of Transact-SQL statements and optional control-of-flow statements stored under a name and processed as a unit. Figure 1.6 displays Transact-SQL commands that define the Sales by Year stored procedure from the Northwind SQL Access project file.
Figure 1.6. Sales by Year Stored Procedure in Design view
A stored procedure can be executed with one call from an application, and allows user-declared variables, conditional execution, and other powerful programming features. Stored procedures can contain program flow statements, business rules, and queries against the database. They can accept arguments, and can return parameters, single or multiple result sets, and values. You can use stored procedures for any purpose for which you would use SQL statements, with these advantages:
- You can execute a series of SQL statements in a single stored procedure.
- You can reference other stored procedures from within your stored procedure, which can simplify a series of complex statements.
- The stored procedure is compiled on the server when it is created, so it executes faster than individual SQL statements do.
- You can execute external programs, such as an executable Visual Basic program.
Like the action queries you can create in Access, stored procedures support bulk actions, but unlike action queries, stored procedures can support a series of SQL statements and can contain much more complex logic and error handling. You can also use stored procedures to perform a broad array of administrative functions, such as reporting server-status information, sending mail messages, and managing security. Additionally, you can use stored procedures to create parameter queries, which can return multiple result sets. However, Access 2000 doesn't provide visual tools or wizards to create or modify stored procedures; you must work with stored procedures by entering Transact-SQL programming code and commands directly in stored procedure Design view.
In a SQL Server or MSDE database, the Database Diagram Designer performs the same function as the Relationships window for Access .mdb files. In other words, it provides a way to graphically define and view the relationships between tables in the database. A database diagram can either be a whole or partial picture of the structure of a database; it includes objects for tables, the columns (fields) they contain, and the relationships between the tables. Unlike the Relationships window for Access Jet databases, the Database Diagram Designer for a SQL Server or MSDE database can contain more than one database diagram, and you can also create new tables and modify existing tables directly from the Database Diagram Designer.
Figure 1.7 shows the Database Diagram Designer for the Northwind SQL Access project file.
Figure 1.7. The Database Diagram Designer for the Northwind SQL project file
Tip If you have an installation of SQL Server 7.0, you can also use tools such as the Enterprise Manager and the Query Analyzer to work with MSDE 1.0 databases running on other computers.
Working with Client-Side Application Objects
An Access project can contain the same five application objects as an .mdb file: forms, reports, links to data access pages, macros, and modules. The following sections provide a brief introduction to the features of each of these objects in an Access project. The remaining two articles in this series will provide in-depth information and specific examples, as illustrated by the State University sample application.
You can use most of the same design tools as those available for an Access database to work with forms, reports, data access pages, macros, and VBA modules in an Access project file. To create any of these objects, open the Database window, select the type of object you want to create, and then click New.
Forms and reports
In Access projects, you can create forms and reports from scratch by using Design view in much the same way you do in .mdb files. When you are creating a form or report in an Access project, you can set the form's or report's RecordSource property to tables, views, and any stored procedures or SQL statements that return a single set of rows. If the stored procedure used as the data source for a form or report requires input parameters, Access will automatically prompt for those values when the form or report is opened. If you want to specify the values, you can use the form's or report's InputParameters property to specify the source for the stored procedure's input parameters. For more information about input parameters and how to use them, see "Client/Server Form Properties" in "Chapter 3: Building Microsoft Access 2000 Client/Server Applications."
Access projects also provide the following wizards for creating forms or reports:
- The Form Wizard
- The AutoForm Wizard
- The Report Wizard
- The AutoReport Wizard
- The Label Wizard (reports)
Using these wizards is essentially the same as using the corresponding wizards in .mdb files.
Data access pages
A new feature of Access 2000 is data access pages, which are interactive Web pages connected to Access databases or SQL Server and MSDE databases. When users view a data access page in Microsoft Internet Explorer 5 or later, they can sort, filter, add, or change data, expand and collapse detail views, work with PivotTable lists, and chart the results of their changes.
Note Although the Pages object in the Access Database window allows you to create, modify, and open data access pages, the pages themselves aren't stored in your Access project or in the server database it is connected to. Each data access page is stored as a separate .htm file in the file system or on a Web server. A page object in the Database window is a shortcut (link) to a data access page's .htm file.
Data access pages support the same design tools for both Access databases and SQL Server and MSDE back-end databases: the data access page Design view, the Page Wizard, and the Microsoft Script Editor.
The Northwind SQL Access project file sample doesn't actually install sample data access pages; instead, the Show Me Help file explains how to import the data access pages from the standard Northwind Traders sample application (Northwind.mdb) and change each page's connection information to point to the NorthwindCS.adp server database.
Macros and modules
Macros in Access projects provide additional actions and action arguments to allow you to work with SQL Server and MSDE objects. Similarly, the following additional methods of the DoCmd object have been added to open Access project views, stored procedures, and database diagrams from VBA code: OpenView, OpenStoredProcedure, and OpenDiagram. In addition, some of the other methods of the DoCmd object have new behaviors and arguments to accommodate the differences in Access project objects. For more information about Access project-specific macro actions and methods of the DoCmd object, search Microsoft Access Visual Basic Reference Help for "OpenView Method," "OpenStoredProcedure Method," "OpenDiagram Method," and "DoCmd Object."
The same Visual Basic Editor is used in Access projects to create VBA code. Programming in Access client/server solutions is very similar to programming in Access database solutions. In most regards, the Access object model is the same regardless of whether you are working with an Access project or an Access database. To work directly with the objects stored in a SQL Server database from an Access project, you should use ADO code. For more information about ADO and how to use it, see "Data Access Programming" in "Chapter 3: Building Microsoft Access 2000 Client/Server Applications."
Working with Server Data in Access Projects
In order to produce the most scalable and efficient interaction with SQL Server databases, Access 2000 project files are designed to use an "updatable snapshot" model for interacting with the rows of data displayed in forms and datasheets. Unlike Access databases, which require multiple round trips to retrieve rows, an updatable snapshot makes a request for data that fills a client-side cache of rows in a single round trip to the server, thus resulting in better performance.
When you are working with updatable snapshots, you specify a maximum number of rows that can be contained in their client-side cache. For better performance, updatable snapshots asynchronously fill the cache to allow the user interface to display rows as soon as a sufficient number are available, and continue to fill the cache in the background so that no locks will be held at the server if the user leaves the form or datasheet open.
Users can manage these features by using the Cancel Query and Maximum Record Limit buttons (shown in Figure 1.8) that are added to the right end of the navigation buttons in datasheets and forms.
Figure 1.8. Navigation buttons showing the Cancel Query button and the Maximum Record Limit button
The Cancel Query button allows a user to stop downloading data from the server if the operation is taking too long. The Maximum Record Limit button displays a slider that allows the user to dynamically control the number of rows downloaded and cached in the updatable snapshot. By default, this value is 10,000 rows, but you can set a different default value for a form by using the MaxRecords property. You can set the default value for all new forms and datasheets by typing a value in the Default max records box on the Advanced tab of the Options dialog box (Tools menu). If you don't want these buttons available on forms, you can set the NavigationButtons property of a form to No.
Using Access 2000 project files in conjunction with MSDE provides you with all the tools you need to rapidly develop client/server solutions. Installing MSDE and the Northwind SQL Access project file should give you a good start for learning about these powerful new features. The next two articles in this series will provide step-by-step details about specific development issues, as illustrated by the new State University sample application. For additional resources that provide information about using Access projects for client/server development, see "Appendix 2: Additional Resources for Working with Microsoft Access 2000 Projects."
The names of companies, products, people, characters, and/or data mentioned herein are fictitious and are in no way intended to represent any real individual, company, product, or event, unless otherwise noted.