What is MS Access?
MS Access is a database technology used primarily by Microsoft servers – hence the ‘MS’ part.
Access databases aren’t as common as they once were, but many old sites still rely on them, so you’ll need a web host that can host the database for you.
As a database application used on Windows computers is sold along with Word and Powerpoint, part of the Office suite.
The database content is saved with the .mdb file extension. The use of .mdb isn’t anywhere near as popular as it used to be. Moreover, it is still a viable solution.
Who Uses MS Access?
There are two main user groups for MS Access: desktop users and developers. MS Access’ perks however primarily come through in the life of developers.
When writing code and building a database, the quality of the database technology does come through.
Desktop Users
A desktop Access user needs to create simple databases for a small audience, such as colleagues in a small office.
In this scenario, the database will probably be saved locally, or on a network, so that users can dip in and out.
Access Developers
The other major group is developers. These people are making custom apps. Most of them are small internal apps run on intranets.
Why MS Access?
If there are better options for databases than MS Access, why would anyone want to use it in the first place? There are several reasons:
- Simplicity
- Number of users
- Experience
- Platform
- Time and money
Simplicity
MS Access, as a part of the Microsoft Office suite, has been targeted toward people with less experience in database design or management.
It’s graphical with wizards walking users through the steps of creating databases and forms.
A large database server is overkill for most projects that expect a small number of users. Access was originally designed for a limited number of users on local networks.
Number of Users
Since MS Access isn’t necessarily designed for the largest, scalable projects, the number of users is a huge factor.
MS Access is usually used internally by separate user groups or departments even, making it ideal for smaller teams in terms of infrastructure.
Experience
Like with everything, there is a learning curve for MS Access, when wanting to learn it properly. This process is always more enjoyable with a Microsoft product that is spoilt with resources online.
Not only does Microsoft have a ton of learning material, but there are forum topics run by old-school MS Access folk.
All-in-all, it’s a fun experience to be able to learn about a long-time popular database technology.
Platform
Windows is still the desktop platform of choice for businesses, so it still makes sense to create small databases using Access.
While the user interface and flow are both intuitive enough, Windows is still considered flexible as an operating system.
Time and Money
Timea and money are motivators for choosing Access. Access is included in copies of Office and subscriptions to Office 365. SQL Server is an expensive, complicated program.
MS Access for Small Sites
MS Access can handle a small number of concurrent users in this kind of set-up. Having said that some companies like Coca-Cola still use the database technology (apparently).
There are developers who need a database to hold content for an application or website.
The latter will probably use ASP, ASP.NET or PHP to hook into the Access database. But there are some limitations, which we will look at later.
Not Made for Large Projects
MS Access is primarily designed for desktop use and light traffic. If you’re planning to develop a website with MS Access at its core, you need to consider the alternatives in order to future-proof your website. For some users, Access still has its place.
Depending on yourself or team of developers, MS Access could still be a logical choice. Every team has their individual preference, so don’t rule it out straight away.
MS Access Today
The Microsoft Access application was originally released in November 1992, and it is still used to hold content for limited web use. It’s also used for some functionality on Microsoft’s SharePoint platform.
Size vs. MS Access’ Simplicity
For most purposes, Access has been overtaken by MS SQL, Oracle, and MySQL.
That’s because MS Access does not provide the features or resources that its competitors offer.
However, there is something to be said for accessibility, and Access is very simple for a beginner.
It also has a nice graphical user interface that helps newbies get to grips with the way databases work, and the database can be shared very simply.
MS SQL vs MS Access
The majority of common scripts – such as WordPress, Joomla! and Drupal – require a database that follows the conventions of the structured query language (SQL).
If you plan to build a website on one of these platforms, the decision has been made for you. Access won’t support these applications.
Uses for MS Access
Yes, MS Access is a simple, cheap way to get a database online fast, with integrated wizards to do a lot of the legwork for you. Installation couldn’t be easier, and creating databases is a breeze.
But it’s built around the Jet/ACE database engine, which is not considered industry standard.
So why use MS Access at all? For small businesses and hobbyists, MS SQL can be expensive and complicated to set up. There’s no user interface to make things easier. It’s really a developer tool.
There is a trimmed-down version, SQL Express, but it’s still more difficult to set up than Access. MS SQL is designed for more demanding applications and higher traffic than MS Access.
For that reason, it’s the better option for web deployment if you anticipate more than a handful of visitors.
Other Databases are Better for New Sites
Access can be used as a front end to an MS SQL server, so there’s no need to go for one or the other. Still, you never know when a site is going to become more popular in the future.
If you are building a site from scratch, you might go with MS SQL or another database that’s designed for large numbers of users at once.
It’s a lot faster and cheaper in the long run than having to convert an inadequate database. MySQL also runs on Windows servers, so you don’t have to convert to Linux, though it runs better there.
Looking for a great deal on MS Access hosting?
A2 Hosting just scored #1 in our speed and performance tests. Right now you can save up to 50% on A2 plans by using this discount link. Includes an “Anytime” money-back guarantee.
Publishing an Access Database
While Access has been mostly used for creating local databases on an individual machine or an intranet, it’s possible for it to be used as a web-based database. This is done by using Access Services.
Access and SharePoint
Access Services will take an existing database and turn it into a SharePoint site. This SharePoint site can be accessed through any modern browser.
SharePoint sites are widely used in corporate intranets but they can also be hosted on an external server, provided it runs Windows.
Database Modes
There are several modes you can set your database to:
| Mode | Function |
|---|---|
| Full control | Anyone can read and write a database and its design |
Contribute | Users can change data, but not design |
Read | Users can only read the data and can’t change anything |
Full control allows anyone to read a database and change its design. This is fine for small groups that aren’t working over the internet. You don’t want random people on the internet to access the database.
As a result, you only set your database this way over an intranet link firewalled from the greater internet.
Contribute Mode and Real Mode
Contribute mode lets users contribute data but not change the database design itself. This mode is useful for applications where users need to input information through forms, such as customer information.
Read mode only allows users to query the database, not add to or change the data or design. This makes a database more secure.
With any database-driven web app, there’s always a risk of a user manipulating a database if inputs aren’t properly escaped.
MS Access Maintenance and Conversion
When you want to maintain an MS Access database, you essentially need to take it offline, locking out all access as you work.
That’s inconvenient and impractical since Access databases are quite prone to corruption anyway.
As a database grows larger, it’s likely that you’ll have more people needing access, so the corruption and maintenance issue becomes more of a concern.
MS Access to SQL
It is possible to convert MS Access to MS SQL, and this is a sensible option if you want to grow or modernize your application, or make it more available.
Some columns need to be formatted differently, so you’ll need to convert the database and check the quality of the conversion carefully prior to deploying it. You may also need developer support.
Access to Azure
Microsoft has been making a big push toward the cloud with SQL Azure and is encouraging its clients to migrate as well, by turning off Access in its cloud projects.
If you depend on Microsoft Access you have can either convert manually or use a wizard to convert your database. There are several steps in the process:
- Create a new SSMA project
- Connect to the SQL server
- Run Microsoft’s Migration Wizard to convert
- Convert access database objects to SQL objects
- Load the converted database
Microsoft has detailed instructions for migrating from Access to SQL.
MS Access Requirements
MS Access is recommended for small websites with relatively low traffic. Even so, there are some basic requirements such as:
- Processing Power: 1Ghz
- Required Memory: 1 GB RAM (32-bit) or 2 GB Ram (64-bit)
- Required HD Space: 3 GB
- Graphics: DirectX 10 or better
It’s worth doing some research about further requirements and additional considerations when looking into using MS Access.
Host Support for Access
Not all web hosts support it, so check the specs of your chosen package. You will need a Windows server.
Since this database technology isn’t exactly new, this doesn’t come as a surprise. It is worth consulting your selected hosts about it, prior to purchasing a package or plan.
MS Access Security
Regardless of host, it’s recommended that all MS Access databases are placed outside of the web root, away from the section of your server that can be browsed over the web.
This is a security issue since a database located within web-accessible folders could be downloaded and viewed by anyone.
Is MS Access Dead?
Not yet, although many IT professionals don’t like Access and would rather not deal with it. Moreover, the scalability issues of Access have certainly not helped in this matter.
If you have a legacy application that needs hosting, make sure your web host supports MS Access and plans to support it in the medium term too.
The Bad News: MS Access is Being Phased Out
If you plan on hosting with Office 365 and SharePoint Online, that’s a different story.
The company officially deprecated Access for building web apps and is removing the feature, with plans to fully complete the removal by the spring of 2018. That only covers Microsoft’s cloud hosting.
If you need to keep an Access-backed site running, you’ll have to move it to another server, one that won’t be shut down by Microsoft.
The Good News: There Are Still MS Access Hosts
Microsoft Access is being phased out for web databases in favor of more robust servers like MySQL and Microsoft SQL Server.
If you’ve got a small database, you can still get away with hosting a site based on Access.
Reasons to Use MS Access?
If there are better tools for hosting a database-driven website than Access, why bother to use them at all? There are several reasons:
- The site needs to use features that only Access offers
- The site is only intended for a small number of users and doesn’t need advanced features like those in Microsoft SQL Server
- You don’t have a lot of experience with web design or database administration.
- You know that you’ll only need a Windows server.
- You’ve written custom software in .NET or Visual Basic and can’t replace it easily on another database
Top 3 MS Access Hosts
If you’re starting from scratch, we recommend choosing one of the other databases like MySQL, but if you’ve got an existing database and can’t convert it, there are some hosting providers that will suit you.
A2 Hosting
A2 Hosting has Windows hosting plans starting at $4.90 a month. The plans all include unlimited storage, transfer, and run on SSD drives.
This means that your Access site will run fast.
Liquid Web
Liquid Web is a company specializing in cloud, VPS, and more advanced solutions.
When you have a whole Windows server to yourself, you don’t have to depend on the whims of what Microsoft or the hosting company will want to do on your server.
You can install Access and keep it up as long as you like. Plans start at $59 a month. The company offers what it calls “heroic support” 24/7.
HostGator
While Access isn’t designed to handle large numbers of users, HostGator offers plans with unlimited storage and bandwidth.
The company has a 99.9 percent uptime guarantee, 24/7 support, and a 45-day money-back guarantee, one of the most generous in the industry.
Looking for serious MS Access hosting?
Liquid Web provides high-performance Windows plans and excellent support for MS Access. Right now you can get special pricing on their plans. Use this discount link to get the deal.
Other features in SQL
MS Access Frequently Asked Questions
- How do I install MS Access?
Usually, MS Access will come installed by default on Windows hosting plans. However, if you wish to play around with databases, you can install MS Office on your PC and get MS Access.
- Can MS Access be used for common website platforms such as WordPress?
Though MS Access can be queried just like any other database, platforms like WordPress do not support it. In fact, applications like WordPress use libraries that specifically connect to MySQL.
- Why do people use of MS Access?
MS Access has a number of advantages over industry standard MSSQL and MySQL databases and they include ease of installation, ability to quickly deploy databases, and a GUI for database creation. MS Access is truly suitable for applications involving small databases accessed by a handful of users.
- Does MS Access have potential security issues?
If you are using MS Access for a web application, you should place any Access databases you use in a safe location outside the web root folders. Otherwise, visitors could download private data.
- How do I enhance the security of an MS Access database?
MS Access provides users with the ability to set a database password and encrypt it which will help to secure sensitive data stored.
- What are some disadvantages of using MS Access?
Compared to popular database packages like MSSQL and MySQL, MS Access has a few disadvantages such as difficulty in handling large datasets, issues with high traffic, and tendency towards corruption as more data is stored.
- What is the maximum amount of data that can be stored in an MS Access database?
The maximum size an MS Access database can have is 2 GB. However, you can implement a workaround to this limitation by linking to tables in other Access databases.
- Am I able to link to other types of databases from an Access database?
You can link an Access database to another DBMS such as MySQL. When a query is executed on Access, it ends up going straight to the MySQL database. This feature can be helpful for users who are only familiar with Access.
- How do I optimize an Access database?
To ensure that queries to an Access database get executed as quickly as possible, you need to use the minimum number of fields or columns, compact data regularly using MS Access menus, and split databases into smaller ones.
- What scripting or programming languages can I use to work with MS Access?
You can use multiple programming languages to connect to and query data from MS Access databases. These include PHP, ASP, and ASP.NET.
- How can I share an MS Access database with other users?
Since MS Access saves databases to a file, sharing a database is as easy as copying the file to another user’s computer allowing them view the same data.
- Is it possible to migrate an Access database?
If your Access database starts to grow and becomes an integral part of your organization, then it may be time to migrate. However, migrating to different type of database such as MySQL or MSSQL will be difficult because of the different data types and formats involved. Developer support may be needed in this case.
- How is maintenance performed on an MS Access database?
As a file server model for databases, MS Access stores its data in an mdb file. Therefore, during maintenance, the database file has to be taken offline. In other words, users cannot make queries while the database is being maintained.
- Am I able to export MS Access data into other formats?
Users can take databases created with MS Access and export it in multiple formats including PDF and Excel spreadsheets.
Olivier
November 4, 2019
Hello David,
I was able to publish an 2010 Access Database to SharePoint web service. How do you do it with Access 2019 and SharePoint 365? I do not find this functionnality anymore
Many thanks
Olivier