PROJECT FILE ARCHITECTURE

Before you create a new Project, you need to understand a few basics:

• The new OLE DB technology

• The new data link feature

• The new Microsoft Data Engine

• The primary objects available in a server database

Introducing OLE DB

OLE DB is a new set of data access utilities that expose data and database structures to your applications. In the past, you've used ODBC to link an Access application to non-Jet data. Although ODBC is still part of the Microsoft Office architecture, the new OLE DB components will be more important than ODBC to your present and future development efforts.

With OLE DB, Microsoft has defined a standardized architecture that provides a connectivity layer to various data types. OLE DB provides access to relational data like that in Jet and SQL Server databases. It also supports access to Microsoft Exchange data, flat files, spreadsheet data, and non-Microsoft server products.

Within OLE DB, there are three important terms to know:

• Data source— The storage location for the data records, such as a table, file, message, or document.

• Data consumer— The application that accesses data in the data source, such as an Access Project or C++ application.

• Provider— The OLE DB engine (essentially, a small program) that allows the data consumer to connect to the data source, such as the OLE DB Provider for SQL Server used by an Access Project.

In Access 2000, you don't need to know how to programmatically communicate with an OLE DB provider; instead, you use a Data Link object to provide data to your application. Of course, you can also write VBA code to retrieve and manipulate data—when you do this with ADO, you are using OLE DB via ADO.

Linking to Data

When you create an Access Project, the Project uses an OLE DB data link to bind data and objects in a SQL Server database to the Project. The OLE DB provider that you use to create a data link between an Access application and SQL Server 6.5 or 7.0 is installed with Access 2000.

You can also use OLE DB providers to work with data from VBA program code by using ADO as discussed in Chapter 6, “Introducing ActiveX Data Objects.”

Data Links and Access Projects

The data link between an Access Project and its SQL Server database is managed from the user interface. A new Connection option, added to the File menu in an Access Project, displays the Data Link Properties dialog box. It is here that you define a data link to the SQL Server data for your application (see Figure 25.1). An Access Project can use only SQL Server for the data and objects displayed in its Database window.

Access 2000 .mdb files are still built on the Jet database engine and the Data Access Objects (DAO) technology, which uses the Microsoft Jet OLE DB Provider.

In a Jet-based application, each table in the application may be local to the application database or linked from another database. The Data Link Properties dialog in an Access Project enables you to identify the SQL server that will provide the application with data and database objects.

As a result, there's no central way to manage all the links between the application and its data. In contrast, an Access Project binds to a single server database through a single connection—a data link. Thus, you can change the entire database for a Project through the Data Link Properties dialog.

Data Links and VBA Code

You can also use the new OLE DB data links by creating a data link disk file and using it from application code. Data link files, with a .udl file extension, can be used from an Access Project and a Jet-based application to define connection information for any OLE DB provider.

To create a data link file, follow these steps:

The value of encapsulating connection information in a data link file is similar to the value received from ODBC data sources. If you've worked with ODBC before, you know that you can use a data source to connect application code to a certain set of data, and later change the data source to point to different data without changing any code. Similarly, a data link file can simplify your development and coding efforts.

Assume that several remote warehouses each create a daily receiving information file as an Access .mdb and send it in email to corporate headquarters. Sally, the inventory control manager at headquarters, has an Access Project that validates the warehouse files and loads the data into a SQL server. The Access code to do this in the Project looks something like this:

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset

cnn.Open "File Name=C:WhseLoad.Udl"
rst.Open "Receipts", cnn, , , adCmdTable
' ...
' In a Do loop, each record is validated and added to a server table here
' ...

rst.Close
cnn.Close

Notice that no Access database is specified. Instead, a data link file is named to provide connection information to the code. The data link file Load.Udl was created to use the Jet 4.0 OLE DB provider, which works with Access database files. In the data link file, a database name is specified, but this can easily be changed from Explorer without changing any code or properties inside the Access application that contains the code. Each time the code runs, it looks in the data link file for connection information. Therefore, when Sally gets a file from a remote warehouse, she simply does this:

