This
article applies to a Microsoft Access database (.mdb) and a Microsoft Access
project (.adp).
Novice: Requires knowledge of the
user interface on single-user computers.
MORE INFORMATION
Database management system (DBMS)
A DBMS is an application that you can use to automate the
storage, the retrieval, and the manipulation of information in a prescribed
format. There are many different types of DBMSs. DBMSs range from small DBMSs
that run on personal computers to huge DBMSs that run on
mainframes.
Access manages data and provides a graphical user interface. You can
use Access on personal computers to manage data at different levels and for
different purposes. You can also use Access to manage personal information or
to organize and to manage data in a small business. You can also use Access in
an enterprise to communicate with servers that store lots of
data.
However, Access does not store data on its own. Access uses a
database engine to store data to a hard disk and to retrieve data from the
hard disk. The database engine performs the actual interaction with the
physical data.
back to the
topDatabase
engine
A database engine is the part of a DBMS that provides a link
between the DBMS and the physical data on the hard disk. The database engine
retrieves data from and stores data to user databases and to system
databases.
Simple database engines only let you read data and write
data. Advanced database engines provide many critical security features.
Advanced database engines also provide a single entry point for all the users
of a database. Therefore, all users can access data in a manner that is
consistent with the permissions that the database owner grants.
Access
primarily supports the Microsoft Jet Database Engine (Jet) and the Microsoft
SQL Server 2000 or Microsoft SQL Server Desktop Engine
(MSDE).
back to the topMicrosoft Jet Database Engine (Jet)
Jet is an advanced
relational database engine that is used by Access. Jet handles database
processing for Access. Because Jet does not have a user interface, you have to
use a program such as Access to use Jet. You can use Jet to interact with other
databases, such as Paradox and dBASE. You can use Jet with the Open Database
Connectivity (ODBC) driver to provide data to the ODBC client
application.
Access stores data in the Jet Database Engine .mdb file
format. You can store many related tables in a single .mdb file. Jet also
stores the following in the same .mdb file:
- Indexes
- Relationships among tables
- Validation rules
- Query definitions
- Security permissions
When you use Access, Jet also stores the following database
objects in the .mdb file:
- Forms
- Reports
- Macros
- Modules
back to the topMicrosoft SQL Server 2000 or Microsoft SQL Server Desktop Engine (MSDE)
Microsoft SQL Server 2000 is a complete DBMS in itself. You can
use SQL Server 2000 to efficiently store lots of data. SQL Server 2000 uses a
specialized mechanism to help protect databases from unauthorized
users.
Access uses the OLE DB component architecture to provide
native-mode access to a SQL Server 2000 database or to MSDE. Access also lets you
develop true client-server applications in the Access environment by using SQL
Server 2000 as the database server. When you use SQL Server 2000 as the
database server, Access stores the data on the computer that is running SQL
Server 2000. The Access objects, such as forms, reports, macros, and modules, are
stored in the .adp file format that is used for Access projects. When you use
SQL Server 2000 as the database server, Access permits the use of client-server
technology. In this situation, the computer that stores the original SQL Server
2000 database acts as a server. The computer that has Access installed acts as the
client.
back to the topDatabase
A database is a collection of related data that is organized so
that you can easily view the data. You can also perform operations on the data
that is in a database. For example, you can retrieve data and modify data. You
can use a database as a computerized record-keeping system that maintains
information and that makes the information available when you want the
information.
For example, if you want to maintain the details of all
the students who attend a college, you can create an .mdb file that contains biographical data
for each student and performance scores for each student. This .mdb file
might also include the user interface forms that you use to add or to modify the
details for a new or existing student. If you want a report about a class or
about the individual students in a class, you can create a report that provides
this information. The report is included in the same .mdb file. The
.mdb file contains all the student details, the forms that you use to
enter these details, and the reports.
back to the topDatabase window
The Database window is the first window that appears when you open
an .mdb file or an .adp file. The Database window is the default window in Access. From the Database window, you can locate any database
object that is stored in a database. The Database window also displays the
shortcuts that you can use to create new database objects and to open existing
database objects.
back to the
topDatabase object
A database object is a self-contained component of a database. A
database object interacts with the physical data and then presents this data in
a specialized format. Access supports the following seven database objects:
- Table
A table is a collection
of data about a specific topic that is stored in rows and columns. You can
categorize all available data, such as employees and customers, and then store
each category of data in a separate table. You can store many tables in a single database. You can combine
the data in different tables to retrieve the data that you
want.
To organize the data in a
database, you must segregate the available data and then identify the tables
that you must have to store this data.
For additional information about how
to design a database, click the following article number to view the article in
the Microsoft Knowledge Base: 288947
Where to find information about designing a database in Access
- Record
A record is a row in a table. A record contains
organized details about a person, a location, or some other real-world entity.
For example, in an Employee table in the database for an organization, each
record corresponds to an employee of the organization. You may store any number
of records in a table. - Field
A field is a column in a table. A field represents a
defined attribute that is stored for all the records in a table. You can define
up to 255 fields in a Jet table. SQL Server supports more fields, but only 255
fields are visible in Access. The definition of a field includes the name of
the field, the type of data that is stored in the field, and any validation
rules that you must have to validate the stored data. - Data type
A data type determines the type of data that the field
can store. Jet data types include the following:
- Text
- Memo
- Number
- Date/Time
- Currency
- AutoNumber
- Yes/No
- OLE object
- Hyperlink
For example, to make sure that the Salary field in the
Employees table only contains numbers and not text, you must set the data type
of the Salary field as Currency.
back to the
top - Query
A query is an
instruction to retrieve information that is in a set of rows. You can use a
query to retrieve information from a single table or from multiple tables based
on specific criteria. You can also use a query to modify the information that
you retrieve.
In Access, you can categorize queries as follows:
- Select query
A select query retrieves information that you want from
one or more tables in a database. Then, the select query presents the information that you retrieved in a format that you want. You can also use a select query to group
records and to calculate sums, counts, averages, and other types of
mathematical values. The select query can also do string
manipulation. - Parameter query
A parameter query retrieves information in an
interactive manner. You specify the information that you want by providing
specific criteria. You can use a parameter query to extend the flexibility of
other query types. - Action query
An action query performs an action on data in a table.
You can use an action query to insert new records, to update existing records,
or to delete existing records. - Crosstab query
You can use a crosstab query to analyze information that
you retrieved. You can use a crosstab query to group data in rows or in
columns. You can also use a crosstab query to calculate the sum or the average
of a data group. - SQL-specific queries, such as the union query, the pass through query, and the data definition query
In Access, you can also use an SQL query to retrieve
information. However, Access does not provide a graphical user interface that
you can use to create an SQL-specific query.
You can also use an Access query as the source of data for
other database objects, such as other queries, forms, reports, or data access
pages.
back to the
top - Form
In Access, you can use
a form as the graphical user interface to data. Forms do not store any data.
Forms only fetch data from tables or from queries and then present this data to
you. However, you can bind a form to a query or to a table in your database. If
you change any data in a bound form, the data in the corresponding table or
query is updated. You can use a form to insert data in a table, to modify the
existing data in a table, or to delete data from a table.
You can also
create unbound forms. Unbound forms are not connected to a record source, such
as a table, a query, or an SQL statement. For example, a dialog box is an
unbound form. Forms use controls and properties.
- Control
A control is an object on a form, on a report, or on a
data access page. You can use a control to display data, to perform actions, or
to enhance the representation of data. For example, you can use a text box on a
form, on a report, or on a data access page to display data. You can also use a
command button on a form to open another form or a report. You can organize or separate the controls in a form by using lines
or rectangles. - Property
A property is a characteristic of a control object or a
database object that is defined. The value of a property defines the behavior
of a control object or a database object. You can set and retrieve
properties.
For example, Left is a property of a text box control. The Left property defines the left starting point of the text box when the
text box appears.
back to the
top - Report
A report presents the
data from a table or from a query in a preformatted and useful manner. You can
control the appearance of a report. Reports can use controls to improve the
graphical representation of data and to make the reports more
useful.
You can present the results of complicated calculations on
data in a report. You can use a report to view data or to print data for
additional analysis. For example, you can create reports for sales summaries,
phone lists, and mailing labels for the employees in your organization. You
cannot modify the data in a report.
back to the top - Data access page
A data access page is an
HTML page that lets you use a Web browser to access the data in an Access database. You can use data
access pages to view and to work with Access data over the Internet or over an
intranet. When you view a data access page, the Web browser opens a local
copy of the page. This local copy is connected to the corresponding database.
Therefore, any changes that you make to the presentation of the data, such as
filtering or sorting, only affect the local copy of the page. However, any
changes that you make to the data are reflected in the underlying
database.
back to the
top - Macro
A macro is a database
object that lets you automate tasks in an Access database. You can create
macros that run when an event occurs and that automatically perform
specific actions. You can use macros to automatically open a form, print a
report, or run some code. You can also run another macro from a macro. Access
also lets you group related macros together and store them as a single macro.
Such groups of macros are referred to as macro groups.
back to the
top - Module
A module is a
collection of Microsoft Visual Basic for Applications (VBA) code declarations,
code statements, and code procedures that are stored as a unit to help you
organize VBA code in Access.
Modules are different from macros because they are designed to perform complicated tasks that a macro cannot complete.
In Access, you can use a module as a stand-alone database object or
as a part of another database object, such as a form or a report. Stand-alone
modules are referred to as standard modules. Standard modules are available throughout a database. Modules
that are a part of forms or of reports are referred to as class modules. In class modules, you can write VBA code for event procedures
that are triggered when the corresponding event occurs in a form or in a
report.
- References
Access lets you reuse existing libraries, and it lets
you use the functionality of pre-existing modules by using references. A VBA
project in Access can contain references to object libraries that use .dll
files, .tbl files, and other files, including Access files or ActiveX Controls.
To view VBA project references, click References on the
Tools menu in the Visual Basic Editor.
back to the
top
Database object views
Access lets you view data in different formats. The formats are
based on the database object that you use. The following are the common
database object views:
- Design viewDesign view is
available for all database objects. Design view has tools and shortcuts that
you can use to design a database object. In Design view, you can only modify
the design of an existing database object. You cannot modify the underlying
data.
back to the
top - Datasheet viewDatasheet view is
available only for certain database objects, such as tables, queries, and
forms. Datasheet view displays the underlying data for a database object in a
tabular format. You can use Datasheet view to add data to a database, to modify
the data in a database, or to delete data from a database.
back to the top - SQL viewIn SQL view, you can
view queries in the basic form of SQL syntax. You can only use SQL view to
modify queries. You cannot use SQL view to modify the underlying
data.
back to the
top - Form viewAfter you design a
form, you can use Form view to display the form. In Form view, you can display
data and accept data. You can use Form view to modify the underlying data in a
table. However, you cannot change the design of a form in Form
view.
back to the
top - Print PreviewWhen you view a
database object in Print Preview, you can preview the printed report by using
the actual data. Print Preview is useful when you use Access
reports.
back to the
top - Page viewYou can use Page view
to view the contents of a data access page. Data access pages have the same
functionality in Page view that they have in Internet Explorer 5.0 and later.
The record navigation control displays the record source, the current record
number, and the total number of records.
back to the top
Relationship
A relationship is an association between Access tables or queries
that uses related fields. A relationship can be one-to-one, one-to-many,
many-to-one, or many-to-many.
A relationship lets you guarantee data
integrity and data consistency. A relationship lets you avoid data redundancy.
Relationships let data flow from one database object to another database
object. Therefore, you can match data from different tables, and you can make
sure that data is consistent between tables and between queries. You can use
relationships to make a database streamlined, manageable, and
organized.
You can define a relationship between two tables or between
two queries by using related fields. Related fields typically have the same
name in both tables. Typically, these fields include the primary key from one
table and a foreign key from the other table. The primary key provides a unique
identifier for each record.
back to
the topNormalization
Normalization organizes data in a database. Normalization includes
creating tables and then establishing relationships between the tables.
Normalization lets you help protect data, and it makes a database more flexible
by eliminating redundancy and inconsistent dependencies.
Normalization
typically involves dividing data between two or more tables and then defining
relationships between the tables. The purpose of normalization is to isolate
data so that additions, deletions, and modifications can be made in one table.
The additions, deletions, and modifications are then propagated through the
rest of the database by using defined relationships.
The different
stages of normalization are referred to as
normal forms. When you perform more normalization, normal forms become more
restrictive. For example, the first normal form is the least restrictive. The
fifth normal form is the most restrictive. We recommend that you normalize your
tables to the third normal form.
For additional information about database
normalization, click the following article number to view the article in the
Microsoft Knowledge Base:
283878
Description of the database normalization basics
Join
After you define relationships between tables and then normalize
the database, you may want to retrieve data from tables that are related. You
can use a
JOIN statement to combine data from two tables in the form of a join.
A join is a connection between two tables that are merged based on a common
field. Joins play an important role in queries that retrieve data from multiple
tables.
For more information about
joins and relationships between tables, click
Microsoft Access
Help on the
Help menu, type
about joining
tables or queries in a query in the Office Assistant or the Answer
Wizard, and then click
Search to view the topics returned.
back to the
topConnecting to external databases
Access does not restrict you to only Access databases or SQL
Server databases. You can connect to and work with other data sources,
such as dBASE, Paradox, Microsoft Excel, or text files. You can
also let other databases use your Access data.
Link
In Access, you can link to tables that exist in other formats such
as Excel, dBASE, and Paradox. When you link to these tables, Access only
creates a link between the original file and the Access database. Therefore, you
access the data in the original file from the Access database.
If you
modify the data in a linked table, the data in the original table also changes.
However, Access does not let you modify either the structure or the design of a
linked table. When you view the data in a linked table, Access retrieves the
data from the original table and then presents the data. Therefore, the
performance of the linked table may be slow.
back to the topImport
When you import a database object from other data sources to
Access, Access creates a local copy of the data. You can create a database
object to store the imported data in. Or, you can append the imported data to
an existing database object.
Because Access creates a local copy of
the data that you imported, the changes that you make do not affect the
original data. Changes that are made to the original data after the import do
not affect the table that you imported.
back to the topExport
You can export database objects so that other programs can
access the database objects that you create in an Access database. When you
export a database object, Access converts the database object to the
destination file format. Access then creates a local copy of the database
object in the destination file format.
back to the top