O365: Access 2013 app exceeded maximum database size limit allowed

Applies to: Access 2013

Symptoms


When you try to add data or save design changes in your Microsoft Access web app on Microsoft Office 365, the attempt fails.  Additionally, you receive an error message that resembles the following:
We're sorry. Access can't perform this operation because your app has exceeded the size limit allowed. Please remove some data to free up space, wait for your database size to get below the size limit and try again. You might need to allow up to a day before you can add or update data.

Cause


This issue occurs because you have reached the 1 gigabyte (GB) size limit for an Access app on Microsoft SQL Azure. When the Access app's SQL Azure database reaches or exceeds the 1 GB limit, you cannot edit or add records, and you cannot save any design changes.

Resolution


To resolve this issue, you must delete data from the app in order to free enough space to let you edit or add records and to save design changes.

More Information


We recommend that you back up your data before you delete it. To back up non-image data, you can link to the Access app data from an Access desktop database. Then, you can right-click each linked table and convert it to a local table in the desktop database. 

If you need help backing up and restoring image data, please call Microsoft Support at 1-800-MICROSOFT.

Linking from an Access desktop database to the Access web app SQL Azure database

To link to the Access app data on SQL Azure from an Access desktop database, follow these steps:
  1. In the Access 2013 web client, click Info.
  2. Click Manage in the Connections section, and then select From Any Location.
  3. Click Enable Read-Write Connection.
  4. Click Manage.
  5. Click the View Read-Write Connection Information option.
  6. Record the connection information.
  7. Create a new desktop database in Access 2013.
  8. In the Access desktop database, click the External Datatab, and then select ODBC Database.
  9. In the Get External Data dialog box, select Link to the data source by creating a linked table, and then click OK.
  10. Select the Machine Data Sourcetab, and then click New.... You may receive a message that says you are not logged on with Administrative permissions. If you receive this message, click OK.
  11. On the first Create New Data Source screen, you will see that you are creating a User DSN. Click Next.
  12. In the Create New Data Source dialog box, scroll down and select SQL Server Native Client 11.0*, click Next, and then click Finish.
  13. Assign any name to the New Data Source to SQL Servertext box.
  14. Copy the Server name that is shown in your Access web app connection information into the Server text box.
  15. Click Next, select With SQL Server authentication using a login Id and password entered by the user, and then click Next again.
  16. Enter the User name from your Access web connection information as the logon ID, and enter the password from the web connection information. Then, click Next.
  17. Select the option Change the default database to:, copy in or type the name of the database, and then click Next.
    Note Do not click the drop-down menu.
  18. Select the table that you want to link to, click Next, and then click Finish.
  19. Click Test Data Source. You should receive a message that the tests completed successfully. Click OK, and then click OKagain.
You should now see a list of tables in the Access app.

Note If you do not see SQL Server Native Client 11.0 in your list of options in ODBC, install the driver from the Microsoft SQL Server 2012 SP1 Feature Pack. Install the x64 version of sqlncli.msi if your version of Windows is 64-bit. Install the x86 version of sqlncli.msi if your version of Windows is 32-bit.

For more details on connecting to Access app data from an external connection, see How to make external connections to an Access web app.
Saving the Linked SQL Azure data to an Access desktop database
To save linked SQL Azure data to an Access desktop database, follow these steps:
  1. In the desktop database in which you linked to the Access app data on SQL Azure, select the table that contains the data that you will delete.
  2. Right-click the table, and then select Convert to Local Table.
The table and all the data (except image data) will be saved to the local desktop database.

Note If you have access to SQL Server 2012 (or a later version), you can use the Import/Export function in SQL Server to import the non-image data from the Access app's SQL Azure database into a local SQL Server. Follow the previously mentioned steps to create the Connection information in the Access app.

Deleting Data
After you have backed up your non-image data, you can delete records individually when in the Access app. However, because you cannot select a large range of records in the Access App, it may be unlikely that you can delete the necessary number of records in order to reduce the size. To delete records, it is best to link to the Access app's database on SQL Azure from a client application such as an Access desktop database.

If you need help backing up and restoring images, please contact Microsoft Support at 1-800-MICROSOFT.

When you link from an Access desktop database, you can delete records individually. However, that can be a very slow process, even when you select a range of records to delete. To delete a large range of records, the fastest way is to create a SQL pass-through query in the Access desktop database. For information about how to do this, see How to create an SQL pass-through query in Access.

The following is an example of a SQL pass-through query to delete a range of records of which the record IDs are between 2500000 and 3000000:
DELETE
FROM Table1
WHERE ID Between 2500000 AND 3000000