Notice from Sally's workflow that she can use the same chunk of code to talk to several different .mdb files in several different locations without changing any code; she changes only the connection information in the data link file. This approach to coding gives you significant flexibility when writing code to work with data that is not accessed via the default connection for a Project.

The Microsoft Data Engine

Much of your Access Project development can be done rapidly and portably, thanks to a new version of SQL Server that runs on your workstation. This version is the Microsoft Data Engine, or MSDE for short.

MSDE is a scaled-down version (but only barely so) of SQL Server 7.0, designed to run on a workstation. MSDE can be installed on Windows 9x and Windows NT workstations. Because MSDE is based on the same data engine as SQL Server, most Microsoft Access Projects or client/server applications run on either version unchanged.

Here are the limitations of MSDE as compared to a Windows NT Server–based SQL Server:

• A 2GB database size limit

• Supports Symmetrical Multiprocessing (SMP) for only two CPUs (only one CPU supported on Windows 9x)

• Different memory management model (MSDE attempts to minimize memory usage, which may reduce performance)

• Can't be a transaction replication publisher (but can be a subscriber)

• Doesn't support a few high-end features such as parallel queries and fail-over clustering

MSDE isn't installed as part of Access 2000 setup. You install it from the CD file SQLX86SetupSetupSQL.Exe after you complete Access/Office setup. On a Windows NT workstation, MSDE runs as a service and starts automatically when you log on. On a Windows 9x workstation, the SQL Server engine and Service Manager are started with shortcuts in the Startup program group. You should see a small server icon for the Service Manager on the Windows taskbar, which can open this icon to start SQL Server if it's not running after you complete setup. If you want SQL Server to start up when you log on, make sure that you select the Auto-Start Service When OS Starts option at the bottom of the dialog. Figure 25.5 shows the Service Manager dialog.

Objects on a SQL Server

At times, you can use an Access Project to connect to an existing SQL Server to create forms, reports, or code against the data. In other cases, you might need to create or enhance a SQL Server database from the Access Project. Figure 25.6 show the Database window in an Access Project; note that the list of objects you can work with varies from the list in a Jet-based application.

From an Access Project, you can work with the following SQL Server objects and tasks:

• Tables— SQL Server tables are much like Jet tables in that they have fields, properties, and relationships to other tables. You work with table designs and data directly from a Project's Database window.

• Views— A SQL Server view is what Access calls a query, but of a very limited variety. A SQL Server view joins tables and optionally applies criteria to the result set—it does not allow for actions (append, create table, delete, or update), does not sort the results, and does not support passed parameters. You work with view designs and data directly from a Project's Database window.

• Database Diagrams— A SQL Server database diagram is more than a visual representation of a database's structure—you can create and modify tables, columns, and their attributes by working with the diagram. You work with diagrams directly from a Project's Database window.

• Stored procedures— These scripts contain program code and database instructions. Think of a stored procedure as compiled code that runs as fast as possible because it's stored in the database with the objects it affects and not in your application. You work with stored procedures directly from a Project's Database window.

• Triggers— A trigger is a stored procedure attached to a table-level event and defined to run automatically when a record in its table is modified. Triggers can be defined to run when table data is deleted, inserted, or updated, and are used to apply validation rules or maintain data integrity. Triggers are created and managed by choosing the Tables object in the Database window and then selecting Triggers from a table's shortcut menu.

• Replication— You can establish and maintain replication settings that enable the SQL Server database to publish or subscribe to other SQL Server databases. SQL Server 7.0 can also publish data to Jet 4.0 tables. Replication is managed by choosing Replication from the Tools menu.

• Security— You can create and maintain a security model for the SQL Server database objects and data by choosing Security from the Tools menu.