Access 2000 lets developers build SQL Server solutions from a familiar database container interface
Developing SQL Server databases with Access 2000 offers you the optimal combination of power, fast results, and minimal costs—a solution that will satisfy the end users, CEO, and CIO in your organization. With Access 2000, Microsoft added SQL Server data-definition capabilities to its popular desktop database manager.
Access 2000 lets developers build SQL Server solutions from a familiar database container interface. And non-SQL Server developers can tap the power of SQL Server with the ease of Access. Also, Access applications built for SQL Server inherit the hardware scalability of Microsoft's enterprise database solution. Access 2000 ships with the Jet database engine and the new Microsoft Database Engine (MSDE). Because MSDE employs the same core database technology as SQL Server 7.0, Access developers can easily migrate solutions from small workgroups to departments and enterprises.
To illustrate the differences between the new Access project and a traditional Access database file, I'll use detailed examples to demonstrate how to create SQL Server databases and tables from Access 2000. Also, I'll demonstrate how to construct stored procedures with familiar Transact SQL (T-SQL) statements inside Access projects.
What's an Access Project?
In Access 2000, Microsoft introduced a new file type (.adp) to manage databases. An Access project works with three types of database servers, SQL Server 6.5 with Service Pack 5 (SP5) installed, SQL Server 7.0, and MSDE running on Windows 9x or Windows NT 4.0. The .adp file extension denotes an Access project. This file works natively with SQL Server, as an Access database file (.mdb) works with the Jet database engine. Traditional Microsoft Access applications can consist of only one .mdb file because .mdb files can store database objects, such as tables and queries, and application objects, such as forms and reports. Applications that rely on Access projects always segment the database objects from application objects. Access stores the application's forms and modules separately from the database tables they use. SQL Server manages the database objects, but developers manage application objects within the .adp file.
Screen 1 and Screen 2 compare the database containers' GUIs for Access project and Access database files. Screen 1 shows the traditional container user interface, and Screen 2 shows the new design of the container object for Access projects. Note that Microsoft removed two items from the container object for Access projects, Create table by using wizard and Create table by entering data. The remaining wizard, Create table in Design view, presents a slightly different grid layout control in Access projects from that in Access database files. SQL Server developers will find the Access project user interface familiar because it matches the Enterprise Manager's user interface. You can use Access project features, such as the ability to set and reset identity seed and increment values. This ability requires programming for Jet database files and it wasn't available in earlier versions of Access. The Access project database container shows four database objects—Tables, Views, Database Diagrams, and Stored Procedures—and five application objects. Developers can expose triggers for any table by right-clicking the Tables icon in the database container objects group and choosing Triggers from the context-sensitive menu that opens.
The Access project database container graphically underscores another Access project innovation. The container integrates database objects with application objects. Notice that the database container gives you access to collections of forms, reports, pages, macros, and modules. Users can invoke familiar wizards for creating forms and reports. However, users can now work with custom databases and user data- definition capabilities for SQL Server databases instead of databases created by others, which developers had to use before Access 2000. It's easy to create a simple bound form, highlight a table name in the Tables collection, and click the AutoForm tool on the Database toolbar. When you make changes to data displayed in a form, it revises the original source data table, which wasn't easy in prior versions of Access. You can easily modify this behavior by altering the Recordset Type property in the Data tab on the Property dialog box (available when you open a form in the Design view). Updateable Snapshot is the default setting. Changing the property to Snapshot converts the form to a read-only format.
Creating an Access Project
You can create a new Access project file two ways. First, you can create a new project with a new database, add new database objects to it, and import objects into the new database. Second, you can create a new Access project for an existing database. This approach lets you create or import a custom set of application objects that will work with the objects in an existing database.
To build a new database when you create a new Access project from the File menu, click the New icon, and choose the Project (New Database) icon in the New dialog box. The File New Database dialog box will open and present a default name that you can change. Screen 3 shows the SQL Server Database Wizard dialog box, which lets you enter a server name, login ID and password, and database name. Select any connected database server name in the combo box. For database servers residing on NT computers, you don't need to enter a login ID and password. If you don't enter this information, the wizard will default to trusting the NT login account of the current user and NT integrated security. For database servers running on Windows 9x computers, you must specify a login ID with an appropriate password. Clicking Next brings you to another dialog box, which lets you continue creating a database after you click Finish.
Instead of making a new database for a new .adp file, you can connect a new .adp file to an existing database. The Data Link Properties dialog box specifies a connection for the .adp file to connect with a specific database. The connection is exclusively for the use of the .adp file. To make this connection, launch the process by clicking the Project (Existing Database) icon in the New dialog box from the File Menu. Next, specify a custom file name for the Access project or accept the default. Then when you click Create, Access presents the Data Link Properties dialog box where you specify the database server name, type of security (NT integrated or SQL Server), and a database name. Click OK to connect the Access project to the existing database that you designated in the Data Link Properties dialog box.
Prev. page
[1]
2
next page 
