Which Access file format should I use?

The .accdb file format that was introduced in Access 2007 offers many benefits that are unavailable in earlier file formats. If you are upgrading to from a version of Access prior to Access 2007, you may have databases files that use an .mdb file format. In most cases, you should save these databases using the .accdb file format . You can keep a copy of the original .mdb file to help you verify that the transition went well.

This article explains the benefits of the .accdb file format, reasons why you might want to use an .mdb file format, and how to convert a database to the new file format.

The .accdb file format

Starting with Access 2007, .accdb is the default Access file format. The .accdb file format supports a number of newer features, such as calculated fields and attachments. However, there are some circumstances in which the .accdb file format might not be the right choice.

Features provided by the .accdb file format

Apps and web databases using Access Services Given appropriate server resources, you can publish an .accdb file to the Web (subject to certain compatibility restrictions). There are two ways to do so: Access apps and web databases.

Access apps are the newest form of database – new this release. An app runs entirely in the cloud – Access itself acts only as a design tool for apps. Apps take advantage of the latest SQL Server and SharePoint technology. Web databases were released in Access 2010, and although they offer many of the same benefits as apps, they do not support SQL Server.

After you publish an app or a web database, the database itself is on the server – the data on SQL Server or SharePoint and the user interface on SharePoint – but you can open the database as an .accdb file to make design changes. The app or web database runs in a browser - Access is not required to use either.
Multivalued fields A multivalued field is a kind of lookup field that lets you store more than one value per record. For example, suppose you need to assign a task to several employees. In an .accdb file, you can create a multivalued field to store which employees are assigned to the task. The employees' names can be selected from a table or a list of values.

Multivalued fields make it easy to select and store more than one choice, without having to create a more advanced database design. Multivalued fields are also important for integration with SharePoint, because SharePoint lists also support multivalued fields.
Attachment data type The Attachment data type lets you easily store all types of documents and binary files in your database while helping you keep the database file under the 2GB file size limit – attachments are automatically compressed. A record can have multiple attachments, but there can only be one attachment field per table.
Better integration with SharePoint and Outlook The .accdb file format support SharePoint and Outlook security requirements that aren't supported by .mdb files. This makes it possible to integrate Access more fully with SharePoint and Outlook.
Improved encryption You can choose to set a database password and encrypt the contents of your database. When you do so using the .accdb file format, by default Access uses the Windows Crypto API to encrypt the data. Third-party encryption tools can also be used.

Top of Page

The .mdb file formats

Before the .accdb file format was introduced in Office Access 2007, Access file formats used the .mdb file extension. There are multiple different versions of the .mdb file format.

You can still open some .mdb files. If the file is stored in the Access 2002-2003 or Access 2000 file format, you can open it and use it normally. However, you cannot take advantage of features that require the .accdb file format.

If you rely on features that aren't available in an .accdb file, don't convert the file – leave it as an .mdb file.

.mdb file features that aren't available in an .accdb file

Mixed-version environment The .accdb file format cannot be opened –or even linked to –using versions of Access prior to Access 2007. If there are people in your organization who have an earlier version of Access, you should consider whether using an .mdb file is a better option. But don't forget – you can use an Access app or a web database in a web browser, regardless of whether Access is even installed.
Database replication You can't use replication in an .accdb file. If your Access solution uses the replication feature, you will need to re-create the solution without replication if you want to use the .accdb file format.
User-level security The .accdb file format doesn't support user-level security, a feature that provides a way to help people see just what they need to see. User-level security is no longer effective for data security, as its security method is now obsolete and easily compromised. However, user-level security can improve usability by keeping things simple for people – for example, if someone has no business reason to use a particular form, you could hide the form from them. If you have an Access solution that relies on user-level security to improve usability, you might want to keep using the .mdb file format so you can keep using your solution as-is.

Top of Page

Converting to the new file format

To convert an .mdb file to the .accdb file format, open the file and then save it in the .accdb file format.

Important: The following procedure is for .mdb files created in Access 97, Access 2000, Access 2002 or Access 2003.

First, open your database:

Click the File tab.
On the left, click Open.
In the Open dialog box, select and open the database that you want to convert.

Now, convert your database:

Click the File tab.
On the left, click Save As.
Under File Types, click Save Database As.
On the right, under Database File Types, click Access Database.
In the Save As dialog box, in the File name box, enter a file name or use the file name supplied.
Click Save.

A copy of the database is created in the .accdb file format.

Top of Page

Microsoft Office Tutorials

Thursday, December 1, 2016