Microsoft Access Project

Introduction

A Microsoft Access project is an application that allows a user to connect and use a Microsoft SQL Server database. The database could be located in the same computer as Microsoft Access or in a different computer of the same network.

One of the most common reasons for creating a Microsoft Access project is to get graphical objects that offer an aesthetic interface. Such user-friendly objects are not available in Microsoft SQL Server. Besides, you should always avoid letting users directly use Microsoft SQL Server.

Before creating a Microsoft Access project, you must set up Microsoft SQL Server, or the computer on which it is installed, to allow Microsoft Access to connect to it (setting up Microsoft SQL Server for remote connectivity).

Before starting a project, you should (must) create a Microsoft SQL Server database. To do this in the Microsoft SQL Server Management Studio, on the Standard toolbar, you can click the New Query button . Then, in the Query window, enter the code for the database. Here is an example:

USE master;
GO
CREATE DATABASE Exercise1;
GO
USE Exercise1;
GO
CREATE SCHEMA Management;
GO
CREATE SCHEMA Personnel;
GO
CREATE TABLE Management.Departments
(
	DeptCode nchar(5) not null,
	Department nvarchar(40),
	CONSTRAINT PK_Departments PRIMARY KEY(DeptCode)
);
GO
CREATE TABLE Personnel.Employees
(
	EmplNbr nchar(7) not null,
	FirstName nvarchar(24),
	LastName nvarchar(24) not null,
	DeptCode nchar(5)
		CONSTRAINT FK_Departments
		FOREIGN KEY REFERENCES Management.Departments(DeptCode),
	Title nvarchar(50),
	CONSTRAINT PK_Employees PRIMARY KEY(EmplNbr)
);
GO

To execute the code to create the database, you can press F5:

Connecting Microsoft Access to a Server Database

Once the server and the database are ready, you can use Microsoft Access to create graphical objects that connect to it.

To start the project, launch Microsoft Access (if you have already launched it, click File -> New). Set the name of the new database but add the file extension as .adp:

Click Create. When you do, a message box would come up:

If Microsoft Access and Microsoft SQL Server are installed in the same computer and you want to user a database in Microsoft SQL Server, click No. If the database you want to use is in Microsoft SQL Server installed in a different computer, click Yes.

In the Select Or Enter A Server Name combo box, click the arrow and select the name of the server.

In the Enter Information To Log On To The Server section, select the desired option (in most cases, if you are working on a network you should select Windows NT Integrated Security; otherwise, if you plan to use a username and password to establish a connection to the server, select the second option and provide the necessary credentials).

In the Select The Database On The Server combo box, click the arrow and select the name of the database you want to use:

If necessary, click Test Connection:

Then click OK and OK. Once this is done, the tables from the database should appear in the Navigation pane:

You can then use your knowledge of Microsoft Access to create and design forms and reports that use records from the database's tables.

 

After creating the forms, you can use them to create records. Here are examples of deparments:
 

Department Code	Department
RESDV	Research and Development
MKSLS	Marketing and Sales

Here are examples of employees:

Employee #	First Name	Last Name	Department	Title
284-057	Donna	Mights	Research and Development	Research Associate
830-288	Alexander	Duma	Marketing and Sales	Sales Representative
884-062	Susan	Hawks	Marketing and Sales	Research Supervisor
558-973	Gabriela	Hugh	Marketing and Sales	Head Sales
380-226	Jeremy	Rodriguez	Research and Development	Research Associate