Access Database Types Explained
Today’s post is in response to the frequent discussions we have with customers around the various types of Microsoft Access databases that you can create in Access and the key features that makes each of these unique.
Access Desktop Database
The desktop database has been around since Microsoft released the first version of Access and is available in a number of file formats. The database may consist of a single file, span multiple files or serve as a front-end file while using other linked data sources. Within the Access desktop database there are a number of possible objects: tables, queries, forms, reports, macros and modules. You are able to use both Visual Basic for Application (VBA) code or macro programming with desktop databases. Additional features of this type of database are that you can create a database up to 2 gigabytes (GB) and have multiple users share the database. You must have Access or Access runtime installed to use a desktop database and Microsoft supports sharing one only within a local area network (LAN). All versions of Access are able to create/modify desktop databases but not all file formats are supported in all Access versions. For instance, you cannot open an Access 97 file format database in Access 2013.
Access 2010 Web Database
Microsoft introduced the Access Web database in Access 2010. Access 2010 and Access Services (an optional component of SharePoint) allow you to create databases that you can use on the Web. You design a Web database in Access 2010 and publish it to SharePoint. The publishing process creates a SharePoint site and all of the database objects and data are moved into SharePoint lists in that site. You can later open the Web database in Access, make design changes, and then sync your changes back to the SharePoint site. In addition to being able to use the Web database in the Access client, users who have SharePoint accounts are able to use the Web database from a web browser. Programming in a Web database is limited to macros and data macros. The key features here revolve around the fact that the database may be accessed over the web without having Access installed. There are some disadvantages with using SharePoint lists for the tables as you may encounter limitations in the number/type of columns and performance/timeout issues.
While you can modify and use existing Access 2010 Web databases in Access 2013 on SharePoint 2013, you cannot create new Access 2010 Web databases.
Access Web App
The Access web app is new to Access 2013. Access 2013 uses Access Services 2013 on Microsoft SharePoint 2013 to manage authentication and to act as the web server for your Access app's URL. An Access app stores all of the app objects and data in a dedicated database on SQL Server (SQL Azure on Office 365). Using SQL Server for the data storage allows many more records to be stored within any given table than can be stored in a SharePoint list. Because the processing of Access app data is done in SQL Server/SQL Azure, data can be manipulated more efficiently than the data in SharePoint lists. When using an Access web app on Office 365, there is a 1 gigabyte (GB) size limit to the SQL Azure database. For an on-premise installation of SharePoint 2013 with Access Services, the local SQL database will determine the overall web app size. In an Access web app, you can add, edit, and delete data in the web browser. To make design changes, you must open or customize the app in the Access 2013 client on your local machine. All operations in an Access app require you to have an active internet connection as there is no offline access for an Access web app.
Summary
Access provides a highly integrated set of tools to create databases or apps that fill a variety of needs. You can find a number of links discussing each database type in more detail below.
Access Desktop Database Links
Ways to share an Access desktop database
Access Web Database Links
Build an Access database to share on the Web
Design differences between desktop and web databases
Set up and configure Access Services 2010 for web databases in SharePoint Server 2013
Access Web App Links
Get started with Access 2013 Web Apps
How to: Create and customize a web app in Access 2013
Should I create an Access 2013 app or an Access desktop database?