Microsoft Access 2007 and SharePoint

This is a very quick post having just completed my last one 15 minutes ago. This post will be a placeholder for some topics that probably deserve more attention. That is, I’m interested in sharing some random thoughts before I forget them myself.

Reporting and report creation via wizards or other simple means by users of SharePoint is technically non-existent with (maybe) the exception of clever list views, KPI’s, and third-party components. Microsoft Reporting Services can “tie” into the MOSS list web service to help minimize the pain but I understand this method to be a bit “fiddly” and otherwise cumbersome to use.

One often overlooked option is using Microsoft Access 2007 with SharePoint. This does sound a little funny and MS Access is often perceived  as a rogue element within the enterprise. Let me spell out two simple ways MS-Access 2007 can be integrated with SharePoint sites in a scalable way.

The first is that an MS Access DB can be exported to SharePoint. Did you know this? Access tables become linked to MS Access generated “site lists” located within a SharePoint site. This is not my favorite use of MS Access or SharePoint because it creates a large set of dependencies and is likely difficult to manage long term. Also, SharePoint lists have greater physical limits as compared to database files (more on that some other time). Having said that I’m certain “use cases” exist to justify such an option.

A second method where MS Access can be used (and my preferred way) is to use MS Access as a reporting tool only. The way I’ve done this is as follows.

1. Create a blank database in Access

2. Create a Document library on your site (call it Reporting)

3. Export the Access Db into your new “Reporting” document library

At this point your Access DB is ready to be used.

4. Click on the Access file and open it from SharePoint

5. Using standard MS Access techniques link to the lists on your site

6. Construct reports or run the report wizard.

Uploading is not required but allows others to see the generated reports or run queries on the site.

Once you’ve created reports your site users can look at the database in ‘read-only’ mode or update the reports in real time (depending on authorization). Although not totally seamless this method of MS Access integration allows your site a high degree of versatility with regard to generating and viewing reports. I’ll update this post with a few images and further commentary at a later point.

One thing to keep in mind, Access 2007 must still be installed as a client (hence the reason I say its not totally seamless). 

The MS link on this topic is here (a dull read but with important implications) – http://office.microsoft.com/en-us/access/HA012303141033.aspx

Here is the MS Access Team blog using the tag SharePoint

http://blogs.msdn.com/access/archive/tags/SharePoint/default.aspx

Enjoy!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s