L.0.0- Creating an Access 2000 Project

Creating an Access 2000 Project

Although SQL Server 7.0 is a great database, in it's MSDE form you don't get any good administration or query tools. This can seriously hinder development time, and it especially makes it hard to manipulate the database. Luckily, Access 2000 allows you to create an Access project that links into an MSDE (or SQL Server) database.

This works by having a normal Access database, with Forms, Tables, and so on, where the tables are actually links to the MSDE data. The data doesn't exist inside Access, so if you modify it, you're actually modifying the MSDE data. The great thing about this is that you get all of the Access table editing facilities.

Here's what you need to do to get this working:

1.    Start Access 2000. From the main dialog select the second option, titled Access database wizards, pages, and projects, and press the OK button.

2.    In the next dialog, make sure you have the General tab selected, click on the Project (Existing Database) option, and then click the OK button:

3.    The next dialog is the File New Database dialog, where you enter the name and location of the Access part of the database. This is stored as a file with a .adp extension.

Next you're shown the Data Link Properties dialog, allowing you to select the source of the data.

The server name (under section 1) should be the name of the machine where the MSDE or SQL Server is located. This will probably be the name of your local machine.

If you're using Integrated Security with your SQL Server, then you can select the appropriate option under section 2. For a local machine you can use the user name sa, with a blank password.

Finally, for section 3 you can select either the name of a database on the server, or you can attach a SQL data file (this is the MDF file, available from the Wrox Web site). Once you've entered the details you can press the OK button

4.    Access will now link all of the tables and queries from the database into the ADP file.

At this stage you have a fully functional Access database, with data coming from the MSDE or SQL Server. The only thing that's different is that instead of having a Queries Object, you have Views, Database Diagrams and Stored Procedures. This should make it clear that you're really not working with an Access database, but with SQL Server, since these are SQL Server items.