databasedev.co.uk - database solutions and downloads for microsoft access

Understanding and working with Access Projects

An Access 2007 project is an .adp file (Access file) which provides a direct connection a Microsoft SQL Server database through the OLE DB component architecture. An Access 2007 project can be connected to a local and/or remove SQL Server Database or a local installation of the SQL Server Desktop engine.

In Access 2007 you can use the projects created with the older versions of the software and you can create new Access projects as well.

Advantages

Access Projects provide several advantages over linking to a SQL Server table or view. In an Access project you can make design changes to a SQL Server object and you can use, create, or edit other SQL Server features.

An Access Project does not contain any data or data-definition based objects. Instead, all this information is stored in a SQL Server database. However, working with an Access Project is similar to working with an Access database. Creating forms, reports, macros in an Access Project requires the same actions as when creating them in an Access database. But the interface used for working with projects is different than the one used for databases, however it’s just as friendly.

An Access Project also contains many of the wizards you can find when working in a database, such as the form wizard or the report wizard, which help you create a simple application, which you can later edit to create advanced options.

Access 2007 Projects

The process of creating an Access Project in Office 2007 is not as obvious as it was in the earlier versions. But you can still use those created in earlier versions as there are no file differences between versions.

Create an Access 2007 Project

Click the Microsoft Office Button, then click New.

If you want to create a new project in the default location, under Blank Database (right hand side of the screen) type the project’s name and the extension .adp, then click on Create.

Figure 1 Create new project on default location

If you want to create a new project in a different location, click the Browse button under Blank Database, browse the location, and then type the project’s name and choose "Microsoft Office Access Projects" from the drop down list.

Figure 2 Create a new project in a different location

The name will show up now under Blank Database and you can click Create. Access asks you if you want to an existing SQL Server database. Click "Yes" to continue or "No" to create a new SQL Database.

Figure 3 Click to create the project

Note: for the purpose of this tutorial, we skipped the next step in order to create a new SQL Database.

Create a new SQL Server Database

When the wizard opens input the following information: the SQL server computer, your log on information and the name of the SQL Server Database.

Figure 4 Create a new SQL Server Database

Note: You can skip the connection process and click Cancel. The project will still be created but it won’t be connected to a SQL database. However, before you can use the project you need to connect it to a SQL database.

After you entered the required information click on "Next" and Access will try to connect to the Server you specified. When Access connects to the Server, the wizard will display the message "The wizard has all the information it needs to create your SQL Server database". Click Finish.

Connect to an existing SQL Server Database

When you click "Yes" after pushing the "Create" button, the Data Link Properties dialog box will appear. Type the server name, the log on information and the database name. Click on "Test Connection" to verify if the logon information is correct.

Figure 5 Data Link Properties Dialog box

If the connection was established the message "Test connection succeeded" should appear. Click on "OK" to close the dialog box.

Connect an Access Project to a SQL Server

If for any reason you haven’t connected the Access Project to a server, you can do it easily using the main menu. Click the Microsoft Office Button, point to Server and click on Connection.

Figure 6 Click to open the Data Link Properties Dialog Box

This will open the Data Link Properties Dialog box (see Figure 5). Input the information requested, test the connection and you can start using the Access Project you have created.

Definitions:

OLE DB (or Object Linking and Embedding Database), as defined by Microsoft: "a component database architecture that provides efficient network and Internet access to many types of data sources".