An Access project is one of the jewels that Microsoft slipped into Microsoft Access 2000. I liked it so much that I wrote a book on the topic (Professional SQL Server Development with Access 2000). This Daily Drill Down introduces Access projects to IT pros. It includes an overview of Access project benefits, how to use Access projects with different versions of SQL Server, and how to set up a new Access project. My next Daily Drill Down will include samples that illustrate how IT pros can make Access projects work for them.

Included in Access 2000 is a new file extension, .adp. This extension refers to an Access project, which is a new way to manage databases on SQL Servers. Briefly, while a SQL Server manages database objects, the .adp file lets developers manage application objects. Since Access 2000 contains a flood of new capabilities, Access projects never received the kind of visibility they deserve. IT pros can drastically short-circuit the development cycle for client/server solutions by taking advantage of Access projects. Access projects especially target SQL Server 7 and MSDE as back-end database servers, but, with appropriate fixes, you can use Access projects with SQL Server 6.5 and SQL Server 2000.

Benefits of Access projects
Access projects have many important benefits:

  • An Access project has the same look and feel as an Access database file. Access developers migrating to SQL Server solutions will feel right at home with the Access Database window that lets them view database objects, such as tables, along with application objects, such as forms and reports. In addition, you can use your VBA programming skills to build highly powerful and interactive solutions.
  • An Access project keeps SQL Server application development simple. Access projects do not support three-tier solutions—just client/server solutions. You can readily create forms bound to SQL Server data sources with a single click. You can use forms to browse, edit, add, and delete records from a SQL Server data source. Access can automatically pop up a prompt for values for forms based on input values.
  • Access projects tightly integrate both database objects and application objects to create a RAD (rapid application development) environment for building solutions. Access reports and forms can work closely with database objects, such as views. For example, an Access report can readily pass values based on form control values to a stored procedure serving as the record source for the report. You can also use Access reports to compensate for the fact that SQL Server views do not support ORDER BY clauses. Furthermore, you can specify sort columns without any Transact SQL (T-SQL) code.

Using Access projects with versions of SQL Server
Access projects work with SQL Server 6.5, 7, 2000, and MSDE. There are tradeoffs associated with each of these database servers.

Access 2000 was released just after SQL Server 7. In addition, the Microsoft Data Engine (MSDE) shipped initially with any version of Office 2000 that contained Access. Therefore, Access projects work with both SQL Server 7 and MSDE. Using SQL Server 7 offers a full-featured SQL Server. Using MSDE offers a database engine compatible with SQL Server 7, but you do not enjoy the same scalability and feature set associated with SQL Server 7. In exchange for accepting the limitations of MSDE, developers and small departments gain a free database engine with no requirement for client access licenses. As your application needs grow, you can readily upgrade from MSDE to a more full-featured SQL Server edition.

Access project features also work with SQL Server 6.5. For the database creation and design tools in Access projects to work with SQL Server 6.5, however, your server must have Service Pack 5 or later installed.

SQL Server 2000 was released well after the release of Access 2000. Therefore, selected new features in SQL Server 2000, such as XML functionality, are not directly accessible through the Access project user interface. In addition, you need to alter the shipping version in several ways to use the standard Access project features.

First, upgrade to the SR-1 version of Office 2000. This action permits selected designer tools in Access projects, such as those for creating and editing tables and views, to open with SQL Server 2000 databases. Registered Office 2000 users and organizations can download SR-1 for free, or order disks from the OfficeUpdate Web site. There is also an administrative version available for download.

Second, install the SQL Server 2000 client management tools, such as Enterprise Manager, on the workstation running Access 2000. This enables Access projects to save new and edited versions of tables, views, database diagrams, and stored procedures in SQL Server 2000 databases. The third fix is still in development as of the time that I write this article. It will appear as a patch at the OfficeUpdate site and will enable Access projects to create new SQL Server 2000 databases and the Access Upsizing wizard to work with SQL Server 2000. In the interim, developers can use T-SQL to create databases, and Data Transformation Services to move data from legacy Access database files to SQL Server.

Two ways to create or open an Access project
An Access project connects to a database managed by SQL Server or MSDE. There are two ways to create a new Access project: One way connects to a new database and the other way connects to an existing database. Once you build an application, you can send copies of the Access project to users so that they can connect to the database from their own copy of the Access project. The Access project file has an .adp extension in contrast to the .mdb extension for a traditional Access database file.

Connecting to a new database
You can jointly create a new Access project and SQL Server database with the following five steps:

  1. After clicking the Microsoft Access icon on the Windows Start menu, select Access Database Wizards, Pages, and Projects and click OK on the Microsoft Access dialog box.
  2. Select Project (New Database) on the General tab of the New dialog box and click OK.
  3. Designate a folder and a filename for your new Access project on the File New Database dialog box, and then click Create.
  4. On the first Microsoft SQL Server Database Wizard dialog box, enter your database server’s name and a legitimate login ID and password.
  5. Click Finish on the second Microsoft SQL Server Database Wizard dialog box. This launches the process for creating a new database and connecting the Access project to it.

You can also launch the creation of a new Access project and database from within an existing Access project or Access database file. Click the New control on the Database window toolbar. This opens the New dialog box and permits you to continue with step 2.

