It has been the belief of many Microsoft Access developers for some time that Access was being repositioned by Microsoft as a development tool for SQL Server. This belief has been strongly reinforced by Access 20002 and the ability to work directly with SQL Server via Access Data Projects. Access now provides you with a super tool for working with the server.
Unlike an Access database, a SQL Server database contains two files. If you check out C:\Program Files\Microsoft SQL Server\MSSQL\Data once you have upsized the Ice Cream database, you will notice that there are two files, FileName.mdf and FileName.ldf .
Do not simply move these files to a new folder. Unlike Access it doesn't work like that and there are procedures to follow before you can move a SQL Server database file.
The mdf file is the actual database file used by SQL Server. The ldf file contains details of your actions on the database. The ldf file is the log file containing a record of all the transactions that have taken place on your database. As a result, its importance cannot be overstated. When backing up SQL Server, you backup both of these files. Again a detailed discussion of this process would fill a book, never mind a chapter, so it is outside the scope of this book. For a good solid grounding in SQL Server, see Beginning SQL Server 2000 Programming , published by Wrox Press (ISBN 1-86100-523-7).
Your best friend when moving to SQL Server will be the help files provided; known as Books Online, they provide a great deal of information and example files that will assist you in the move to this environment. Books Online is available on the SQL Server 2000 installation CD and is also available on the Internet at http://www.microsoft.com/sql/techinfo/productdoc/2000/default.asp . If you are installing the SQL Server Desktop Engine, then download Books Online as it is not available on the Office XP CD.
An ADP is a specific file type available with Access to enable you to develop front-end applications to data held on SQL Server. The ADP file itself stores user objects such as:
Data is held within SQL Server, together with any stored procedures, functions, and views, and all the management of the database, including security, is carried out on the server. It is worth noting that an ADP is only available when working with SQL Server. For those working with other Relational Database Management Systems, ADPs are not available. Just like Data Access Projects, we are dealing with a total Microsoft Solution.
SQL Server provides you with features, such as linked servers, to deal with data held in other RDBMS. A linked server allows you to connect to another database, for example Oracle, and use its tables as if they where part of SQL Server 2000. For more information see SQL Server Books Online available for download from www.microsoft.com/sql.
The interface to SQL Server 2000 has been greatly improved in this version of Access with graphical tools now available to enable you to work in a more 'Access-like' environment. For example, building a stored procedure is just as easy as building an Access Query. A stored procedure is precompiled SQL that is stored on the database server we will discuss these shortly. However, the ability to work with SQL Server security has been removed from the Access Interface, and must now be handled either via code, T- SQL, or using the SQL Server Enterprise Manager.
Transact or T-SQL is SQL Server's version of the SQL language. You will notice many differences, including the ability to use conditional logic within your SQL statements.
As can be seen from the figure, the interface to create a stored procedure is identical to that used to create a standard Access Query. The same interface is also used to create views and user defined functions. Stored Procedures, Views, and user defined functions are discussed later in this chapter. The single most important point to remember when working with Access Data Projects is that the Jet database engine is now no longer in the picture, and that you are now working with SQL Server.