Access File Formats: ACCDB vs MDB

The release of Access 2007 brings a new file format. The MDB database files we’ve known for over a decade are now slated to become historical relics. While Access 2007 continues to support MDB databases for backwards compatibility purposes, Microsoft has now introduced the new ACCDB file format as the future standard.

he new format supports functionality not available in earlier versions of Access. Specifically, by using the ACCDB format, you will be able to:

• Include attachments in your database. The ACCDB format allows you to store file attachments and other binary large objects (or BLOBs) in database fields. This is a feature common to enterprise databases such as Oracle and SQL Server that’s been missing from Microsoft Access.
• Use multivalued fields. While database purists may scoff at the idea of multivalued fields because they defy the principles of normalization, they’ll make life easier for developers of simple databases. Multivalued fields offer you the opportunity to allow users to select one or more options for a field value by using checkboxes. For example, you could create a Shirt Sizes field with the values S, M, L, and XL. Users could then select all values that apply from the multivalued field.
• Safe integration with SharePoint and Outlook. SharePoint and Outlook both block MDB databases due to security concerns. Improvements in the database security model allow for the security validation of database files and both SharePoint and Outlook trust this validation.
• Encryption improvements. Users of ACCDB files may leverage the Windows Cryptographic API for database encryption. This capability is especially important in this day and age of identity theft where an encrypted database can mean the difference between an embarrassing and costly security incident and a non-event. This site describes Microsoft Access encryption below.

There are also two limitations that you should consider before making the switch to ACCDB. ACCDB databases do not support user level security or replication. If you need either of these features, you should stick with MDB format for the time being.

Microsoft Access encryption : What is encryption?

Encryption provides you with the ability to protect your database file from prying eyes. It transforms the way data is stored on your disk so that individuals who do not know the database password can not open the database or use other techniques to view the file contents. Security professionals recommend the use of encryption to protect sensitive information.

How do I encrypt an Access 2007 database?

Access 2007 users may encrypt databases stored in ACCDB format by password-protecting them. Note that this feature is not available for database stored in the older MDB format.

Sample Access 2007 database

This sample database template demonstrates how Access can manage small business customers, orders, inventory, purchasing, suppliers, shipping, and employees. The database can generate 15 different reports, and is a great showcase for learning and customizing Access databases.

Desktop Northwind 2007 sample database : download here

Other Ms Access 2010 Templates from Office website :

http://office.microsoft.com/en-us/templates/desktop-services-template-TC102489834.aspx

http://office.microsoft.com/en-us/templates/desktop-product-inventory-database-TC102483123.aspx

What are the Microsoft Access advantages?

Easy to install and use — Access gives data managers a fully functional, relational database management system in minutes. Like many other Microsoft applications, Access contains Wizards that walk you through each step of the way. The user interface is intuitive; accelerating data information retrieval.

Ease to integrate – Access works well with many of the developing software programs based in Windows. It also can be used in the front-end as back-end tables with products like Microsoft SQL Server and non-Microsoft products like Oracle and Sybase.

.NET-friendly – Access is a go-to choice for users who plan to develop software using .NET; linking to Access database. Its graphical user interface also offers easy functionality and set up.

Widely popular — Microsoft Access is the most popular desktop database system in the world.

Saves you money — Microsoft Access is hundreds of dollars more economical than other larger systems; offering the same functions and usage.

Convenient storage capacity – A Microsoft Access database can hold up to 2 GB of data.

Multi-user support – About ten users in a network can use an Access application.

Importing data — Microsoft Access makes it easy to import data.

What are the Microsoft Access disadvantages?

Finite – Microsoft Access is useful for individual departments or small-to-medium business sectors. Any sector whose usage goes beyond 2 GB will hit a wall and discover limitations.

Structure Query Language (SQL) — SQL for MS Access is not as robust as MS SQL Server or Oracle, to just name a few.

One file — All the information from your database is saved into one file. This limits options and how you choose utilize data; slowing down reports, queries, and forms. Its performance becomes slow as the user scales data size. Multimedia data can use up MS Access limited space quickly.

Static-Friendly — It’s difficult to publish files a part from static files.

Multi-user limited — Technical limit is 255 concurrent users, but real world limit is 10 to 80 (depending on type of application).