Transcript MS Access

MS Access
Objectives

What is MS Access?

How to create table?

How to write queries?

Developing front end (forms)

Create, format and print report

Uses
What is MS Access?

Microsoft Access, also known as Microsoft Office Access, is a database
management system from Microsoft that combines the relational Microsoft
Jet Database Engine with a graphical user interface and softwaredevelopment tools. It is a member of the Microsoft Office suite of
applications, included in the Professional and higher editions or sold
separately.

Microsoft Access stores data in its own format based on the Access Jet
Database Engine. It can also import or link directly to data stored in other
applications and databases.

User writes queries in SQL, form objects are used to create front end and
VBA is used to communicate between forms and data.
Relational Database

What Is a Relational Database? The term database means different things
to different people. For many years, in the world of the older database
technologies, database was used to describe a collection of fields and
records. Access refers to this type of collection as a table.

In Access terms, a database is a collection of all the tables, queries, forms,
reports, macros, and modules that compose a complete
system. Relational refers to concepts based on set theory.
Creating a Project
Creating a Project - contd
Choosing a Design
Creating a Table
Creating a table through Design View
Insert into table
Relationship between tables
Select Query
Select Query
SQL view of Select Query
Select Query in Datasheet view
Creating forms
Login form
Accessing Data in forms

Users can access data through one of the following methods –

A) SQL tables directly.

B) Through Macro.

C) Through VBA code.

D) Through Expressions.
Using Macros
Expression Builder
Reports
Report
Uses

It’s cheap. Also most of the companies would be having the MS Office installed in their
systems. Acquiring a copy of Access won't require a loan or a call to investors.

It’s easy. Anybody with just a bit of time and reasonable intelligence can learn to use Access.
It doesn't take weeks of classroom instruction and then months of mentored on-the-job
training to acquire the skills necessary to create and administer a database.

Development costs less. Many developers make a good living creating custom database
applications in Access. However, in general, they charge less than SQL Server and Oracle
developers.

Prototyping is a snap. Access is a great way to show fast results for the impatient client or boss.
You can collect a little data and in just a few hours (or days) wow them with a few neat forms
and reports

It talks to Office. Access is part of the Microsoft Office suite, so it plays well with the other
applications. Users can quickly and easily export data from or import data into Excel or
publish reports to Word.
Uses - contd

There's less code! All things being equal, Access can get the job done with less code than
SQL Server (or some other client-server database). In addition, VBA is an easy language to
master.

It offers connectability. Access offers an affordable solution for individual users and smaller
teams.

MVC in one application. You don’t have to worry about compatibility. We can develop our
front end, middleware and back end using the same application.
Disadvantages of using MS Access

Concurrent Users - Even though Microsoft has stated that Access is able to support 255 concurrent
users, it is a more practical choice to select Access when the database will be used by only 15 to 20
simultaneous users.

Finite - Microsoft Access is useful for individual departments or small-to-medium business sectors. Any
sector whose usage goes beyond 2 GB will hit a wall and discover limitations.

Multiple Windows Operating Systems - Whenever various Windows operating systems like Windows 98,
Windows ME, Windows 2000, Windows XP, or Windows Vista are used to access the same database,
the database may experience corruption issues.

Internet Limitations - Access was not designed to be used with applications that are web based since
its forms and reports are only functional in a Windows environment and are not compatible with an
internet browser like Internet Explorer.

One file - All the information from your database is saved into one file. This limits options and how you
choose utilize data; slowing down reports, queries, and forms. Its performance becomes slow as the
user scales data size. Multimedia data can use up MS Access limited space quickly.

Access on a Mac - Unfortunately, Microsoft Access is not designed to operate on Apple Macintosh
computers.
Thank You!