Here’s an overview of the comparisons between using Microsoft Access 2016 App and the desktop version.
An Access app is a database that you design and modify in Microsoft Access 2016 and use in a standard web browser. The data and database objects are stored in SQL Server or Microsoft Azure SQL Database, so you can share the data within your organisation using on-premises SharePoint or Office 365 for business. An Access App is created either from a template or from scratch.
Generally, a desktop database is a database system created to run on a single computer. Desktop databases are much more limited and constrained than larger data centre or data warehouse systems, where primitive database software is replaced by sophisticated hardware and networking setups.
An MS Access desktop database helps you store and track any kind of information such as inventory, contacts, or business processes. Like the Access app, you can also create an Access desktop database by using a template or creating it from scratch.
How to identify the two types of Access templates? An Access app template has a globe icon in its picture and the title of the app does not contain “desktop.” For example, “Asset tracking” or “Custom web app.” While an Access desktop database template has no globe in the picture and its title has “desktop” in it. For example, “Desktop asset tracking,” or “Blank desktop database.”
Access app and Access desktop database serve different purposes. You may want to check the table below to see which one works best for you.
Comparing Microsoft Access 2016 App and MS Access Desktop Database
| Use an Access app if … | Use an Access desktop if… |
| Your organisation is using either:
| Your organisation is not using SharePoint or your Office 365 plan does not include SharePoint Online. |
| You like the simple, intuitive look and feel of the user interface for Access apps. | You want complete control over the look and feel of the database user experience. |
| You want the reliability, security, and manageability of storing your data in a Microsoft Azure SQL database or in SQL Server. | You require a local database to store the data or you need to consolidate data from different on-premises data sources. |
| Anyone who has permission can view and edit data, even if they don’t have Access. | You’re willing to make sure each database user either has Access or has installed the Access runtime. |
| People inside and outside your organisation need to access data from any location and from many different devices. | Everyone can connect to the computer on which the Access database is stored, and will most likely use the database from a networked desktop or laptop computer. |
| You want your design changes to be available immediately. | You’re willing to redistribute the database front end every time you make a design change. |
| You want the latest features of Access Services as soon as they’re available. | You don’t mind waiting for the next release of Access to use the latest features. |
| Anyone who needs to create or modify an Access app has Access 2013. | You have people creating and modifying databases who are using different versions of Access. |
| The problem you’re trying to solve doesn’t require advanced database features. | You need advanced features, such as:
|
| Your reporting needs are straightforward, or you’re okay with using a desktop database to link to the data in the Access app for more complex reports. | You have complex reporting needs, beyond the simple summary reports provided in Access apps; for example, you need to integrate other databases into a report, or format the report. |
To help you distinguish between Microsoft Access 2016 apps and Access desktop databases, we included a set of tables that compare the two in terms of data storage, data entry, tools, and structure of the database.
Getting Started…
| Feature | Access App | Access desktop |
| Templates | App templates are available through the Office store and in Access when you create an Access app.Table templates are available as you create your tables. | Database templates are available on Office.com and in Access 2013 when you create a desktop database.Application parts, templates that contain several database objects, are available in the Application Parts gallery.Data Type parts are available for fields. |
| Specify permissions | Not directly in the app – permissions are inherited from the SharePoint site where the app is created. | Can set permissions for individuals based on system file permissions.Can also encrypt the database with a password. |
| Required tools for entering data, viewing data, and modifying database structure. | To enter and view data, no client software other than a web browser is requiredTo create or modify an app, Access 2013 is required. | To enter and view data, Access or the Access runtime (available as part of Access or as a free download) is required.To create or modify a database, Access is required. |
Data Storage…
| Area | Access app | Access desktop |
| Where data is stored | If you use Office 365 with SharePoint Online, data is stored in a Microsoft Azure SQL database.If you use your own on-premises SharePoint Server 2013 deployment, data is stored on-premises in SQL Server. | Local Access database |
| SQL Server required | No, if you use Office 365 with SharePoint Online, Microsoft hosts the SQL databases for you in Microsoft Azure.Yes, if you use your own deployment of SharePoint Server 2013 and SQL Server 2012. | No |
| Database size | If you use SharePoint Online, the limit is 1 gigabyte (GB).If you use SharePoint Server 2013 and SQL Server 2012 on-premises, database size is controlled through SQL Server 2012.In either case, if you plan to back up your data by saving an app package, the compressed data must be less than 100 MB. | 2 gigabytes (GB) for data and objects |
Data Entry…
| Task | Access app | Access desktop |
| Copy and paste from Excel, Word, or other sources: | Yes | Yes |
| Import data from: | ExcelAccess ODBC databases, such as SQL ServerText or comma-separated value (CSV) files SharePoint list | ExcelAccess ODBC databases, such as SQL ServerText or comma-separated value (CSV) files SharePoint list XML Data Services HTML document Outlook folder |
| Append data to a table using the import wizard: | No | Yes |
| Link to data in: | SharePoint list (read-only) | ExcelAccess ODBC databases, such as SQL ServerText or comma-separated value (CSV) files SharePoint list HTML document Outlook folder |
Database Structure…
| Area | Access app | Access desktop |
| Tables | Table templates available.Can hide tables. | Application part templates are available. |
| Relationships between tables: | Creating the Lookup data type establishes a relationship. Information about relationship is viewed by selecting the Lookup field and clicking the Modify Lookups icon. | Can view in relationships window |
| Queries: | No action queries.Data macros are used to perform updates, appends, and deletes. | Action queriesCrosstab queries |
| Forms, Views: | Called “views” in Access appsInteractive view designerCan show thumbnails Types:
| Form Wizard
Types:
|
| Form layout: |
| Pixel by pixel control, split forms, sub-forms, tab controls, modal dialogs. |
| Data entry controls: | All the basic controls, including:
| Additional web, navigation, and chart controls |
| Find data: |
| Options available through the UI and custom design |
| Reports: | Simple summary and grouping views are included.For traditional Access reports, use a separate Access desktop database, and connect it to the Microsoft Azure SQL database that stores the data from your Access app. | Can create Access reports as needed. |
| Macros: | Use provided macros to automate user interface and data operations. | Can use macros or VBA to automate user interface and data operations. |
| ActiveX Controls and data objects: | No | Yes |
| Email notifications: | No. Can be created by connecting a desktop database to the Microsoft Azure SQL database. | Yes, by using macros |
Tools…
| Tool | Access app | Access desktop |
| Table analyzer wizard to identify redundant data: | No | Yes |
| Compact and repair: | No | Yes |
| Database documenter: | No | Yes |
| Backup and restore processes for data and structure: | Though the database is stored in Microsoft Azure, regular local backups are recommended.Back up, move, or deploy an Access app by saving it as an app package.Package just the structure of the app, or app plus data. | Back up data or data and structure by saving the database file. |
The principles of Microsoft Access databases apply to all versions and my popular eBook bundle covers the main core modules to MS Access – check them out.
Tags: building ms web apps, desktop database, Microsoft Access 2016, MS Access, web apps