Which Access file format should I use?

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.

Long Text (Memo) field history tracking    Long Text (Memo) fields are useful for storing large amounts of information. When you use the .accdb file format, you can set a property (AppendOnly) that forces Access to retain a history of all changes to a Long Text (Memo) field. You can then view a history of those changes. This feature also supports the versioning feature in SharePoint so that you can use Access to track changes in multiple lines of a text field that is stored in a SharePoint list (provided that the field has the Append Changes to Existing Text option set to Yes).

Calculated data type    Beginning in Access 2010, the .accdb file format supports the use of a Calculated data type. You can use the Calculated data type to store the results of an expression that you define.

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.

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.