Important Tip: Queries and the size of your Access 2010 Web database

This blog post is a follow up to a very important post/question: How much SharePoint 2010 storage will my Access application consume ?

Unfortunately, there is still no way to figure out how much space your local Access 2010 database will take up on the web before it is published to SharePoint.  The good news is that after working with clients on this and their databases, I can tell you that queries are a big reason for the ballooning of the database file size.  The more queries that you have in a Database, the more your database will increase in size once it is published to Access Hosting and SharePoint 2010.

The best way to describe this problem is to give a real word example.  Recently a customer had a database that was about 40MB that they had built from scratch in Access 2010.  The Database and tables passed the web compatibility checker (BTW – Microsoft’s Checker doesn’t catch everything, but that’s a story for another post). The database had over 500 Queries.  This local  Access database  that was 40MB (under 10MB zipped) grew to be over 100MB after it was published to SharePoint.  It more than doubled in size and took over 10 minutes to upload!

Please take into account that Queries seem to massively increase the storage space needed on SharePoint.  The additional load times is also something to be weary of.  If you are making a database from scratch, the best thing to do is create a few forms, tables, and queries and then publish the site to Access Hosting.  The first publish takes the longest.  After you have the basics published and your database started, you can continue to develop your database.   As you work in Access to add new queries, tables and reports, all of these changes will be uploaded and synced to the server seamlessly.

TIP: Don’t build a huge complex database locally and then attempt one large (and long) publish

This entry was posted in FAQ. Bookmark the permalink.

Leave a Reply