Creating an Access Data Project

There are a few approaches you can take when working with SQL Server and Access. You can simply link to the SQL Server tables in the same way as you would link to an Access backend, or you can move completely into the world of ADPs. For this section, we are going to upsize the IceCream database directly to SQL Server 2000 using the Microsoft Access Upsizing Wizard.

Important 

Remember if you're using Microsoft Access Security, your Access Data Project file will be unable to use it. You will need to recreate your security system using SQL Server Security. However, if security is in place in the mdb file, ensure you log in as the Admin user in order to have the required permissions to upsize.

What's New in Access 2002 for ADPs?

It is really more a case of what's new in SQL Server 2000. Microsoft has made the process involved in upsizing your Access Database easier with this release. The following Access features are now supported in SQL Server:

Extended Properties

The following Access properties will now be upsized to SQL Server:

• Decimal Places

• Format

• Caption

• Row Source Type

• Row Source

• Column Headings

• List Rows

• Input Mask

• Limit to list

• Column Width

• List Widths

SQL Server 2000 now supports the Cascading Update and Delete features for relationships long used with Microsoft Access. Cascading Update permits you to change a Primary Key and have that change reflected in the related tables. Cascading Delete permits you to delete a record and have the database delete all related records.

Within Access itself there are some changes:

• The ability to build stored procedures, functions, and views graphically.

• You can no longer work with SQL Server security from within Microsoft Access, as was the case with Access 2000.

• Database Diagrammer is now available from within Access. This is a bit like the Access relationship window, but with a lot more functionality. The Database Diagrammer can be viewed as an application development tool. From within the Diagrammer we can build and amend tables, set relationships, build constraints, and even delete tables.