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:
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:
Here are examples of employees: