Comparing Microsoft Access App and MS Access Desktop Database

Comparing Microsoft Access 2016 App and MS Access Desktop Database

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.”

microsoft acces-app-icon microsoft access-desktop-icon

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:

  • Office 365 with SharePoint Online
  • On-premises SharePoint Server 2013 with Access Services and SQL Server 2012
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:

  • Visual Basic for Applications (VBA)
  • Linking to external data, not just read-only SharePoint lists
  • Using update or crosstab queries
  • Importing data from XML, Data Services, HTML documents, or Outlook folders
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:

  • List Details
  • Datasheet
  • Blank (for custom switchboards, popups containing a single record, and so on)
  • Summary (group based on a field, calculate sum, average, popups let you drill down)
Form Wizard

 

Types:

  • Multiple items
  • Datasheet
  • Split forms
  • Modal dialogs
Form layout:
  • Snap to grid
  • Controls can be re-arranged and modified to some extent
  • Customizable action bar
Pixel by pixel control, split forms, sub-forms, tab controls, modal dialogs.
Data entry controls: All the basic controls, including: 

  • Cascading controls (Office 365 only)
  • Related items
  • Autocomplete
Additional web, navigation, and chart controls
Find data: 
  • Popup views
  • Filter lists
  • Sort or filter datasheet views
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.

ms-access-workflow

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.

Leave a Reply

Your email address will not be published. Required fields are marked *