Many Microsoft Access developers are finding that they are required to work with databases other than Microsoft Access. The push to have corporate data made available via the Internet, Access databases growing beyond the limitations of Access, and greater numbers of users have all lead to a need to move to other Relational Databases. For many Access developers, this means SQL Server. We will look at SQL Server 2000 or Microsoft SQL Server Desktop Engine, formerly MSDE, which is provided free with Microsoft Office XP.
In this chapter we will introduce you to the Client-Server Model and using Microsoft Access to develop solutions that are based on SQL Server data storage either using the full version of SQL Server or the free version, SQL Server Desktop Engine. In this set up, Microsoft Access is used as the front-end to the database housed on SQL Server. It's worth bearing in mind that they are both the same database engine with some slight , but significant, differences. We will be looking at Access Data Projects, the Microsoft Access file type used to develop against SQL Server backends . We will also provide an overview of Stored Procedures, User Defined Functions, Views, and Transact SQL. In addition, we will also step through the Microsoft Access 2002 Upsizing Wizard in order to move the Ice Cream example database to SQL Server 2000.
As this is but a single chapter, we cannot cover all the information you will need, but we hope sufficient information is provided to get you started on the road to client-server development using Microsoft Access 2002 as your development platform.
All the examples and code shown in this chapter apply equally to both SQL Server 2000 and SQL Server Desktop.