The Admin Area is the administrative area of a WordPress website. Usually it can be accessed by visiting the
wp-admin
directory in your web browser. Example: http://www.example.com/wp-admin/The WordPress admin area is the administration center of a WordPress powered website. An administrator has full access to all the sections within the WordPress Admin Area. Users with other roles such as editor, contributor, or author have limited access to the admin area. Some users such as users with the subscriber role only have access to their profile page inside the admin area.
At the top of each administration screen is the toolbar or admin bar. It provides access to several administrative functions. On the left side is the main navigation that provides access to most of the WordPress management tools. Each major section typically comes with a sub-menu that can fly out and expand to show extra options.
The area in the middle of the screen is called the work area. This is where you can write, edit, and delete posts as well as adjust settings. At the bottom of each administration page is the footer. The footer contains links to WordPress and the version of WordPress that you currently have installed.
Additional Reading
Processing data is an important part of business life. Whether it’s a customer register, your private address book, accounting, or inventory: with digitalization ever increasing, it is becoming more and more important to organize and manage records as quickly, flexibly, and clearly as possible. This is achieved using database management systems (DBMS) that organize data and display it (e.g., a list of contacts’ locations sorted by zip code).
Microsoft Access (also known as MS Access) is a popular DBMS. The program is used in many small to medium-sized companies as well as by private users. The advantage over other DBMS: programming skills are not required to operate Microsoft Access. In addition to MS Access, there are other tried and tested database management systems that enable you to work in a similar way — some of them are also compatible with other operating systems such as macOS or GNU/Linux. There are even some free alternatives to Microsoft Access. We provide you with three of the best Microsoft Access alternatives that you can download for free.
- Why are MS Access alternatives needed?
- What are the free alternatives to Microsoft Access?
Why are MS Access alternatives needed?
But why would you need to look for an alternative to Microsoft Access? Its popularity would suggest that it’s a very capable and well-developed DBMS. In fact, MS Access does a fair amount of advantages — it is suitable for beginners as well as those who have more experience dealing with databases. However, it is only available for Windows and comes at a cost.
Microsoft Access is a relational database management system (RDBMS) that supports the input, storage, processing, evaluation, and presentation of data. The relational database model employed by the software is the most widely used form of database management systems — with an RDBMS, the data sets of different tables can be compared with one another.
The software provides a graphical user interface so you can easily manage databases. The front end not only allows you to request data but also directly change it, etc. By default, MS Access saves data sets in its backend using the Microsoft Jet Engine. Microsoft Access can also be connected to an SQL server database in which case Access only functions as a front end/GUI.
Unlike many database management systems that work according to the client-server principle (such as MySQL or its spin-offs MariaDB, PostgreSQL, Microsoft SQL Server, Oracle Database , etc.), Microsoft Access uses a desktop database management system. This means that the server doesn’t necessarily need to be connected to a network to be able to work on a database with MS Access.
Databases can be fully created as desktop applications. However, there is also the possibility to design a database as a web app but it must be hosted via a SharePoint web application from Microsoft and has fewer options than the desktop app. You can then view and edit a web database developed with MS Access in your browser.
Whether you are using MS Access to create a desktop or web application, MS Access databases are saved by default in a single file (in .mdb or the newer .accdb file format). This is the difference between desktop database management systems (such as MS Access) and server-database management systems (such as MySQL or MS SQL Server): a server DBMS typically creates one file for each database, as well as one entire folder, where the contents and design of the database are distributed over multiple files.
Advantages of MS Access
The software is particularly suited to beginners in database management: you don’t need any programming skills for producing simple databases since this is also relatively easy using the graphical user interface. Various assistants and templates (e.g., for contact administration, project tasks, etc.) make everything a lot easier. However, it is possible to freely define the data fields and to adapt the data model’s characteristics to specific requirements. Data sets or tables can be created, edited, and linked with each other quickly and easily in MS Access. If you want to use the program to implement extensive applications, there’s no way you can get around programming with VBA (Visual Basic for Applications) — although the effort required is usually less than with other database management systems. Using macros, you can also automate various tasks relatively easily.
It’s not only creating or administrating data sets that is often easier with MS Access than with other database management systems, but it’s also easier to analyze them. With the help of various assistance functions, it’s possible to analyze databases that have already been created, request databases according to certain characteristics, and also create reports without further ado.
By saving an MS Access database in a single file, you can easily share it with other people or transfer it to other devices. If you want to use the database in another DBMS (or you want to open other database files in MS Access), there are many interfaces at your disposal. In addition to other MS Office file formats (like Excel), you can import and export various other formats, e.g., ODBC databases (MySQL, Microsoft SQL Server, PostgreSQL, etc.), HTML documents, XML files as well as Dbase files. You can easily move tables and forms from one program to another.
Disadvantages of MS Access
The software’s simplicity also has its downside. Microsoft Access’ database system is far less powerful compared to a server database system: MS Access is more suitable for implementing lean databases, which don’t have several hundred thousand entries (a server DBMS is used for this). Processing large amounts of data takes a lot of juice with MS Access - hence the long loading times. This is the downside of a file-based database: the database is searched for the complete file, meaning that the larger it is, the longer it will take. The program is also limited in capacity, depending on the size of the database: the maximum file size is 2GB. However, as long as you don’t store large amounts of images, this limitation shouldn’t be a problem.
There are also significantly fewerfunctions available compared to an average server DBMS. In addition, there are shortcomings when several people are using the program at once, compared to a DBMS based completely on SQL: on the one hand, the SQL standard works with a much more differentiated authorization concept than MS Access, but on the other hand, the Microsoft program encounters performance problems quite quickly when a database is being used simultaneously by more than one or two people. Even with just a handful of users, Microsoft Access will start to run noticeably slower. At around ten users, the program has often reached its limit.
MS Access is only available for Windows operating systems and was an integral part of the Microsoft Office Suite in the past. After introducing the Cloud version of the Office Suite, it started to be released in various Office 365plans. In addition, you can also purchase the program individually. Fortunately, users who still have a classic version of MS Office installed on their Windows computer (as opposed to the Cloud version of Office 365) can still install this program. This is because Microsoft hasn’t made any major changes to the program for a long time. The current MS Access 2016, as well as the 2010 and 2013 versions, are best suited for typical database tasks.
Microsoft Access: pros and cons at a glance
Advantages | Disadvantages |
---|---|
Thanks to its advanced graphical interface, MS Access is ideal for quickly creating, managing, and analyzing databases, as well as producing simple reports. | Compared to database systems that follow the client-server model, MS Access is more unstable, only really suited for smaller and/or specific database requests. It also offers much less functionality. |
It’s suitable for any level of knowledge: both inexperienced as well as professional and experienced database managers can work with MS Access – with macros, you can combine various commands without programming knowledge and therefore automate tasks. | Because all the information is saved in only one file, MS Access has limited performance and capacity; the processing of large files slows the program down considerably and makes it unstable (usually around upwards of 1GB in data volume) — in addition, a file can’t be larger than 2GB. |
Uncomplicated operation that is easy to learn; assistants also explain many functions. | Simultaneous use only possible with additional software (SharePoint) and only to a limited extent |
By using a SharePoint web application, databases can be implemented as web apps. | Performance decreases noticeably when multiple users access the same database at the same time |
Many import and export possibilities to other database systems and MS Office products via ODBC | Fee-based and only available for Windows |
Widely used as part of Microsoft Office |
What are the free alternatives to Microsoft Access?
Microsoft Access as a desktop DBMS doesn’t provide the same opportunities that server database management systems do. But MS Access isn’t aiming to compete with programs like these: it’s because the server DBMS is on another scale among database management systems and conforms to a different target group (the operation and setup of the connected server both require SQL knowledge). Microsoft (with the MS SQL server) offers independent software in this class of database management systems while acknowledging MS Access is a product for users who are less tech savvy.
If you are looking for Microsoft Access alternatives, you have to compare the DBMS with other desktop database management systems. One of the most widely used alternative programs is FileMaker, which operates on Windows, macOS and even iOS. However, FileMaker is paid software and, therefore, not a free Microsoft Access alternative. The following programs can be used free of charge and without any prior SQL knowledge. This, in turn, distinguishes them from open source systems such as MySQL, MariaDB, and PostgreSQL. All three database management systems — just like MS Access – are not designed to work with very large chunks of data since the database is saved in a single file.
Base (available in LibreOffice and Apache OpenOffice)
Both LibreOffice and Apache OpenOffice are known for imitating programs from the Microsoft Office Suite and offering them as free software – Base is the equivalent alternative to Microsoft Access. The functions and characteristics of LibreOffice Base and OpenOffice Base barely differ from one another. This isn’t surprising since LibreOffice has been distributed as a spinoff to OpenOffice since 2010, and both Office Suites are based on the same source code. However, regular updates are released for LibreOffice.
Base is a Java-based RDBMS that is similar to its role model, MS Access: it works with a graphical user interface and provides the same basic functions for database generation and management. Program assistants are available to help you use different functions.
However, Base differs significantly from its Microsoft role model, as is the case with the LibreOffice and OpenOffice copies of Word, Excel, or PowerPoint. For example, it doesn’t contain as many advanced features as MS Access and it’s somewhat more complicated to use. In particular, users who are accustomed to working with Microsoft Access are likely to encounter problems with this program (e.g., macros and reports are much more complicated). Nevertheless, Base’s functionality is amazing — especially considering that it is a completely free program.
Base’s backend is based on HSQLDB (Hyper Structured Query Language Database), which can be used to back up a database as a Base file as well as a dBase file. Since an HSQL database does not allow any contending writing access, this means that multipleusers cannot work on one Base database at the same time. However, many database systems with multi-user access such as MS Access, MySQL, PostgreSQL, and Adabas D are supported.
Being compatible with other databases is generally one of the biggest advantages of the program. Thanks to the ODBC and JDBC database interfaces, you can connect Base to many other databases. Another great advantage compared to MS Access is its availability: Base (just like Windows) is also available for macOS and GNU/Linux, Unix, as well as BSD.
Advantages | Disadvantages |
---|---|
Available for Windows, macOS, GNU/Linux, Unix, BSD | Offers fewer functions than MS Access |
Supports the standard database interfaces ODBS and JDBC, which you can use to establish a connection with various other databases | Reports and macros are more difficult to create compared with MS Access |
Supports many database systems, such as MS Access, MySQL, PostgreSQL, and Adabas D, which can be used to set up multi-user access | Base is not user-friendly for multi-access as a desktop application (in combination with the standard HSQL database) — this is only possible by linking up with an appropriate database server |
Kexi (available in Calligra Suite)
Kexiis part of the Calligra Suite, an office software compilation similar to Microsoft Office, OpenOffice, and LibreOffice. It contains design applications such as an image processing program and a vector graphics program. The Calligra Suite developed from the now discontinued Office package KOffice, which was initially only operable on Unix-like operating systems such as Linux. Meanwhile, the Calligra Suite is also available for Windows and macOS with some limitations, but Kexi is not included in this.
The DBMS is therefore only available for GNU/Linux, but a Windows version is being planned. If you use it as a pure desktop DBMS, the program will access SQLite as a database. Alternatively, you can also use an SQL database server such as MySQL, PostgreSQL, Microsoft SQL Server, or dBase. Kexi supports the import of MS Access and dBase files.
Classic DBMS tasks, such as generating and modifying tables and requests can easily be performed with the software. You can also work with forms and create reports — the latter, however, are less detailed than they would be when using Microsoft Access. However, you cannot use macros at all (this is planned for future versions). In addition, the desktop application does not provide the option for multiple people to work simultaneously in a database.
Kexi’s functional range can’t keep up that of Microsoft Access, but in spite of this, various database management tasks can be managed perfectly well with this software. Kexi is a good choice if you want to create address books, product lists, and simple tables, but it isn’t a full-fledged replacement for MS Access.
Advantages | Disadvantages |
---|---|
File import of MS Access and dBase possible | Offers far fewer features and fewer options than MS Access |
Supports database server MySQL, PostgreSQL, MS SQL Server, and dBase | Macros are not (yet) supported |
No multi-user access on desktop DMBS | |
Currently only available for GNU/Linux |
Glom
The open source software, Glom, is also a good substitute for Microsoft Access. The RDBMS is available for GNU/Linux and Windows, but the Windows version hasn’t had any further developments since 2010.
You can create a database in a short time thanks to Glom’s clever user interface and clear functions. It’s possible to request data sets via the search function and compare them with one another. The program also offers the option of creating reports. Compared to Microsoft Access or LibreOffice Base or OpenOffice Base, however, the functionality is far more limited.
PostgreSQL is used as the database format. Glom is especially suited to smaller databases and projects such as contact lists and product lists, which can be created quickly thanks to the simple means of operation. However, Glom isn’t a fully developed MS Access alternative.
Advantages | Disadvantages |
---|---|
The clear user interface makes it extremely easy to use – databases can be quickly created and analyzed | Contains significantly less features than MS Access or Base |
Current version is only available for GNU/Linux (Windows version is from 2010) | |
Can no longer be used on desktop versions of Windows |
Summary: can free Microsoft Access alternatives replace the original?
The bottom line is that there is only really one free alternative to Microsoft Access in database management that offers roughly the same possibilities: LibreOffice/OpenOffice Base. But even with Base, you have to accept that there will be a few drawbacks compared to MS Access, especially regarding the database’s request possibilities and the general functional range. In addition, MS Access appears to be more stable and reliable: the program crashes very rarely and doesn’t need fixing often.
Glom and for the most part, Kexi, are more likely to use the database frontend concept, which additionally requires a database management system to be installed or at least collaborated with. This is useful because it solves some common problems associated with classic desktop database systems and provides a stable and multi-user database. However, the range of functions and the possibilities of the two database management systems are really quite limited, meaning that the two programs are only really suitable for a small number of tasks.
The advantage that MS Access has over the free, file-based database management systems is, among other things, the long development period that the program has already undergone as part of the MS Office Suite. Even though only a few new features have been introduced in recent versions, the software has been constantly improved since its release in the early 1990s and any errors encountered have been fixed. FileMaker is the only (fee-based) program that has a longer development history than the desktop database management system from Microsoft. If you are looking for a fully developed desktop DBMS with a great variety of functions, you should consider purchasing Microsoft Access (or the FileMaker software for macOS).
It could be that the other programs are right for you; it just depends on your requirements and your operating system. Base from LibreOffice and OpenOffice has the largest range of features out of all the free alternatives to Microsoft Access, followed by Kexi. On the other hand, Glom has the clearest frontend and is the easiest to use, thanks to its minimalistic layout. The type of software you go for can be dependent on how many user interfaces to other database systems are available or whether full multi-user features are included. The final overview presents all the important features of the three desktop database management systems that have been introduced in this article.
Overview: Microsoft Access compared with other desktop databases
Operating system(s) | Operation simplicity | Range of functions | Import/export options of other file formats | Special features | Multi-user access | Free | |
---|---|---|---|---|---|---|---|
Microsoft Access | Windows | ↗ | ↑ | ↗ | All-rounder among desktop DBMSs: has many features and interfaces to other databases | Yes (with limitations) | No |
LibreOffice Base/Apache OpenOffice Base | Windows, macOS, GNU/Linux | → | ↗ | ↑ | Available for all current operating systems and offers many interfaces to other databases | Only in combination with a database server | Yes |
Kexi | GNU/Linux | ↗ | → | ↗ | Supports various SQL databases | Only in combination with a database server | Yes |
Glom | GNU/Linux (and Windows with limitations) | ↑ | ↘ | → | Very clear and easy-to-use DBMS, suitable for simple databases | With GNU/Linux only in combination with a database server | Yes |
Out of the box, WordPress provides tons of functions that can be used to interact with the database. In most cases, the
WP_Query
class and related functions such as wp_insert_post
, update_post_meta
, get_posts
will be sufficient to get the job done. However, there will be times that we’re required to do something that is not provided by WordPress natively, especially when we need to deal with custom tables.In this tutorial, we’ll walk through the most important class to deal with databases in WordPress –
wpdb
, including a few tips and tricks that can be implemented in our development workflow later on. We’ll also touch on dbDelta
that can be used to create custom tables in our plugin. We won’t cover the basics of creating your original WordPress database in this tutorial, but feel free to check out this tutorial on creating databases from cPanelWorking with the wpdb
Class
wpdb
is perhaps the single most important class that we use when we need to deal with database directly. It is based on the ezSQL
Shree manache shlok meaning in marathi pdf. class written by Justin Vincent, adapted to work with WordPress.The basic methods and properties of the
wpdb
class is already well explained on the WordPress Codex page so there’s no point to reiterate them here. Instead, we’re going to go through a few common mistakes that WordPress developers can make, how to rectify them, as well as best practices that can be applied when using the wpdb
class.Do Not Hardcode Table Names into the SQL Query
Some developers make the general assumption that the table prefix will be unchanged and use default value of
wp_
. A basic example of the wrong way to do this is illustrated in the snippet below:This is of course is an over simplification of what a plugin will be actually doing, but this example shows how quickly things can go wrong. What if our users change the table prefix to something else? This can be easily fixed by replacing the
wp_
string with actual properties provided by using prefix
.The above code can be made portable by applying the changes as below:
Even better, if you’re dealing with WordPress’ default tables, you can skip the prefix part and instead, directly address them as properties in
wpdb
. Every default WordPress table is represented by a custom property in the wpdb
class with the same name of table without the prefix.For example, assuming that the table prefix is
wp_
:$wpdb->posts
will correspond towp_posts
table$wpdb->postmeta
will correspond towp_postmeta
table$wpdb->users
will correspond towp_users
table
And so on. The above code can be improved further, since we’re querying the posts table by doing things this way instead:
Use Specific Helper Methods for Database Operations
Although the
query
method is designed to handle any SQL queries, it’s preferable to use more appropriate helper methods. This is usually provided by methods such as insert
, update
, get_row
and others. Besides that it is more specific to our use cases, it’s also safer as the escaping and other grunt work is taken care of.Let’s take a look at this example:
Apart from the unsafe nature of this snippet, it should run just fine with proper values. However, this snippet can be improved further by using the
insert
method instead. The above code can be changed to look like the following:If we’re not supplying the format as a third parameter for
insert
method, all of the data provided in the second parameter will be escaped as a string. Plus, we can easily know what this code does at a glance, since the method name is clearer.Properly Debugging Database Queries
By default, error reporting is turned off. However,
wpdb
provides two methods that can be used to toggle the state of error reporting.To turn on the error reporting feature, simply run this code.
And to turn it off:
Another thing to note is that if we’re setting both
WP_DEBUG
and WP_DEBUG_DISPLAY
to true
, the show_errors
method will be automatically called. There is another useful method that can be used that deals with errors, namely print_error
:As the name suggests, it will only show the error for the most recent query, regardless of the state of error reporting.
Another neat trick is to enable
SAVEQUERIES
in wp-config.php
. This will store all of the database queries that run, times taken, and where it’s originally called from into a property called queries
in the wpdb
class.To retrieve this data, we can do the following:
Note that this will have a performance impact on our site, so use it only when necessary.
Most of the time, these functions will be enough to debug what is going wrong with our code. However, for more extensive debugging and reporting, there’s always the Query Monitor plugin that can help debugging more than database queries.
Securing Queries Against Potential Attacks
To completely secure our code from SQL injection,
wpdb
also provides another helpful method called prepare
that will take a string of an SQL statement and data that needs to be escaped. This is relevant whenever we’re dealing with methods like query
or get_results
.The
prepare
method supports both syntax of sprintf
and vsprintf
. The first parameter, $sql
is an SQL statement that’s filled with placeholders. These placeholders can exist in three different formats:%s
for string%d
for integer%f
for float
$format
can be a series of parameters for sprintf
like syntax, or an array of parameters that will be used to replace the placeholder in $sql
. The method will return the SQL with properly escaped data.Let’s take a look at how we can achieve the process of deleting
meta_key
in wp_postmeta
for a specific post ID:Note that this is not the recommended way to delete a record in the database using
wpdb
. It’s because we’re leaving the code open to SQL injection, since the user input is not properly escaped and used directly in the DELETE
statement.However, this can be easily fixed! We simply introduce the
prepare
method before doing the actual query, so that the generated SQL is safe to use. This can be illustrated in the snippet below:Connecting to Separate Databases
By default, the
$wpdb
variable is an instance of the wpdb
class that connects to the WordPress database defined in wp-config.php
. If we want to interact with other databases, we can instantiate another instance of wpdb
class. This benefits us greatly, because methods like insert
, update
and get_results
are available.The
wpdb
class accepts four parameters in construct, which are username, password, database name and database host, in that order. Here’s an example:If we’re using the same username, password and database host, but only need to change the selected database, there’s a handy method called
select
on the global $wpdb
variable. This is achieved internally by using the mysql_select_db
/mysqli_select_db
function.This is also particularly useful when we want to switch to another WordPress database, but still want to retain the functionality of functions like
get_post_custom
and others.Using Custom Database Tables
The WordPress default tables usually suffice to handle most complex operations. Utilizing custom post types with post metadata, and custom taxonomies and term metadata, we can do almost anything without the need of using custom tables.
However, custom tables might be useful whenever we want to have finer control over the data our plugin can handle. Benefits of using custom tables include:
- Total control of the data structure – Not all types of data fits into the structure of a
post
, so when we want to store data that does not make any sense as custom post type, a custom table may be a better option. - Separation of concerns – Since our data is stored in a custom table, it won’t interfere with the
wp_posts
orwp_postmeta
tables as opposed to if we were using custom post types. Migration of our data to another platform is easier since it is not restricted to how WordPress structures its data. - Efficiency – Querying data from our specific table will be definitely much faster than scouring over the
wp_posts
table that also contains data unrelated to our plugin. This is an apparent problem when using custom post types to store lots of metadata that can bloat thewp_postmeta
table.
dbDelta to the Rescue
Instead of using
wpdb
to create custom database table, it’s recommended using dbDelta to handle all of your initial table creation, as well as table schema updates. It’s reliable, since WordPress core uses this function as well to handle any database schema updates from version to version, if any.To create a custom table initially on plugin install, we need to hook our function to the
register_activation_hook
function. Assuming that our main plugin file is plugin-name.php
inside a plugin-name
directory, we can put this line directly into it:Next, we need to create the function
prefix_create_table
that does the actual table creation on plugin activation. For example, we can create a custom table called my_custom_table
that will be used to store simple customer data such as first name, last name and their email address.To maximize compatibility, we retrieve the database charset collate from
wpdb
. Plus, the SQL statement needs to abide by some rules to make sure it works as intended. This is taken directly from the Codex page on Creating tables with plugin:- You must put each field on its own line in your SQL statement.
- You must have two spaces between the words PRIMARY KEY and the definition of your primary key.
- You must use the key word KEY rather than its synonym INDEX and you must include at least one KEY.
- You must not use any apostrophes or backticks around field names.
- Field types must be all lowercase.
- SQL keywords, like CREATE TABLE and UPDATE, must be uppercase.
- You must specify the length of all fields that accept a length parameter. int(11), for example.
It’s also generally a good idea to store the database version into the options table, so that we can compare them during a plugin update in case our custom table needs updating. In order to do so, we simply add this line right after we create our table using the
dbDelta
function:Updating the Table Schema
Using the same example as above, let’s say during development, we change our mind and we also want to store our customer phone number inside our table. Garry s mod rp gmod. What we can do is trigger a table schema update during our plugin update. Since
register_activation_hook
will not be fired during a plugin update, we can hook into the plugin_loaded
action instead, to do our database version checking, and update the table schema if necessary.First, we add our custom upgrade function to the
plugin_loaded
hook:Game or Patch Questions? Ghost recon wildlands download.
The actual functions needs to do a few things:
- We need to get the stored database version.
- Compare them with our current database version.
- If it’s newer, we run the
dbDelta
function again. - Finally, we store the updated database version to the option table.
For the most part, we can actually reuse the
prefix_create_table
function like we did above, with some minor modifications:Notice that we do not need to use the
ALTER
statement, since dbDelta
will take our SQL statement, compare it against existing tables and make the modification accordingly. Pretty handy!Conclusion
Wordpress User Access
WordPress is not limited to creating simple websites, as it’s rapidly moving to a full-fledged application framework. Extending WordPress via custom post types and custom taxonomies should be our main priority. However, when we need finer control of our data, it’s reassuring to know that WordPress itself provides various functions and classes like
wpdb
for developers to utilize. This is what makes WordPress a mature solution.