What is a Database and How WordPress Uses it?
A database is a system of storing and fetching data in an organized way. Database allows software to manage data in a programmable way.
For example: WordPress uses PHP (programming language) to store and retrieve data from the database.
The information stored in a WordPress database includes posts, pages, comments, categories, tags, custom fields, users, and other WordPress settings. We will elaborate on this later in the tutorial when we cover all WordPress database tables.
When you first install WordPress, it asks you to provide a database name, host, username, and password. This information is stored in the configuration file called wp-config.php.
During the installation, WordPress uses the information you provide about the database to create tables and store default installation data inside those tables.
After the installation, WordPress runs queries to this database to dynamically generate HTML pages for your website or blog.
This is what makes WordPress extremely powerful because you don’t have to create a new .html file for each page that you want to create. WordPress handles everything dynamically.
Where is my WordPress database stored?
WordPress uses MySQL as its database management system, which is a software installed on your WordPress hosting server. Your WordPress database is also stored on the same server.
This location however is not accessible on most shared hosting environments. However, if you are on a VPS hosting plan or a dedicated server, then you can use command line tools to locate the database.
Most often it is located at the following path:
/var/lib/mysql/your_database_name
However, this could differ from one hosting provider to another.
It is important to keep in mind that you don’t really need to access the database file itself. You can use other tools like phpMyAdmin to manage your database.
What is phpMyAdmin?
PhpMyAdmin is a web-based software that allows you to manage MySQL databases using your web browser. It offers an easy to use interface that allows you to run MySQL commands and database operations.
You can also use it to browse and edit database tables, rows, and fields. It also allows you to import, export, or delete all data inside a database.
How to Access PhpMyAdmin?
PhpMyAdmin comes pre-installed with all top WordPress hosting companies. You can find it under the Databases section of your hosting account’s cPanel dashboard. Below is an example screenshot from Bluehost control panel:
Depending on your hosting provider, your cPanel interface may look different than the above screenshot. You would still be able to find phpMyAdmin icon under the databases section.
Clicking on it will open the phpMyAdmin interface where you can select your WordPress database from the left column. After, that phpMyAdmin will display all tables in your WordPress database.
Let’s take a look at the WordPress database tables.
Understanding WordPress Database Tables
Each WordPress installation has 12 default tables in the database. Each database table contains data for different sections, features, and functionality of WordPress.
Looking at the structure of these tables, you can easily understand where different parts of your website are stored. Currently, a default WordPress installation creates the following tables:
Note: wp_ before each table name is the database prefix you choose during the installation. It can be different if you changed it at the time of installation.
wp_commentmeta : This table contains meta information about comments posted on a WordPress website. The table has four fields meta_id, comment_id, meta_key, and meta_value. Each meta_id is related to a comment_id. One example of comment meta information stored is the status of comment (approved, pending, trash, etc).
wp_comments : As the name suggests this table contains your WordPress comments. It contains comment author name, url, email, comment, etc.
wp_links : To manage blogrolls create by earlier versions of WordPress or the Link Manager plugin.
wp_options : This table contains most of your WordPress site-wide settings such as: site url, admin email, default category, posts per page, time format, and much much more. The options table is also used by numerous WordPress plugins to store plugin settings.
wp_postmeta : This table contains meta information about your WordPress posts, pages, and custom post types. Example of post meta information would be which template to use to display a page, custom fields, etc. Some plugins would also use this table to store plugin data such as WordPress SEO information.
wp_posts : The name says posts but actually this table contains all post types or should we say content types. This table contains all your posts, pages, revisions, and custom post types.
wp_termmeta : This table allows developers to store custom metadata for terms under their custom taxonomies. For example, WooCommerce uses it to store metadata for product attributes and categories.
wp_terms : WordPress has a powerful taxonomy system that allows you to organize your content. Individual taxonomy items are called terms, and they are stored in this table. For example, your WordPress categories and tags are taxonomies, and each category / tag inside them is a term.
wp_term_relationships : This table manages relationship of WordPress post types with terms in wp_terms table. For example this is the table that helps WordPress determine post X is in Y category.
wp_term_taxonomy : This table defines taxonomies for terms defined in wp_terms table. For example, if you have a term “WordPress Tutorials“, then this table contains the data that says it is associated with a taxonomy called category. In short, this table has the data that helps WordPress differentiate between which term is a category, which is a tag, etc.
wp_usermeta : Contains meta information about registered users on your website.
wp_users : Contains user information like username, password, user email, etc.
Managing WordPress Database using phpMyAdmin
Your WordPress database contains important WordPress settings, all your blog posts, pages, comments, and more. You need to be extremely careful when using phpMyAdmin, or you may end up accidentally deleting important data.
As a precaution, you should always create a complete database backup. This would allow you to restore your database the way it was before.
Let’s take a look at how to easily create a WordPress database backup.
Creating a WordPress Database Backup using phpMyAdmin
To create a backup of your WordPress database from phpMyAdmin, click on your WordPress Database. On the top menu, click on the Export tab.
In newer versions of phpMyAdmin, it will ask you for an export method. The quick method will export your database in a .sql file. In the custom method, it will provide you with more options and ability to download backup in compressed zip or gzip archive.
We recommend using the custom method and choosing zip as the compression method. The custom method also allows you to exclude tables from the database.
Let’s say if you used a plugin that created a database table, and you are no longer using that plugin, then you can choose to exclude that table from the backup if you want.
Your exported database file can be imported back into a different or the same database using phpMyAdmin’s import tab.
Creating a WordPress Backup using a Plugin
Keeping regular backups of your WordPress site is the best thing you can do for your WordPress security. While the WordPress database contains the majority of your site information, it still lacks several fairly important elements such as template files, images, uploads, etc.
All your images are stored in the uploads folder in your /wp-content/ directory. Even though the database has the information which image is attached to a post, it is useless if the image folder doesn’t have those files.
Often beginners think that the database backup is all that they need. It is NOT true. You need to have a full site backup that includes your themes, plugins, and images.
While most hosting companies claim they have daily backups, we recommend that you keep backups yourself just in case.
Note: If you are on a managed WordPress hosting solution like WPEngine, then they do create daily backups.
For the majority of us who are not on WPEngine, you should use a WordPress backup plugin to set up automated WordPress backups on your site.
Importing a WordPress Database Backup via phpMyAdmin
PhpMyAdmin also allows you to easily import your WordPress database. Simply launch phpMyAdmin and then select your WordPress database.
Next, you need to click on the ‘Import’ link from the top menu.
On the next screen, click on the Choose file button and then select your database backup file you downloaded earlier.
PhpMyAdmin will now process your backup file upload and import it into your WordPress database. Once finished, you will see a success message.
Optimizing your WordPress Database in phpMyAdmin
After using WordPress for a while, your database becomes fragmented. There are memory overheads which increase your overall database size and query execution time.
MySQL comes with a simple command that allows you to optimize your database. Simply go to phpMyAdmin and click on your WordPress database. This will show you a list of your WordPress tables.
Click on the check All link below the tables. Next to it, there is a “With Selected” drop down, you need to click on it and choose Optimize table.
This will optimize your WordPress database by defragmenting selected tables. It will make your WordPress queries run a little faster and slightly reduce the size of your database.
Fixing WordPress Issues using PhpMyAdmin
As we mentioned earlier, phpMyAdmin is a handy tool for troubleshooting and fixing some common WordPress errors and issues.
Let’s check out some common WordPress issues that can be easily fixed using phpMyAdmin.
Reset WordPress Password Using PhpMyAdmin
If you forgot your WordPress admin password and cannot recover it via lost password email, then this method allows you to quickly reset WordPress password.
First, launch the phpMyAdmin and select your WordPress database. This will display your WordPress database tables where you need to browse next to wp_users table.
Note: Table names in your WordPress database may have a different table prefix than the one we are showing in our screenshot.
You will now see the rows in your WordPress users table. Go ahead and click on the edit button next to the username where you want to change the password.
PhpMyAdmin will show you a form with all the user information fields.
You will need to delete the value in the user_pass field and replace it with your new password. Under the function column, select MD5 from the drop-down menu and click on the Go button.
Your password will be encrypted using the MD5 hash and then it will be stored in the database.
Congratulations! You have successfully changed your WordPress password using phpMyAdmin.
Now some of you may be wondering why did we select the MD5 hash to encrypt the password.
In the older version, WordPress used MD5 hash to encrypt passwords. Since WordPress 2.5, it started using stronger encryption technologies. However, WordPress still recognizes MD5 to provide backward compatibility.
As soon as you log in using a password string stored as an MD5 hash, WordPress recognizes it and changes it using the newer encryption algorithms.
Adding a New Admin User to WordPress using PhpMyAdmin
Let’s suppose you have access to the WordPress database but not the WordPress admin area. While you can change the admin user password, it will prevent the other admin user from using their account.
An easier solution would be to add a new admin user via phpMyAdmin.
First, you need to launch the phpMyAdmin and then select your WordPress database. This will show your WordPress database tables where you need to click on the ‘Browse’ link next to wp_users table.
phpMyAdmin will now show you the rows inside the wp_users table. Go ahead and click on the ‘Insert’ link from the menu on the top.
This will bring up a form that you need to fill up to add a new user to your WordPress site.
Here is how you need to fill each field in this form.
ID – You can ignore this one as this is automatically generated.
user_login – This will be your WordPress username that you’ll use to login.
user_pass – This is your WordPress password you need to enter the password and select MD5 in the function column.
user_nicename – This is the URL friendly username you can use the as your login.
user_email – Enter a valid email address as you may need it to recieve password reset and WordPress notification emails.
user_url – Add your website URL or you can leave it blank.
user_registered – You need to select the CURRENT_TIME in the function column to automatically insert current time here.
user_activation_key – You can leave this field blank as well it is used to approve user registeration.
user_status – You can leave this field blank as well.
display_name – You can enter the user’s full name as you want to be displayed on the articles. You can also leave it blank.
After filling in the form, click on the Go button to insert it into your WordPress database. PhpMyAdmin will now run the MySQL query to insert the data.
We have added the user, but that user does not have an administrator user role on your WordPress site. This value is saved in another table called wp_usermeta.
Before we can make this user an administrator, we will need to find the user ID. Simply click on the ‘Browse’ link next to wp_users table and you will see a row containing your newly added user with their user ID.
Note down the user ID as you’ll need it in the next step.
Now, let’s open the wp_usermeta table by clicking on the Browse link next to it.
Next, you need to click on the Insert link at the top to add a new row to the table.
You’ll now see a form to enter a new row. This is where you’ll tell WordPress that the user you created earlier has the administrator user role.
Here is how you’ll fill in this form.
umeta_id – You need to leave it blank as it is automatically filled in.
user_id – Enter the user ID you copied earlier.
meta_key – You need to enter wp_capabilities in this field. However, you may need to replace wp_ if your WordPress table names use a different prefix.
meta_value – You need to enter the following serialized value:
a:1:{s:13:"administrator";s:1:"1";}
Finally, click on the Go button to save your changes.
Next, we need to add another row to define the user level. Click on the Insert link on the top menu to add another row to the usermeta table.
Here is how you’ll fill in this form.
umeta_id – You need to leave it blank as it is automatically filled in.
user_id – Enter the user ID for your newly added user.
meta_key – You need to enter wp_user_level in this field. However, you may need to replace wp_ if your WordPress table names use a different prefix.
meta_value – Here you need to enter 10 as the user level value.
Don’t forget to click on the Go button to insert the data.
That’s all, you can now visit the WordPress admin area and log in with your newly added admin user.
Change a WordPress Username via PhpMyAdmin
You may have noticed that while WordPress allow you to change user’s full name or nickname, it does not allow you to change the username.
Now a lot of users end up choosing usernames during the installation that they may later want to change. Here is how you can do this via phpMyAdmin.
Note: there is an easier way to change WordPress username using a plugin.
First, you need to launch the phpMyAdmin from your hosting account’s cPanel dashboard. After that, you need to select your WordPress database.
PhpMyAdmin will show your WordPress database tables. You need to click on the ‘Browse’ link next to wp_users table.
This table will now list all the registered users on your website. Click on the edit link next to the username that you want to change.
PhpMyAdmin will now show you the user data. You need to locate the user_login field and change the value to the username you want to use.
Don’t forget to click on the Go button to save your changes. You can now visit your website and login with your new username.
Other Useful PhpMyAdmin Tricks
Your WordPress database is the engine behind your website. With phpMyAdmin you can tweak settings to improve performance, fix issues, or simply change things that cannot be changed from inside WordPress admin area.
Following are just a few more tricks you can use with phpMyAdmin.
Securing your WordPress Database
Before we get into this, we want to emphasize that every site can be hacked. However, there are certain measures you can take to make it a little harder.
First thing that you can do to is to change WordPress database prefix. This can significantly reduce the chances of SQL injection attacks on your WordPress database because often hackers target sites in masses where they are targeting the default wp_ table prefix.
You should always choose a strong username and password for your MySQL user. This will make it difficult for someone to get access to your WordPress database.
For WordPress security, we strongly recommend that you use Sucuri. It is the best WordPress security plugin that would catch any attempts at MySQL injection even before it reaches your website.
We hope that this guide helped you learn WordPress database management and how to use phpMyAdmin.
If you are the sort of person who likes to learn how things work behind the scenes, then you would love our guides on how WordPress actually works and how WordPress plugins work.
If you liked this article, then please subscribe to our YouTube Channel for WordPress video tutorials. You can also find us on Twitter and Facebook.
Microsoft Access has the look and feel of other Microsoft Office products as far as its layout and navigational aspects are concerned
MICROSOFT ACCESS DATABASE
Please kindly download our microsft access database guide as you read this information. We have screenshoots that we will be making references to. Download it here
Ms Access is a database management tool that enables one to have good command of data collected. The programme enables one to retrieve, sort, summarize and report results speedily and effectively. It can combine data from various files through creating relationships, and can make data entry more efficient and accurate. Microsoft Access (MS Access) enables one to manage all important information from a single database file. Within the file, one can use: • Tables to store your data. • Queries to find and retrieve specific data of interest. • Forms to view, add, and update data in tables. • Reports to analyze or print data in a specific layout. • Data access pages to view or update, the data. In MS Access, data is stored once in one table, but can be viewed from multiple locations. When the data is updated in a Table, Query or Form, it is automatically updated everywhere it appears Establishment of Ms Access database All Ms Access databases files are saved with extension .mdb A database should have a separate table for every major subject, such as pedigree records, Production data or Treatment information. Data should not be duplicated in multiple tables. Microsoft Access provides three methods to create a database Database Wizard (though easy, the wizard offers limited options to customize the database) Using a template (This method works best if one can find and use a template that closely matches the specific requirements) Creating a database directly (This is the most flexible method, but it requires one to define each database element separately). Create a new Access database [Exercise 1.A] After creating a new database, this should be saved by the name which reflects the content of the database. Upon saving the database, the Ms Access database window opens with the Tables tab-active (i.e in such a way that the next activity should be to create a table). MS-Access Basic 2 Creating a table Tables are the data storage facilities in Ms Access. Each table contains rows called records and columns called fields. A record is a collection of facts about a particular animal or event. Each record in a table should be unique. To distinguish one record from another, tables can contain a primary key field. A field is a single kind of fact that may apply to each animal or event. For example, date of birth is a field in a table on animal information. The fields in a database have settings that determine the - type of data they can store, - how the data is displayed, - what can be done with the data. For example, field settings can ensure that birth dates are entered with two numbers for the month, two numbers for the day, four numbers for the year, and slashes in between: 01/04/2006. One important setting for fields is the data type, which could be a number, text, currency, and date/time. The data type limits and describes the kind of information in the field. The data type also determines the actions one can perform on a field and how much memory the data will use. Fields also have properties that control the details of information inside them, including a character length, a default value, and a validation rule that makes sure the data meets certain criteria. Tables may be created by either: - Table wizard, - Design view - Entering data in a spreadsheet. Creating Tables using design view Creation of a table by design view is a user customized way of making data storage tables. Each field in the design view of a table corresponds to a column in the datasheet view of a table. Designing a table involves: - Entering unique names of the columns of the table in the “field name” column of the design view. Names of fields and objects in Microsoft Access can be up to 64 characters long. They can include any combination of letters, numbers, spaces, and special characters except a period (.), an exclamation point (!), an accent grave (`), and brackets ([ ]). They also can't begin with leading spaces - In the Data Type column, declare the type of data that will be entered in that column. The default is Text; or click in the Data Type column, click the arrow, and select the data type. - In the lower portion of the window Field properties, can be further specified. Microsoft Access allows one to distinguish between two kinds of blank values: Null values and MS-Access Basic 3 zero-length strings The Required property determines whether users can leave a field blank, resulting in a Null value. The AllowZeroLength property determines whether Text or Memo fields can contain a zero-length string. - Define a Primary Key field (You don't have to define a primary key, but it's usually a good idea. If you don't define a primary key, Microsoft Access asks if you want Access to create one for you when you save the table) - Save the table by a name of your choice, (related to the information the table) - Finally, close the design view to return to the database window where the columns of the table that you have just created are displayed. Note: To insert new fields within the table, in design view, click in the row below where you want to add the field, and then click Insert Rows on the toolbar. To add the field to the end of the table, click in the first blank row. To change from one view to another, make a selection on the View menu or click the arrow next to the View button and choose from the list that appears. To view, enter, and change data easily and directly in a table, create a ‘form’. Exercise 1. B Creating data entry forms A form is a type of a database object that is primarily used to enter or display data in a database. Most forms are bound to one or more tables and queries in the database. A form's record source refers to the fields in the underlying tables and queries. A form: - Focuses on one record at a time - Can display fields from more than one table - Can also display pictures and other objects - Can contain a button that prints, opens other objects, or otherwise automates tasks Data entry forms can be created either using a form wizard or in the design view. Once a table with fields is available, it is easy to create a form using the wizard: o Click on the forms tab o Double click on the create form by using a wizard. o This will lead you through a series of steps until you finish creating the form. The form created is linked to the table and information entered in the fields of the form is stored in the table. A form need not contain all the fields from each of the tables or queries that it is based on. MS-Access Basic 4 When you open a form, Microsoft Access retrieves the data from one or more tables, and displays it on the screen with the chosen layout in the Form Wizard, or with the layout that you created on your own in design view. Exercise 1.C Queries A query is a derived item in the database meant to answer specific questions that relate to the information in the database. Queries are handy during data processing. To find and retrieve just the data that meets conditions that you specify, including data from multiple tables, create a query. A query can also update or delete multiple records at the same time, and perform predefined or custom calculations on your data. A query requests data from the database. At its simplest, a query merely fetches all data from a single table. But as you create more complex (and more typical) queries, you can assemble exactly the data you want (i.e. unique sets of data that you require at any given time). Queries can also be used to execute mathematical and logical functions to obtain certain information in the database. Queries are derived from and linked to tables or other queries. (Due to these linkages, they tend to largely inflate the size of the database and should thus only be used to execute the intended functions, and stored only if updated information is to be retrieved) There are various types of queries for different uses: o Select queries- used for extracting specific information from a large multi-information table. They can also be helpful in merging related information from different tables. o Make-Table queries- used for making sub tables from the main table(s) and queries. o Update queries- important in adding information in the fields of a Table. o Append queries- used to copy records from one table/ query to another. o Delete query- to PERMANENTLY remove unwanted content from the table. NOTE: delete query should not be used unless one surely will not require the information to be deleted. To run a simple Select query: In the Database window, click Queries under Objects. Click the query you want to open. Click Open on the Database window toolbar. MS-Access Basic 5 Caution: It's a good idea to make a copy of the data you are changing or moving in an action query, in case you need to restore the data to its original state after running the action query In Design view, - Choose the tables or existing queries that contain the fields to use - Select and drag those fields to a grid (The fields can come from just one table, or from multiple tables, the fields specified for a query control the data that the query retrieves) - Specify criteria and other settings, such as whether to sort the results ( To test a new query in Design view. Just click the Run button on the Query Design toolbar. This will switch you to the results view. If you don't see the results you want, click the Design button to return to Design view. Note: To stop a query in progress (after you start it), press “CTRL+BREAK” Exercise 2.A A query result can include data from multiple tables. To combine data from tables, you use the Join operation The Join operation matches rows of one table with rows of another table, based on values in those rows. Relational Databases A relational database is one whose components (tables, forms, queries etc) are related (linked). The linkages between database components are created by making relationship links between them. The relationship can be between: - One component and another (one-to-one relationship), - One component related to several other components (one-to-many) - Several database components (many-to-many). Creation of relationships between database components reduces data redundancy and enhances ease of access of the information. Creation of relationships between database components When you create a relationship between tables, the related fields don't necessarily have to have the same names. However, related fields must have the same data type. UNLESS the Primary key field is an AutoNumber field. MS-Access Basic 6 An AutoNumber field can be matched with a Number field only if the FieldSize property of both of the matching fields is the same. For example, you can match an AutoNumber field and a Number field if the FieldSize property of both fields is Long Integer. Even when both matching fields are Number fields, they must have exactly the same FieldSize property setting. Defining a one-to-many or a one-to-one relationship 1. Close any tables you have open. You can't create or modify relationships between open tables. 2. Press F11 to switch to the Database Window. 3. Click Relationships on the toolbar. 4. If you haven't yet defined any relationships in your database, the Show Table dialog box is automatically displayed. If you need to add the tables you want to relate and the Show Table dialog box isn't displayed, click Show Table on the toolbar. 5. Double-click the names of the tables you want to relate, and then close the Show Table dialog box. To create a relationship between a table and itself, add that table twice. 6. Drag the field that you want to relate from one table to the related field in the other table.
but MS Access is a database and, 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. Early versions of Access cannot read accdb extensions but MS Access 2007 and later versions can read and change earlier versions of Access. An Access desktop database (.accdb or .mdb) is a fully functional RDBMS. It provides all the data definition, data manipulation, and data control features that you need to manage large volumes of data. You can use an Access desktop database (.accdb or .mdb) either as a standalone RDBMS on a single workstation or in a shared client/server mode across a network. A desktop database can also act as the data source for data displayed on webpages on your company intranet. When you build an application with an Access desktop database, Access is the RDBMS. Data Definition Let us now understand what Data Definition is: In document or a spreadsheet, you generally have complete freedom to define the contents of the document or each cell in the spreadsheet. In a document, you can include paragraphs of text, a table, a chart, or multiple columns of data displayed with multiple fonts. In spreadsheet, you can have text data at the top to define a column header for printing or display, and you might have various numeric formats within the same column, depending on the function of the row. An RDBMS allows you to define the kind of data you have and how the data should be stored. You can also usually define rules that the RDBMS can use to ensure the integrity of your data. For example, a validation rule might ensure that the user can’t accidentally store alphabetic characters in a field that should contain a number. 2. MS Access – RDBMS MS Access 4 Data Manipulation Working with data in 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 search for text strings in the original document and, with ActiveX controls, include tables, charts, or pictures from other applications. In a spreadsheet, some cells contain functions that determine the result you want, and in other cells, you enter the data that provides the source information for the functions. An RDBMS provides you many ways to work with your data. For example, You can search a single table for information or request a complex search across several related tables. You can update a single field or many records with a single command. You can write programs that use RDBMS commands to fetch data that you want to display and allow the user to update the data. Access uses the powerful SQL database language to process data in your tables. Using SQL, you can define the set of information that you need to solve a particular problem, including data from perhaps many tables. Data Control Spreadsheets and word processing documents are great for solving single-user problems, but they are difficult to use when more than one person needs to share the data. When you need to share your information with others, RDBMS gives you the flexibility to allow multiple users to read or update your data. An RDBMS that is designed to allow data sharing 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 (which is also known as transaction) so that either all the changes or none of the changes appear in your data. You might also want to be sure that no one else can view any part of the order until you have entered all of it. Because you can share your Access data with other users, you might need to set some restrictions on what various users are allowed to see or update. MS Access 5 MS Access uses “objects" to help the user list and organize information, as well as prepare specially designed reports. When you create a database, Access offers you Tables, Queries, Forms, Reports, Macros, and Modules. Databases in Access are composed of many objects but the following are the major objects: Tables Queries Forms Reports Together, these objects allow you to enter, store, analyze, and compile your data. Here is a summary of the major objects in an Access database; Table Table is an object that is used to define and store data. When you create a new table, Access asks you to define fields which is also known as column headings Each field must have a unique name, and data type. Tables contain fields or columns that store different kinds of data, such as a name or an address, and records or rows that collect all the information about a particular instance of the subject, such as all the information about a customer or employee etc. You can define a primary key, one or more fields that have a unique value for each record, and one or more indexes on each table to help retrieve your data more quickly. Query An object that provides a custom view of data from one or more tables. Queries are a way of searching for and compiling data from one or more tables. Running a query is like asking a detailed question of your database. When you build a query in Access, you are defining specific search conditions to find exactly the data you want. In Access, you can use the graphical query by example facility or you can write Structured Query Language (SQL) statements to create your queries. You can define queries to Select, Update, Insert, or Delete data. 3. MS Access — Objects MS Access 6 You can also define queries that create new tables from data in one or more existing tables. Form Form is an object in a desktop database designed primarily for data input or display or for control of application execution. You use forms to customize the presentation of data that your application extracts from queries or tables. Forms are used for entering, modifying, and viewing records. The reason forms are used so often is that they are an easy way to guide people toward entering data correctly. When you enter information into a form in Access, the data goes exactly where the database designer wants it to go in one or more related tables. Report Report is an object in desktop databases designed for formatting, calculating, printing, and summarizing selected data. You can view a report on your screen before you print it. If forms are for input purposes, then reports are for output. Anything you plan to print deserves a report, whether it is a list of names and addresses, a financial summary for a period, or a set of mailing labels. Reports are useful because they allow you to present components of your database in an easy-to-read format. You can even customize a report's appearance to make it visually appealing. Access offers you the ability to create a report from any table or query. Other MS Access Objects Let us now take a look at other MS Access objects. Macro This object is a structured definition of one or more actions that you want Access to perform in response to a defined event. An Access Macro is a script for doing some job. For example, to create a button which opens a report, you could use a macro which will fire OpenReport action. You can include simple conditions in macros to specify when one or more actions in the macro should be performed or skipped. MS Access 7 You can use macros to open and execute queries, to open tables, or to print or view reports. You can also run other macros or Visual Basic procedures from within a macro. Data macros can be attached directly to table events such as inserting new records, editing existing records, or deleting records. Data macros in web apps can also be stand-alone objects that can be called from other data macros or macro objects. Module Module is an object in desktop databases containing custom procedures that you code using Visual Basic. Modules provide a more discrete flow of actions and allow you to trap errors. Everything that can be done in a macro can also be done in a module, but you don't get the macro interface that prompts you what is needed for each action. Modules are far more powerful, and are essential if you plan to write code for a multi-user environment, because macros cannot include error handling. Modules can be standalone objects containing functions that can be called from anywhere in your application, or they can be directly associated with a form or a report to respond to events on the associated form or report. MS Access 8 In this chapter, we will be covering the basic process of starting Access and creating a database. This chapter will also explain how to create a desktop database by using a template and how to build a database from scratch. To create a database from a template, we first need to open MS Access and you will see the following screen in which different Access database templates are displayed. To view the all the possible databases, you can scroll down or you can also use the search box. 4. MS Access — Create Database MS Access 9 Let us enter project in the search box and press Enter. You will see the database templates related to project management. Select the first template. You will see more information related to this template. MS Access 10 After selecting a template related to your requirements, enter a name in the File name field and you can also specify another location for your file if you want. Now, press the Create option. Access will download that database template and open a new blank database as shown in the following screenshot. MS Access 11 Now, click the Navigation pane on the left side and you will see all the other objects that come with this database. Click the Projects Navigation and select the Object Type in the menu. MS Access 12 You will now see all the objects types — tables, queries, etc. Create Blank Database Sometimes database requirements can be so specific that using and modifying the existing templates requires more work than just creating a database from scratch. In such case, we make use of blank database. Step 1: Let us now start by opening MS Access. MS Access 13 Step 2: Select Blank desktop database. Enter the name and click the Create button. Step 3: Access will create a new blank database and will open up the table which is also completely blank. MS Access 14 MS Access 15 Every field in a table has properties and these properties define the field's characteristics and behavior. The most important property for a field is its data type. A field's data type determines what kind of data it can store. MS Access supports different types of data, each with a specific purpose. The data type determines the kind of the values that users can store in any given field. Each field can store data consisting of only a single data type. Here are some of the most common data types you will find used in a typical Microsoft Access database. Type of Data Description Size Short Text Text or combinations of text and numbers, including numbers that do not require calculating (e.g. phone numbers). Up to 255 characters. Long Text Lengthy text or combinations of text and numbers. Up to 63, 999 characters. Number Numeric data used in mathematical calculations. 1, 2, 4, or 8 bytes (16 bytes if set to Replication ID). Date/Time Date and time values for the years 100 through 9999. 8 bytes. Currency Currency values and numeric data used in mathematical calculations involving data with one to four decimal places. 8 bytes. AutoNumber A unique sequential (incremented by 1) number or random number assigned by Microsoft Access whenever a new record is added to a table. 4 bytes (16 bytes if set to Replication ID). Yes/No Yes and No values and fields that contain only one of two values (Yes/No, True/False, or On/Off). 1 bit. If you use previous versions of Access, you will notice a difference for two of those data types. 5. MS Access — Data Types MS Access 16 In Access 2013, we now have two data types — short text and long text. In previous versions of Access these data types were called text and memo. The text field is referred to as short text and your memo field is now called long text. Here are some of the other more specialized data types, you can choose from in Access. Data Types Description Size Attachment Files, such as digital photos. Multiple files can be attached per record. This data type is not available in earlier versions of Access. Up to about 2 GB. OLE objects OLE objects can store pictures, audio, video, or other BLOBs (Binary Large Objects) Up to about 2 GB. Hyperlink Text or combinations of text and numbers stored as text and used as a hyperlink address. Up to 8,192 (each part of a Hyperlink data type can contain up to 2048 characters). Lookup Wizard The Lookup Wizard entry in the Data Type column in the Design view is not actually a data type. When you choose this entry, a wizard starts to help you define either a simple or complex lookup field. A simple lookup field uses the contents of another table or a value list to validate the contents of a single value per row. A complex lookup field allows you to store multiple values of the same data type in each row. Dependent on the data type of the lookup field. Calculated You can create an expression that uses data from one or more fields. You can designate different result data types from the expression. You can create an expression that uses data from one or more fields. You can designate different result data types from the expression. These are all the different data types that you can choose from when creating fields in a Microsoft Access table. MS Access 17 When you create a database, you store your data in tables. Because other database objects depend so heavily on tables, you should always start your design of a database by creating all of its tables and then creating any other object. Before you create tables, carefully consider your requirements and determine all the tables that you need. Let us try and create the first table that will store the basic contact information concerning the employees as shown in the following table: Field Name Data Type EmployeelD AutoNumber FirstName Short Text LastName Short Text Address1 Short Text Address2 Short Text City Short Text State Short Text Zip Short Text Phone Short Text PhoneType Short Text Let us now have short text as the data type for all these fields and open a blank database in Access. 6. MS Access — Create Tables MS Access 18 This is where we left things off. We created the database and then Access automatically opened up this table-one-datasheet view for a table. Let us now go to the Field tab and you will see that it is also automatically created. The ID which is an AutoNumber field acts as our unique identifier and is the primary key for this table. MS Access 19 The ID field has already been created and we now want to rename it to suit our conditions. This is an Employee table and this will be the unique identifier for our employees. Click on the Name & Caption option in the Ribbon and you will see the following dialog box. Change the name of this field to EmployeeID to make it more specific to this table. Enter the other optional information if you want and click Ok. MS Access 20 We now have our employee ID field with the caption Employee ID. This is automatically set to auto number so we don't really need to change the data type. Let us now add some more fields by clicking on click to add. MS Access 21 Choose Short Text as the field. When you choose short text, Access will then highlight that field name automatically and all you have to do is type the field name. Type FirstName as the field name. Similarly, add all the required fields as shown in the following screenshot. Once all the fields are added, click the Save icon. MS Access 22 You will now see the Save As dialog box, where you can enter a table name for the table. Enter the name of your table in the Table Name field. Here the tbl prefix stands for table. Let us click Ok and you will see your table in the navigation pane. Table Design View As we have already created one table using Datasheet View. We will now create another table using the Table Design View. We will be creating the following fields in this table. These tables will store some of the information for various book projects. Field Name Data Type Project ID AutoNumber ProjectName Short Text MS Access 23 ManagingEditor Short Text Author Short Text PStatus Short Text Contracts Attachment ProjectStart Date/Time ProjectEnd Date/Time Budget Currency ProjectNotes Long Text Let us now go to the Create tab. In the tables group, click on Table and you can see this looks completely different from the Datasheet View. In this view, you can see the field name and data type side by side. MS Access 24 We now need to make ProjectID a primary key for this table, so let us select ProjectID and click on Primary Key option in the ribbon. MS Access 25 You can now see a little key icon that will show up next to that field. This shows that the field is part of the table’s primary key. Let us save this table and give this table a name. Click Ok and you can now see what this table looks like in the Datasheet View. MS Access 26 Let us click the datasheet view button on the top left corner of the ribbon. MS Access 27 If you ever want to make changes to this table or any specific field, you don't always have to go back to the Design View to change it. You can also change it from the Datasheet View. Let us update the PStatus field as shown in the following screenshot. Click Ok and you will see the changes. MS Access 28 An Access database is not a file in the same sense as a Microsoft Office Word document or a Microsoft Office PowerPoint are. Instead, an Access database is a collection of objects like tables, forms, reports, queries etc. that must work together for a database to function properly. We have now created two tables with all of the fields and field properties necessary in our database. To view, change, insert, or delete data in a table within Access, you can use the table’s Datasheet View. A datasheet is a simple way to look at your data in rows and columns without any special formatting. Whenever you create a new web table, Access automatically creates two views that you can start using immediately for data entry. A table open in Datasheet View resembles an Excel worksheet, and you can type or paste data into one or more fields. You do not need to explicitly save your data. Access commits your changes to the table when you move the cursor to a new field in the same row, or when you move the cursor to another row. By default, the fields in an Access database are set to accept a specific type of data, such as text or numbers. You must enter the type of data that the field is set to accept. If you don't, Access displays an error message: Let us add some data into your tables by opening the Access database we have created. Select the Views > Datasheet View option in the ribbon and add some data as shown in the following screenshot. 7. MS Access – Adding Data MS Access 29 Similarly, add some data in the second table as well as shown in the following screenshot. You can now see that inserting a new data and updating the existing data is very simple in Datasheet View as working in spreadsheet. But if you want to delete any data you need to select the entire row first as shown in the following screenshot. MS Access 30 Now press the delete button.
Go to LEVEL TWO LESSON
