MS Access – RDBMS Basics
In terms of its layout and navigation, Microsoft Access has the look and feel of other Microsoft Office products, but MS Access is a database, more specifically, a relational database.
- Before MS Access 2007, the file extension was *.mdb, but in MS Access 2007, the extension has been changed to *.accdb extension.
- Earlier versions of Access cannot read the accdb extension, but MS Access 2007 and later can read and change earlier versions of Access.
- Access Desktop Database (.accdb or .mdb) is a fully functional RDBMS.
- It provides all the data definition, data manipulation and data control functions required to manage large amounts of data.
- You can use an Access desktop database (.accdb or .mdb) as a standalone RDBMS on a single workstation, or in shared client/server mode over a network.
- The desktop database can also be used as a data source to display data on the corporate intranet web page.
- When you build an application using an Access desktop database, Access is the RDBMS.
Data Definition
- Now let us understand what is data definition –
- In a document or spreadsheet, you often have complete freedom to define the content of the document or each cell in the spreadsheet.
- In a document, you can include paragraphs of text, tables, charts, or multiple columns of data displayed in multiple fonts.
- In a spreadsheet, you can use text data at the top to define column headers for printing or display, and there may be multiple number formats in the same column, depending on the function of the row.
- RDBMS allows you to define the types of data you have and how the data is stored.
You can also typically define rules that the RDBMS can use to ensure data integrity.
For example, validation rules can ensure that users don’t accidentally store alphabetic characters in fields that should contain numbers.
Data operations
Working with data in an RDBMS is very different from working with data in a word processing or spreadsheet program.
- In a word processing document, you can include tabular data and perform a limited set of functions on the data in the document.
- You can also use ActiveX controls to search for text strings in original documents and tables, charts, or pictures in other applications.
- In a spreadsheet, some cells contain functions that determine the desired results, while in other cells, the data you enter provides information about the source of the function.
- RDBMS provides you with multiple ways to process data. For example,
- You can search for information in a single table or request complex searches across multiple related tables.
- You can update a single field or multiple records using a single command.
- You can write programs that use RDBMS commands to get data to display and allow users toupdate the data.
Access uses the powerful SQL database language to work with data in tables. Using SQL, you can define the set of information you need to solve a specific problem, which may include data from many tables.
Data control
- Spreadsheets and word processing documents are great for solving single-user problems, but they can be difficult to use when multiple people need to share data.
- When you need to share information with others, an RDBMS gives you the flexibility to allow multiple users to read or update your data.
- Designed to allow data sharing, RDBMS also provides features to ensure that no two people can change the same data at the same time.
- The best systems also allow you to group changes (also called transactions) so that all or none of the changes appear in your data.
- lYou may also want to make sure that no one else can view any part of the order until you enter the entire order.
- Because you can share your Access data with other users, you may want to set some restrictions on what different users are allowed to view or update.
