Access file size with SharePoint lists -

Question

I am using Access as a front end, with many linked SharePoint lists as the data source. The database has no native tables; all data is stored in SharePoint lists. There is significant code and several forms.

I use the SharePoint list as a data source because (a) it is a data source easily accessible by the entire team that uses it, (b) my company restricts the availability of other sources - I'd prefer to have an SQL data source available, but this is not easily done - and (c) the data is company sensitive, and an off-site resource isn't an option.

As I've added code and forms, the file size has understandably grown, but it is MUCH larger than I would expect from a database that houses no data (currently over 21 meg).

I have no performance issues, other than the speed of the link to the SharePoint resource. That has not changed over time - the database performs about the same now (at 21M) as it did when it was a fledgling 10M at it's creation.

My question - should I expect a database with no inherent data to be this large? It seems to me that there is no way some forms, queries, and some vba are driving 21M of storage.

Thank you for you time - Mike.

Answer 1

Assuming you do a frequent compact and repair, then that should give you the smallest size.

I would check if forms have “image” backgrounds or “images” to fancy up the form. If you using the “older” image format, make sure you set Access to use the newer format.

Also keep in mind that Access 2010 and later will “copy” or better said cache a copy of the SharePoint tables local. This is done for reasons of performance. The setting that controls this is file->options->

Note that in above you can use “clear” cache on exist, but that results in a huge performance penalty. So best use settings as above.

So the SharePoint tables are copied local. (There is a local copy of the data). This would explain the rather large size despite you using linked tables to the data on SharePoint.