The Power of Two
Microsoft Office 2000 and SQL Server 7.0 are the new dream team for developers who build custom business solutions. Office 2000's new features let developers and power users integrate SQL Server data sources into powerful custom Office applications that you can easily deploy across the office.
Office 2000's new data-access capabilities—the introduction of ADO and the new Access .adp file type—make it nearly as easy for developers to connect with a SQL Server data source as with a Jet data source. The .adp file is an alternative to the .mdb file, the standard Access database file, but .adp links directly to a SQL Server data source through an OLE DB connection. Another Office 2000 innovation that developers can leverage is the Microsoft Data Engine (MSDE), which Microsoft introduced with Office 2000 Standard Edition. MSDE is the desktop version of the SQL Server database engine. An alternative to the Jet database engine, MSDE resembles a SQL Server service in ADO and Access Projects. (SQL Server runs as a Windows NT service on NT computers. MSDE and SQL Server Desktop can run on Windows 9x computers that don't support NT services; both database engines simulate the SQL Server service and two related services.)
ADO in Office 2000
Office 2000 ships with three ADO libraries, ADODB, ADOX, and JRO. These libraries let developers choose the ADO functionality that they will build into an application. Because the ADO libraries don't load automatically, developers can specify the type and amount of ADO functionality they want to make available through their applications.
ADODB and ADOX are generic ADO models that you can use with any database OLE DB provider. You use the ADODB library to develop database connections, perform database commands, and extract recordsets from data sources. The ADOX library enables data-definition and security functions. You can use the data-definition functions to programmatically create tables, procedures, and views with any database that implements the library's feature set. For example, you can use ADOX to add tables to Jet and SQL Server databases. The ADOX security features match the Jet security model. The third object model, JRO, exposes a programmatic interface to Jet-based replication. JRO works exclusively with the Jet database engine.
To use any ADO model in an application, your project must refer to a corresponding library because these libraries aren't built in. Screen 1 shows the References dialog for an example from this article. It includes references to the ADODB, ADOX, and JRO libraries. The ADODB library corresponds to Microsoft ActiveX Data Objects 2.1 Library. (Do not confuse this with Microsoft ActiveX Data Objects Recordset 2.1 Library, which is called ADOR in application code. The ADOR library has a reduced feature set compared with ADODB.) Screen 1 highlights the ADOX library entry. The JRO library entry follows the ADOX library entry. You can select entries in the References dialog to make ADO libraries available for your custom applications.
When you develop applications with Access, you can program the References collection to add an ADO library. Because users can manually add and delete references, your application can fail if it assumes availability of a reference that a user manually removed. Listing 1 demonstrates one approach to programming the References collection. First, the procedure searches the References collection for the ADODB library. Second, if the ADODB library is missing, the procedure adds the library from a file with the AddFromFile method. (Although the file appears to point at the 1.5 version of the ADODB library, it corresponds to the 2.1 version that ships with Office 2000. Microsoft controls the names of these files, and Microsoft retained the old file name, msado15.dll, for a new library, the 2.1 ADODB library.) This procedure works as-is with Access, but other Office components require a reference to the Microsoft Visual Basic for Applications Extensibility Library to run similar code. You need this reference because this library adds the References collection and Reference objects for the other Office components.
SQL Server with Access 2000
Although all Office components support ADO programming, Access 2000 is unique because it also enables a graphical link to remote data sources through Access Projects. This new Access file type, .adp, supports a direct link to a SQL Server service (SQL Server 6.5 and 7.0 and MSDE) via one OLE DB connection. Unlike the traditional links in .mdb files, Access Projects let you create, delete, and maintain databases and their objects. When you open Access, you can choose Project (New Database) to open the Microsoft SQL Server Database Wizard. This wizard creates a new SQL Server database for an Access Project. Using Access Projects with SQL Server offers developers two advantages: Access Projects integrate seamlessly with SQL Server services, and developers benefit from the ease of use and familiarity of the Access database window. You can readily bind forms, reports, and data access pages to tables, views, stored procedures, and ad hoc SQL query statements.
The left panel of Screen 2 shows the Data Link dialog for an Access Project connecting to the NorthwindCS database on the CAB2200 server. (The NorthwindCS database ships with Access 2000 and is a client/server version of the Northwind database that Microsoft shipped with earlier versions of Access. The Northwind database that Microsoft ships with SQL Server 7.0 is similar, but not identical, to the one that ships with Access 2000.) The right panel in Screen 2 presents the Tables collection for the .adp file that links to the NorthwindCS database. You open the Data Link dialog box by choosing the File, Connection command from the Database window menu. You can use this dialog box, which the left panel of Screen 2 shows, to set or reset the OLE DB connection between an Access Project and SQL Server database. Access Projects use the connection to link to the tables, views, database diagrams, and stored procedures in a database maintained by a SQL Server service. Forms, reports, pages, macros, and modules are stored locally in the .adp file for the Access Project.
In SQL Server Enterprise Manager, you can choose Tools, then Database Utilities or Security to expose commands for managing and securing the database associated with an Access Project. For example, you can choose Tools, Security, Database Security to open the SQL Server Login Properties dialog box in Enterprise Manager.
One of the strengths of Access Projects is their ability to integrate traditional Access forms with SQL Server databases. Screen 3 shows a pair of forms from an Access Project. The top form is a switchboard that offers three capabilities. The bottom form is bound to a local data cache based on a remote SQL Server data source. Although Access Project forms bind to a local cache, updates can automatically revise their remote data source. Operations such as finding an employee record perform faster because they operate against a private, local cache.
Listing 2 shows the ADO code that runs when a user clicks the Open frmEmployees button that Screen 3 shows. The code starts with a reference in a Dim statement to the Recordset object in the ADODB library. This reference generates a run-time error unless you either manually or programmatically reference the library. The recordset's Open method sets several essential property settings for a read/write form. You need to designate a keyset cursor type and pessimistic locking because the defaults are forward-only and read-only. These defaults deny write access and back-and-forth record navigation. Setting the cursor's location to adUseClient references the local data cache through the recordset.
Although the ADO commands will be new to you if you're migrating from earlier versions of Access, you'll still find some familiar objects in Access 2000. You can open a form with the OpenForm method for the DoCmd object. Then you can set the form's properties by identifying a member of the Forms collection. A new Recordset property lets you designate a form's record source. This property is richer than the old RecordsetClone property, which supported read-only access to the record source for a form. If your application calls for a read-only form, you can keep the syntax and settings in Listing 2. Set the form's RecordsetType property to 3 for a snapshot source. Then add this line, which can appear after you assign a value to the record source property:
Forms("formname").RecordsetType = 3 (or an integer constant set to 3)
By default, the property is an updateable snapshot (a value of 4) so that changes made to the local cache propagate automatically to the remote data source.
Prev. page
[1]
2
next page 
