Upsizing to Use an Access Data Project

In this section, you take a look at Access Data Projects (ADPs). Create A New Access Client/Server Application to generate a new ADP. If you choose this option, you not only create a new database, but the Upsizing Wizard performs a lot of additional processing. Constructing an ADP means that you will no longer have any queries left in Access; these queries will need translation into SQL Server Objects (a process with which the wizard can assist you).

An ADP is a radical departure for Access in some respects because you do not use the ACE database engine. This means that you can no longer have local tables or queries or utilize the Data Access Objects (DAO) model. Instead of DAO, you use an alternative light-weight technology called ActiveX Data Objects (ADO). The database window becomes a window into SQL Server (it's a bit like a "lite" version of the SQL Server Management Studio inside Access).

Once you have converted to produce an ADP, go into a query in Design for a View.

It is interesting to note that the wizard chose to build a function for Current Product List. This is because the SQL contained an ORDER BY clause, which is only allowed in a View when you use a special TOP syntax (you will see more about this when we look at SSMA). This illustrates an interesting strategic difference between SSMA and the Upsizing Wizard. What is even more interesting is that if you open a function, such as Current Product List, you will find that it is updateable. This is quite radical, because in an Access database (an .accdb or .mdb), you cannot link to a function, and in Management Studio, you cannot open a function and edit the data; in the background there are some very clever things going on inside an ADP.

Query Conversion

Because you can use Access to write SQL in an infinitely-varied number of forms with a subtle interplay of VBA code, it is unlikely that you will ever get a 100% conversion, but it will have made a good attempt at translating your SQL. Sometimes you will find a query like Product Sales For 1997, which at first glance appears to have been converted, in fact has not been effectively converted, because it fails when you try to open the query. In this case, the problem can be fixed very easily because the syntax 'Qtr ' + datepart(q,ShippedDate) will fail, you would need 'Qtr ' + CONVERT(VARCHAR, datepart(q,ShippedDate)) to make this work; sometimes you need a lot more reworking on the SQL. Also note that the translation of SQL in crosstab queries is not supported (the equivalent in T-SQL is the PIVOT statement).

An ADP also provides integration for developing SQL Server-specific objects.

ADP strengths and weaknesses
In general, ADPs are not the most common choice when converting a database, but they have a following, and if Microsoft continues to enhance ADP, maybe it will become the preferred path. An ADP can offer better performance, because it ensures that you create your equivalent queries in SQL Server. This can also be a weakness because in any conversion you are forced to spend time possibly fixing a large number of queries, but with a standard Access database, you can focus on only converting those queries with poor performance. You also need to judge the impact on your productivity as a result of losing the ability to have local tables and local queries in any existing application.