Displaying an Access Report in a Web Part

ok .. so who uses Microsoft Access anymore ???

 

You might be surprised that the Report Designer is becoming increasingly popular now that the ability to link to SharePoint data has been added in. From any SharePoint list you should be able to select "Actions" and then "Open in Access" .. which then gives you the option of taking a copy of the data, or (even better) creating a link to the data in SharePoint (allowing you to report and query the SharePoint data direct from the SharePoint database!!)

 

This of course opens up the possibilities of non-technical end users creating rich reports using nothing more than Microsoft Access and a couple of out of the box web parts.

 

I won't go into all the detail about Access Snapshots .. you can find all that (and more) here.

 

I must give a nod of thanks to the guys over at ProgrammingMSAccess, their samples on embedded Snapshots are the ones I am using in my web part.

So .. how can we get up and running?

 

First, add a Content Editor Web Part. Enter the following code into the Source of the web part (by modifying the web part, and using the built-in source editor).

<OBJECT ID="SnapshotViewer" WIDTH=800 HEIGHT=600

CLASSID=

"CLSID:F0E42D60-368C-11D0-AD81-00A0C90DC8D9">

<PARAM NAME="_ExtentX" VALUE="16722">

<PARAM NAME="_ExtentY" VALUE="11774">

<PARAM NAME="_Version" VALUE="65536">

<PARAM NAME="SnapshotPath"

VALUE=

"http://webserverURL/folder/snapshotfilename.snp">

<PARAM NAME="Zoom" VALUE="0">

<PARAM NAME="AllowContextMenu" VALUE="-1">

<PARAM NAME="ShowNavigationButtons" VALUE="-1">

</OBJECT>

The only part you have to change is the "VALUE" attribute for the "SnapShotPath" node (I've highlighted it for you to make it easy ;)). This needs to be a URL that points to the SnapShot file (preferably in a Document Library in your SharePoint Site - Anyone viewing this report will need Read access to it.).

That is pretty much all there is to it. Feel free to play about with the "Width" "Height" "AllowContextMenu" and "ShowNavigationButtons" ... I'm sure they are all pretty straightforward! ;)

One final note - All users who want to view the report must have Snapshot Viewer for Microsoft Access installed on their workstation (otherwise the web part will just be blank).

Example Report - Shown on a standard Web Part Page:

 

SQL Server Feature Comparison (2005 – 2008)

This is something we've had under our hat where I work for a while.. as you should be aware if you do a "stand-alone" install of WSS 3.0 (or MOSS 2007) then it installs "SQL Express Edition" (a.k.a. MSDE) .. but this version does have some startling limitations!

 

SQL 2005 Feature Comparison

	Express	Workgroup	Standard	Enterprise
Number of CPUs	1	2	4	No Limit
Max. RAM	1 GB	3GB	OS Limit	OS Limit
64-bit support?			X	X
Max. Database Size	4GB	No Limit	No Limit	No Limit
Clustering / Mirroring			X	X
Online Restore / Recovery				X
Management Studio		X	X	X
Job Scheduling (through Agent)		X	X	X
Import / Export		X	X	X
Web Services			X	X

 

Much more also available at Microsoft SQL Server 2005 Features Comparison.

 

SQL 2008 Feature Comparison

	Compact	Express	Standard	Enterprise
Number of CPUs	1	1 (per server)	4	No Limit
Max. RAM	OS Limit	1GB	OS Limit	OS Limit
64-bit support?			X	X
Max. Database Size	4GB	4GB	No Limit	No Limit
Clustering / Mirroring			X	X
Online Restore / Recovery				X
Management Studio		(tool)	X	X
Job Scheduling (through Agent)			X	X
Import / Export	(tool)		X	X
Web Services			X	X

 

Detailed comparisons can be found between Compact and Express … and Standard and Enterprise

Some things to note about using "Templates"

I’ve been doing some testing, mainly prompted when I was questioned about Site Templates (I originally thought that Workflows built in SharePoint Designer would not be copied over in templates) and they proved me wrong! I have done some reasonably extensive testing, and have come to the following results:

Quick Summary

• SharePoint Designer Workflows will ONLY be copied over in a SITE TEMPLATE WITH CONTENT (any other configuration fails)
• Custom Workflows (including the “out of the box” ones) will be copied over in ALL TEMPLATES.
• If you hide the “default” content type then it will re-appear after using a Template.
• Version history is not preserved when using templates with content.

List Templates include the following items:

• Custom Workflows
• Adding more Content Types
• Re-ordering the Content Types
• Changing the default Content Types
• Adding a custom Content Type
• Versioning Settings
• Content approval Settings

Site Templates include all of the above, but if you also select “Include Content” then you will also get “SharePoint Designer Workflows”!

If is important to note that if you hide one of the “out of the box” content types from your list then saving a List Template OR Site Template will not have any effect! The content type will be back and visible when your new list / site is provisioned!

I then did some more general “what if” scenarios:

 

Query	Answer
What happens if the custom Content Type in the Site Template does not exist in the target system?	It creates a LOCAL (i.e. list level) copy of the Content Type, and links it to the parent of the original content type. This means that it works as it used to work, but the “central” copy of the content type no longer exists so maintenance has to be done manually and changes cannot be “pushed down”.
What happens if you delete a Custom Content type while it is in use normally?	Error – “The Content Type is in use”
If you save with content, does it preserve version history?	No. All items copied over will be created as version 1.0. All draft versions are removed.

 

It is also important to state the Disadvantages of using Templates and that is mainly regarding Site Templates, and it is 3 main things:

• Performance. Using lots of site templates will degrade the performance of your system.
• Maintability. Maintaining the structure going forward will be difficult, as you have no “schema” to modify. All you can do is
• Limited to the User Interface. Mainly with reference to lists, changing the default fields, default content types, and modifying some of the “back end” properties is not possible through the user interface, and therefore cannot be achieved using “List Templates”.
• Automation. There is still much more flexibility in terms of automation when using “definitions”. We can tie in event handlers and automation tools to do “almost anything”.