Home

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 New Query. 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:

Microsoft SQL Server Management Studio

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:

Microsoft Access Project

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

Microsoft Access Project

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:

Data Link Properties

If necessary, click Test Connection:

Data Link Properties

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

Microsoft Access Project

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

Departments

  

Employees

 

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

 

 
 
     
 

Home Copyright © 2011 FunctionX, Inc.