Figure A shows the process for creating a new Access project and database at step 4. The first Microsoft SQL Server Database Wizard dialog box appears within an Access window, along with the Database window. The database server has the name cabxli. The figure depicts the creation of a database with the sa login, but you are not restricted to this login. For the process to work successfully, the login must be a member of either the sysadmin role or the dbCreator role. These are the two SQL Server fixed server roles with permission to create and edit database designs. SQL Server automatically assigns the sa login to the sysadmin role. Notice the database name is TechRepublicProject1SQL. By default, the wizard assigns a name based on the Access project name with an SQL appendix. You can override this name if you choose, but this can cause your Access project and its associated database to have unrelated names.

Figure A
Enter your database server name, login ID, and password in the Microsoft SQL Server Database wizard.

The Database window for the TechRepublicProject1 Access project appears in the background. After the wizard completes the creation of the Access project and its associated database, the window will appear empty, like the one in Figure A. Database developers can add new tables, views, database diagrams, and stored procedures to the TechRepublicProject1SQL database by selecting an object class and clicking the New icon on the Database window. Creating application objects, such as forms and reports, works the same way. All application objects that you create reside with the TechRepublicProject1.adp file instead of the database server.

Access projects provide wizards and designers to simplify the creation of both server-side and client-side objects. In addition to creating objects from scratch, you can use File | Get External Data | Import to add tables, forms, reports, page links for data access pages, macros, and modules from other Access projects. You can copy the script for views and stored procedures from the databases associated with other projects through the Windows Clipboard.

Connecting to an existing database
You can also create a new Access project that connects to an existing database. You might create an Access project this way when you want to create a new set of application objects for an existing database. This approach also enables you to create an Access project with a subset of the forms, reports, and other application objects associated with another Access project. Use the following steps to make the new project:

  1. After clicking the Microsoft Access icon on the Windows Start menu, select Access Database Wizards, Pages, and Projects and click OK on the Microsoft Access dialog box.
  2. Select Project (Existing Database) on the General tab of the New dialog box and click OK.
  3. Designate a folder and a filename for your new Access project on the File New Database dialog box, and then click Create.
  4. On the Data Link Properties dialog box, you must provide three types of information to designate how and to which database your project connects. First, you have to specify a database server’s name. Second, you have to indicate a type of authentication for verifying the login. Third, you have to select a database name.
  5. Optionally, you can click the Test Connection control on the Data Link Properties dialog box. This will verify whether the settings made in step four are correct.
  6. Click OK on the Data Link Properties dialog box to open the new Access project with a connection to the database specified in step four. If the settings in step four are wrong, an error message indicates a possible source of the error.

Figure B depicts the settings for connecting a new Access project to the pubs database that ships with SQL Server. Note that the cursor is over the Test Connection control. I want to check the settings before attempting to connect the project to the pubs database on the cabxli database server.

Figure B
The Data Link Properties dialog box allows you to select a SQL Server and database, and test the connection.

Setting database logins
When you’re doing development work, it will often be convenient to log in as sa or another member of the sysadmin server role. This kind of login provides unlimited authority over the database server and any database on it. When you get ready to copy and distribute an Access project for typical users, you will want to create a new Access project with the same database server and database name, but with different authentication credentials. There are two options.

First, you can use Windows integrated security. This approach uses the Windows validation of a login for permission to work with a database. You still have to create a database login account that corresponds to the Windows login. In addition, you need to create a user account in the database that matches the database login to precisely define the permissions for the Windows login.

Second, you can create a separate login for the database server. Although this requires a separate login for the database server, you can cache the password with the Access project so that all a user has to do is open the Access project. The approach that is best for your organization will depend on the degree of local control that database administrators require, as well as integration between database and Windows administrators.

When you get ready to deploy a solution, you will typically not want to distribute Access projects with the sa login. You can reassign any existing Access project for use in deploying an application to users. Simply set the authentication to the type that you want. If you are using SQL Server authentication, then designate a SQL Server login and a password when you test your Access project. You can optionally cache the password for the login with the Access project file. This enables a user to log in without recalling the password for the SQL Server login. However, it also requires the user to secure the file to avoid unauthorized users from logging into the database server with the permissions for Access project login account.

Setting a specific user login
Figure C again shows the Data Link Properties dialog box for an Access project that connects to the pubs database on the cabxli password. This time, the login ID for the server is TRUser1. Asterisks obscure the password. If you click the Allow Saving Password check box, the Access project caches the password for TRUser1. Leaving the check box blank requires users to enter the password whenever they attempt to connect to the server with the TRUser1 password. The Access project user has whatever permissions the TRUser1 login and its associated user account have with the pubs database. Should you want to make any login changes, you can reopen the Data Link Properties dialog box for any existing Access Project by choosing File | Connection.

Figure C
Use Allow Saving Password to have Access project cache a password for a user.

Your new (empty) Access project
After clicking OK in the Data Link Properties dialog box, the new Access project opens with a connection to the pubs database. This project will have all the default tables, views, database diagrams, and stored procedures that ship with pubs, as well as any custom ones created previously. The new project will have no forms, reports, page links, macros, or modules, however. This is because these objects reside with the Access project, and you just created a new (empty) project.

Access projects represent a powerful tool for rapidly building client/server solutions for SQL Server databases. The user interface bears a strong resemblance to the traditional one for Access database files. This similarity will help Access developers migrate from building solutions with Jet to building larger, more powerful solutions with SQL Server. In addition, SQL Server DBAs and developers now have the ease and convenience of traditional Access database files for building SQL Server solutions. The combination of these trends vastly expands the number of potential uses for SQL Server throughout organizations.
The authors and editors have taken care in preparation of the content contained herein but make no expressed or implied warranty of any kind and assume no responsibility for errors or omissions. No liability is assumed for any damages. Always have a verified backup before making any changes.