Tuesday, May 29, 2012

MS Access - A Cost Effective, Powerful Business Tool

MS Access is a powerful low-cost business tool that offers excellent ROI and fills many and varied business needs. 

As the most popular database program in the world, MS Access allows the creation of very sophisticated multi-user applications with a shared back-end data source, simple desktop data manipulation, or adhoc reporting solutions from a variety of data sources.  

I have not found a data source that I could not link MS Access to including SQL Server, Oracle, MySQL, MS Excel, Word, dBase, etc.  Because of this, an Access developer has the ability to develop and deploy real-time front-end solutions that report critical business intelligence from one or more of these sources.  These solutions can either be one-offs or custom menu-driven applications that offer the selection of multiple criteria, grouping, sorting and visual representation. 

With the Access Query Designer, complex multi-table queries can be created. Queries can also use VBA or user defined functions directly in queries for sophisticated analysis and updates.

The reporting ability in MS Access ranges from simple data querying to complex grouped, sorted and summarized reports with charts, graphs and custom VBA functions.  Include sub-reports to further show multi-table relationships if needed.  Here's a few examples:

MS Access 2010 also includes the ability to design and deploy database applications to web hosted Sharepoint sites.  These can be totally browser based or desktop front-ends linked to Sharepoint lists.  Though there are size limitations, this allows the flexibility to have remote users share, add or update all or some business data.  Note the example of Sharepoint hosted reporting design:

Bottom Line Cost Advantage

MS Access offers a low-cost solution for handling data and automating business processes.  Microsoft even makes available a free runtime that alleviates the cost of buying Access for every end user. 
For many business needs, MS Access is a cost-effective and powerful tool!

Wednesday, May 2, 2012

MS Access Key From Image

I wanted to share an additional option to my earlier blog regarding Sharepoint Attachments.  This design shows the flexibility that can be built into an MS Access Web Database.

Here it is:

In my web database (as described in my blog "MS Access SharePoint Attachment Field):

Add CLIENT forms that have an unbound Image Control, place TIF images in a consistent folder on the users computer (I would automate the daily image distribution).

Run my custom PopulateImages() function that adds the image names to a field in the table.

In the OnCurrent event of the form, set the unbound image control's "Picture" to the image.

As my tables are still Sharepoint lists, I still have the administrative advantages of a web database. This is more reasonable for higher daily volumes (remember it took and hour to add 150 images to the attachment field). It also gives the advantage of distributing any updates to users automatically whenever they login. Users can use the free MS Access 2010 runtime. Either browser based or client based can be available from the same application.

Private Sub Form_Current()
Dim MImage
MImage = "C:\folder\NewImages\" & Me.ImageName
Me.ImageNew.Picture = MImage
End Sub

What do you think?