In the beginning there was a file based database product called MS Access. It had a terrific front end. Database administrators did not take it seriously, however, since it was small fry for them. SQL and Oracle databases were "the real stuff." But the real stuff was expensive, and did not have a front-end to speak of. For a while, you could use DTS to get data in and out of anywhere, including MS Access and SQL, and it was not a big deal. With an MS Access Project, you have a new construct. You may have data in SQL Server, but you can transparently see it in an Access database. It may have a few limitations, but it works well. This tutorial is about this new construct, available in MS Access 2000 onwards.
MS Access is a file based database product with a very user friendly front-end. Some of the reasons why one may prefer SQL Server, Oracle, or others is because of considerations regarding the sizes of the databases, security, extensibility, stability, number of connected clients, and so on. However, MS Access has an extremely user friendly programming model, whether it is GUI based or VBA based. It is also relatively inexpensive, bundled with the Office products from the very beginning. For these reasons, it has been used by many small companies, as well as many departments in bigger corporations. Many work arounds have been implemented in the past to have a client/server experience with this product, keeping data separate from business objects such as queries, forms, reports, and so on.
What is an MS Access Project?
Microsoft Access Project gives you access to data stored on SQL Servers via familiar OLEDB technology. With an MS Access Project you can work in the client/server mode as easily as in the file server mode, which you could do with earlier (prior 20 Access 2000) versions. With the Access Project construct, Microsoft has extended the playground of MS Access to the RDBMS arena. The data is all stored in the SQL Server (or, its junior version, MSDE), but the Access application will have access to this data by the OLEDB connectivity which can be implemented in the Access application (or by establishing a connection outside the application) as you will see in this tutorial.
These are some of the significant items to remember with regard to MS Access projects.
The Project file has an *.adp extension and not *.mdb, or *.mde.
The Project file does not have tables or queries. The SQL 2000 server stores tables and views.
SQL Server stored procedures replace Access's action queries.
The project connects to SQL Server by OLEDB and not by ODBC or Jet.
Instead of DAO (Data Access Objects) you will use ADO (Activex Data Objects)
This tutorial is about this new construct available in MS Access from MS Access version 2000 onwards.
Open the MS Access application from Start -> All Programs. This brings up the application and displays the following screen. The MS Access Project related items are shown bracketed in a red rectangle. You have two options, if you have created a Access project. For now, click on Project (New Data).
It immediately opens a File New database window, with the default location My Documents with a default file name, adp1.adp. You should change this to something meaningful, such as ProjAcc in this case.
This makes the MS Access application name change to the chosen name, and at the same time opens up the Microsoft SQL Server database Wizard (there are more wizards in MS than in the Harry Potter books). The wizard has already recognized the resident SQL 2000 Server, XPHTEK including the authentication information (use a trusted connection, namely Windows authentication) and it has even given a database name, which is the Project name concatenated with "SQL" as shown.
Click Next and then click Finish.
The MS Access application screen has changed. Instead of being disconnected, it is now connected. Compare the two screens. Now it displays ProjAcc: Project-ProjAccSQL (Access 2000 Format).
If you now open SQL 2000 Server's Enterprise Manager and look for the database ProjAccSQL, you will sure enough see a complete SQL database with all the objects as seen in the next picture.
With the database created, it is now possible to add in data. In this tutorial, we shall see how we can link to an already existing SQL database so that we can use that data in the Access Project.
Linking with Data
You can link to existing data by going to the File -->Get External Data--> Link Tables.... In this tutorial you will link to the tables, and therefore you choose Link Tables.
This action wakes up another wizard, the Linked Table Wizard. The wizard helps you with linking to the tables on the database, using an OLEDB connection. Here you have two choices. The radio button Linked Server choice gives the most functionality, and allows storing the connection information on the SQL Server. For the tables that need to be used (or linked to), views will be created on the SQL Server. Make sure you read the information provided on this screen. By choosing the Linked Server, it may even be possible to update data on the server, if the OLEDB supports updating. The choice Transact SQL provides a read-only connection. After the choice, click Next.
This opens up the Select Data Source which allows you to browse for an existing connection from a listed source of existing connections, My Data Sources. Here you will make a new connection by clicking on the New Source... button as shown in the next picture.
This brings up the Data Connection Wizard as shown. Here you will see a list of all sources that support the OLEDB connectivity, such as OLAP Server, Oracle, and so on. Choose the Microsoft SQL Server and click Next.
You need to provide the connection information to the server by filling out the needed information, namely, the server name and authentication information. If you choose the SQL Server authentication, you should provide User Name and Password. After this you click Next to access the next step of the wizard.
The next step is to choose the database and table. The wizard automatically opens up with the default database, master, as shown.
Selecting the Database
However, you can choose any other existing databases on the SQL Server. All the databases can be accessed from the drop down. Here the Northwind database is chosen as well as the the table Product Sales for 1997. Since this is just the connection step to the database, the table selection at this point does not really matter as you will shortly see. You may now click on Next.
This bring us to the next step, where the connection information is saved. The filename of this connection is the Server name, concatenated with the table you chose in the Database server connection step earlier, as seen in this screen. You may add a short description to this to help your future searches for a connection. You may now click Finish.
This brings you to this final screen in the Select Data Source screen. The connection file will also be saved in your My Data Sources folder.You may now click on Open to enter the next step, choosing the tables.
The next step after the above is to follow faithfully the Linked Table Wizard. If you have used MS Access before, this should be a familiar interface. On the left you see a list of Available Tables:, and you may choose as many as you want and click on the >. This will be transferred to the right, to Link to Project:. You may also choose all the tables by clicking on >>.
In the present case, three tables and a view are chosen to be linked as shown.
If you click Next to the above screen, you will see the following screen. Now the wizard has everything to link your Access Project to the SQL Server tables by creating views on the SQL Server for the tables you have chosen.
By clicking Finish, you are back in the Access Project. Now you have all the tables and the view you have chosen in the form of Queries.
Voila! the data is linked.
Double click the localhost_Northwind.dbo.Orders table to see the contents of this table as shown in the next picture. You can now work with these linked objects to create queries, reports, and so forth.
Here is a report generated using the Report wizard in the Access Project using the data in the imported View.
Now open up your SQL Server's Enterprise Manager and click on the databases node, followed by opening the ProjAccSQL database. You will see the following views in the Views node of this server as shown in this picture.
Summary
The tutorial provides a detailed introduction to creating a Microsoft Access Project starting from scratch. This is very useful for backend databases on MSDE which do not have a UI. MSDE is free for users having license for the Office suite (check for the version you need to have). The 2GB size of MSDE is large enough for smaller applications. The migration path from Access to SQL Server via this transition is worth considering, however remember that the SQL Server stores all queries as views or stored procedures. You may have to exercise restraint in creating queries as they may end up bloating the database. Also, the shift from DAO to ADO may make the developers learn to adapt to new language constructs. These will be considered in future tutorials.
Microsoft Access 
/ 37 
ASP.NET Export to Excel and Word using VB.NET and C#
