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.
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 top
Microsoft 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:
- Relationships among tables
- Validation rules
- Query definitions
- Security permissions
When you use Access, Jet also stores the following database objects in the .mdb file:
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:
Database object views
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:
288947You have to determine the structure for each table. To do this, you have to analyze the data for the attributes that you want to store in the table.
Where to find information about designing a database in Access
back to the top
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.
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:
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.
- OLE object
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:
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.
- 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.
back to the top
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.
back to the top
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.
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.
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
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
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.
back to the top
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.
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
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:
283878back to the topJoin
Description of the database normalization basics
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