Sunday, October 28, 2007

Advantages and Disadvantages of Using Microsoft Access

I've answered this question in some form or another far more times than I care to count.  Most often it's a question of "why do I need a fancy Web application when I can just build this myself in two days in Access.  I mean, the data's already in Excel."  So I figured I'd post out what I threw together, I know I've missed some points.

Overview

Microsoft Access is an ideal solution for relatively small datasets and a limited number of users. From the Microsoft Web site:

“As a desktop database, Access is well suited for small, departmental applications. These applications may start as one user’s project. For example, an employee realizes that productivity can be increased if a paper-based process is automated with an Access application. Other users in the department recognize that they can take advantage of the application if additional features are added. As more features are added, more employees run the application. As time goes by, more and more Access applications are deployed for different business units.

With increased use, the limitations of a desktop database become apparent. Access security, performance, and disaster recovery features are not robust enough to manage an enterprise-level application. Because you need a new solution, you decide that the applications should be migrated to SQL Server.” (Source http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/bldsysarch.mspx)

Additional Sources:

Multi-User Support

While Microsoft specifications allow for up to 255 simultaneous users, multiple sources have found that no more than 12 users can successfully use a networked Access application before the delay in connections through the file system pose a problem. Also note that users must be attached to the local LAN and have access to the shared networked drive hosting the Access application. This is not a problem for small teams in one location, but once teams are spread across multiple locations the application access slows down significantly for all users.

The maximum concurrent user count listed by Microsoft assumes that the data is stored in one Access Database while access to the data comes from a separate Microsoft Access interface, such as an Access Application Project. In this instance the Access application resides on a user’s machine while the Access database data resides on a shared network drive to which all users need Read/Write access.

While security is often not a consideration when deploying an Access database application, note that complete security can only be attained through folder security permissions. While users and roles can be defined in an Access database, it has been found relatively simply to circumvent these permissions and gain full access to the system. For secure access network administrators will need to grand and deny access to shared folders to the specific users who can access the database.

More inherent in Microsoft access is the inability to easily use profile information about users. It is not trivial to store profile specific information about a user. For example, if a user is in one project, for them to see only their data in their queries, instead of data from all projects, data must be pulled when a user logs into the system from a table and stored globally. Then all queries must code in access to this property. Web application tools such as ASP.NET provide profile information built-in, and adding these filters to stored procedures is fairly trivial.

Most government organizations create a standard version of Microsoft Office across the organization. All users have the same version and service pack of Office, allowing for simple deployment of the application. An Access database or Access Database Project can connect to a remote Microsoft Access database and retrieve information.

Whenever a modification is made to the underlying database or user interface a new version of the Access Database must be loaded on the desktops of all users of the system.

Scalability

For relatively small data sets Access works very well for providing flexible information retrieval. It provides simple functionality to create pivot tables and generate graphical reports. Connections to the database are made using Jet or ODBC database connections. These connections work in small levels, but have more overhead when sending and receiving information.

While up to 255 simultaneous connections are supported, this works in an environment where all users are on the same LAN (local area network). Once users become distributed across multiple locations, connections to the database slow down dramatically. This is primarily due to the fact that all connections to the database must be made through a mapped drive connection. Connecting to a remote mapped drive is much slower than native SQL Server or other database connections.

Microsoft Access also has a file size limitation of 2 GB of data. While there is very little possibility of exceeding the file size in many cases, the amount of time needed to retrieve data for a query or report becomes exponentially long as the number of rows in a table increases. When a table exceeds tens of thousands of rows processing time for a query becomes minutes instead of seconds.

Very often to retrieve information formatted in a way that is usable multiple nested queries are needed. Generally 3 or 4 nested queries are needed to retrieve the information in a usable format. In some cases one query is run to generate a table, after which another query is run to generate another table. This is done so that the final query can run quickly, but requires multiple steps by a user to generate the data in a usable format that can be retrieved in minutes instead of hours. This also adds a very large amount of work to make changes to a query or add additional data, as all underlying queries must be modified.  Confusion can easily occur as unused tables exist in the database, often where only the database creator will know which tables to use at which time.

Reporting

As long as a strong understanding of SQL queries is understood and deep understanding of the underlying tables, reports can be created to extract and display information, including the ability to export the data to Excel or many other formats easily.

These reports use the underlying queries to generate and format their data. They work very well, though not always quickly for large calculations or formatting. Generally a more robust reporting tool such as SQL Server Reporting Services, Crystal Reports or another application will retrieve this information faster and provide the same graphical and export options. The disadvantage to these solutions is that quickly creating a new report is not as easy as it is in Microsoft Access.

Data Import

Importing data into Microsoft Access is relatively simple for simple amounts of data. Performing large data imports and formatting of final data is more difficult in Microsoft Access than it is in more robust database tools such as SQL Server and Oracle. This is generally due to the modified version of SQL available in Microsoft Access. Fewer functions exist for formatting, extracting and updating data into disparate tables. Often to extract a specific set of data, such as a list of locations, the database administrator must create a query for that one action and run it to populate a location table. More robust tools allow for multiple queries to run at one time, and provide transactional processes, so that if any part of the import fails all changes are rolled back to a state before the import began.

Remote Use

For a user to use the system from a machine away from their office, they will need to have the same version of Microsoft Access installed. While this is not often a problem in government organizations, as mentioned above, it does require that users have access to the shared folder wherever they are located. Web based applications allow a user to access the system from any location that has Internet access.

Web applications also allow for seamless updates to the system. If a new field is added to a form or report, the code is placed only on the Web server, and the new form appears to the user the next time they access or refresh a page. In an Access application, the new code must be sent to all users of the system. Without additional coding in the Access application to ensure the latest version is always used, it is possible that a user could use outdated code to update the application. This can allow for invalid data to be entered or invalid reports to be generated.

User Accessibility

While Microsoft Office programs now meet (as of Office 2003) the governments’ standards for Section 508 accessibility, there is no guaranty that applications created in Microsoft Access meet those standards. If forms are used in the access application, more care must be taken in providing keyboard access and linking labels to fields for screen reader access.

While additional coding is also necessary for Web applications, many mature Web application languages, such as ASP.NET 1.1 and 2.0, provide simple methods to make applications accessible. Most user controls generate Section 508 compliant code by default, while others provide properties to make the control compliant.

Wednesday, October 24, 2007

Sharing Information

Why is it that when we're in school we write long papers that are only seen by the teacher/professor?

I'm currently writing out my personal view of how to teach all the different ages in the church to grow a deeper relationship with God.  It's a paper stating my personal philosophy based on my experience and what I've learned in class.  What I wouldn't give to see what everyone else wrote as well to find things which I might have missed or ideas I hadn't considered.

Why do we write papers that are essentially for ourselves and no one else?  How hard would it be to set-up some group which publicly shares the papers written for a class?

Peace,
+Tom

Thursday, October 18, 2007

SQL Server 2005 Reporting Services on Vista

I've been in a very technical mood as of late.  There's something about starting a new project, putting together a new site, and doing things I've never done before.

One of those things was to set up SQL Server 2005 Reporting Services so that we can easily integrate graphs and tables and drill-through reports.  It doesn't hurt that it comes free with SQL Server either.  So I set to installing SQL Server Reporting Services on my Vista laptop and begin developing reports.  Here's what I did and bits of advice.

Set Up

I previously had Microsoft Internet Information Server (IIS) installed (the hoops I jumped through for installing that are for another time), Visual Studio 2005, SQL Server 2005 Express and SQL Server Express Enterprise Manager.

I decided to download SQL Server Express Advanced, which includes a stripped down Reporting Services engine.  I went through the install and . . . Report server wasn't an option for the install.  This was not a good sign for the rest of the process.

It turns out I was missing one component in IIS.  So, after a quick Control Panel -> Programs -> Turn Windows Features on or off.  Then expanding Internet Information Services -> World Wide Web Services -> Application Development Features and checking ISAPI Extensions (okay, not so quick) I was well on my way.

Windows Features

I must admit, in the middle of figuring this out I uninstalled SQL Express and installed SQL Server 2005 Standard.  I thought that was the problem, and while it wasn't, I do like having the ability to easily import data (a feature not available in the Express version of Enterprise Manager).

I finally came across this knowledge base article (KB 934164), which helped immensely.  I won't go into all the steps here, since the article details them very well.  I do recommend grabbing this article before you go through the process.  But there were some gotchas, one in particular required a Microsoft Support call.

No matter what, once you install SQL Server, or later add Reporting Services, you have to install SQL Server 2005 Service Pack 2.  This knowledge base article (KB 913089) explains how to get it, though on my machine Microsoft Update automatically found and installed it.

Configuration

First gotcha once everything is installed was related to the Default Application Pool.  While under the Reporting Server Configuration screen in the Web Service Identity tab, the Classic .NET AppPool needs to be selected.  The default application pool requires web.config the be reformatted and, from what I can tell, breaks report server.

Other than that one thing though, installation seemed pretty simple.

Now it was time to run the application, right?

The first step there is to open Internet Explorer as an Administrator.  Any Vista programmer knows the drill.  Right-Click Internet Explorer and choose Run as Administrator.  Then we go to http://localhost/reports.

Turns out though, that that doesn't work.  Even though I was running IE with administrator privileges, the site didn't recognize the elevated access.  The ever important "Properties" tab wasn't showing up. This is where the Microsoft support call came in.

It turns out, in my case anyway, that you need to turn off that annoying "confirm" box you get every time you run something as administrator.  So, back to Control Panel -> User Accounts -> User Accounts (it's not a misprint, to get your own user settings, click it twice) I was able to Turn User Account Control off and reboot.

UAC

Once I had the user account control (UAC) off I opened up Internet Explorer.  I went back to http://localhost/reports and viola, I saw the properties tab.

Following the directions in the above knowledge base article, I diligently added my own user ID (with computer name, so it was computername\userid) to the Content Manager group.

With that done I turned UAC back on and rebooted.  While that confirm box is annoying, I still recommend it.  I've worked on too many XP and earlier computers where teens or adults install stuff without even realizing it.

Finally, I had access to the report manager and was ready to rock.  When all is working you should see this.

ReportsSite

Sadly, I now have an problem opening the ReportService2005.asmx file.  Every time I try it wants to download the generated XML file instead of displaying the friendly screen.  Oddly, I see the XML inline in Firefox and my own test Web Service works.  But I suspect that is more particular to my machine, and I'm awaiting a call back from Microsoft on it.

I'm not sure whether this really helps anyone else.  But after days of struggling with it I finally decided to get my experience and tips down someplace so I'll have it when I need to install it all again in the future.

Peace,
+Tom

Saturday, October 13, 2007

Clicking with Faith

I wanted to follow on from my previous post about things just clicking in our brains and understanding comes.  I know this click phenomena comes into play with relationships and I think that flows into faith and a relationship with God.

We spend our whole lives looking for people we can connect with.  When we find that friend we think we can understand, and who understands us, something just clicks.  A bond is formed between us and that other person.  We just understand each-other.  Even after years of being apart it becomes easy to come back together.  when the relationship is severed by one person hurting the other, that hurt may often come not from the deed, but from not being able to understand the actions of this person we thought we knew.

The same is true of faith. 

People are looking to understand.  They want to understand how this God can let people they love die.  They want to understand how this God can let someone be brutally attacked.  They want to understand how someone can love them even when they don't love themselves.

Once the click happens though, something changes.  Heck, it's even gotten it's own term, being "born again".  It's impossible to explain to someone who hasn't had the click happen, since it's all about feeling and personal understanding.

It all starts with a click.

That click lets you understand that you can't understand everything and you can't know why every single thing happens in life.  It lets you be complete without understanding and gives brings you comfort from someone you can't see or touch.

Click,
+Tom

Click

The way my brain works I often need to think about something for a while, often watching someone else or absorbing a picture before I really understand it.  But, once that understanding comes somehow I'm able to really get what's involved in making it happen and it seems like only serious practice is needed for me to get good at it.

More often than not that insight and understanding comes in an instant.  My brain clicks into place and I almost want to shout out "I GET IT!"

This morning I was watching a juggler on Sesame Street and as I watched him throw one object up just enough to pull the one in the air out of the way, my brain clicked.  I understand what's involved in juggling.  Not all the details, how gravity is involved, how to juggle chainsaws or anything, but I get juggling.

The same was true of programming and computers.  Years and years ago something just clicked.  While college refined this even more to a 0 and 1 level, I understand what's involved in the hardware, software and storage of digital information.

I was also watching some world renowned pianist on Sesame Street and I realized it.  I'm not the only one things click for. While I could never figure out how to use two hands at the same time to play piano, this guy is closing his eyes and music is flowing out.  The piano playing just clicked at some point, and he's been able to really understand and express himself through music.

So, am I and this great pianist the only ones this happens to?

Peace,
+Tom

Thursday, October 11, 2007

Involved vs. Committed

Chirck riding a pig I thought everyone had heard this by now, but I was in training and a bunch of people never knew the comment. So, here you go, share it wherever you see fit.

In a bacon and egg breakfast, the chicken is involved but the pig is committed.

Update: Seems a little more explanation may be in order. With a bacon and egg breakfast, the chicken is involved. Her eggs are taken to feed the people. The pig though, he's committed. Instead of taking something he produces, they take him and, uh, put him down, so that people can have their bacon.

Peace,
+Tom

Miracles and Faith

I just came across this quote:

There's no way to explain a miracle . . . For those who don't believe, no explanation is possible.  For those who do, no explanation is necessary.

I shouldn't mention the source, but it's Trance from the TV show Andromeda.

We spend a lot of time trying to explain miracles.  This quote though, I need to remember.

Peace,
+Tom

Sunday, October 7, 2007

The Bible for Non-Christians

The bible Most of the non-Christians I've met don't believe that the Bible is strictly true.  So why do we always use the Bible as factual evidence to prove the existence of God and Christ?

I'm not saying that the Bible shouldn't be used to explain God or how to live.  I do think that it shouldn't be the only thing we use.  Thinking about this more though, I can understand why we do use the bible so much.

For anyone who has accepted Christ, the bible is the place to look for answers and refresh our faith.  I do think Christians spend far too little time actually reading the bible.  While we definitely don't need to spout off bible verses at the drop of a hat (the more I'm in seminary the more I see people do this . . . I guess that makes them more faithful than I) we do need to understand what the bible says and try to see how it applies to our life.

I talk to a lot of people who what to believe in God, or really do understand that He exists.  At the same time they see the bible more as general stories passed on instead of factual events.  They are stories to explain life.

Maybe the reason we talk about the bible so much is because we do recognize that it's the best source on how to live.  In some cases it's the only source of our history.

There is no way that we can ever force someone to believe in God.  We can't convince them through proof or explanation.  A person's relationship with God is between that person and God.  There's nothing I can do directly to change that path.

So, maybe it's okay that we use the bible as our evidence.  We've decided that we have faith in something we cannot see, and in that faith we accept a book which can't be unilaterally proven (yet).

All we can do is live each day in that faith and following that book.  We'll let God help those others who aren't quite there yet.

Peace,
+Tom

Saturday, October 6, 2007

One Laptop Per Child

I heard about this a few days ago and the more I read the more I love the idea.  Laptop.org has the simple vision of getting a laptop in the hand of every child in the world.  This is something I began thinking about a couple years ago, that having Internet access all over the world is a necessity for 3rd world countries to survive. 

While many people argued that they a roof over their heads and food to eat first, I have to disagree somewhat.  Those things are critical to living.  At the same time, the Internet puts in their hands the ability to learn new ways of farming and building.  These guys have community done better than us Westerners can imagine.  Giving them more information than they've had before can change their world.

There was even an Escape Pod podcast story about the impact this can make in Transcendence Express by Jetse de Vries.  Here a teacher goes to a 3rd world country with her husband and begins introducing computers in the classrooms.

In any case, XO Giving has created an inexpensive laptop which is slimmed down to focus on doing what it needs to do extraordinarily well.  The Web site does an ok job explaining, but this NY Times article really explains it all well.  The idea is that for $200 I can buy one of these laptops to have them donated to 3rd world countries.

Why do you care?  Well, starting November 12th, they'll have a buy one get one sale.  So, if you buy two laptops, one will go to you and another will go to someone in the third world.  Both of which you can deduct from taxes.

I'm seriously thinking about it for Rachel.  Regardless of what I do though, this is just an incredibly smart idea which you should take advantage of.

Peace,
+Tom

Tuesday, October 2, 2007

Whose Your Hero?

Suresh from HeroesI came across the Which 'Hero' are you? quiz and figured I'd share.  In case you didn't know, there are some TV show's I'm completely addicted with (Heroes, Battlestar Galactica, Dr. Who, Monk and Psych).  Heroes is definitely near the top of that list for it's story and character development.  When given the opportunity to find my super power, you know I'll take it.

Turns out I fail at getting super powers.  I'm Suresh, who has no discernable super power besides that he gets to talk during the opening and recap events.  I guess I'll have to live with the innate abilities the quiz says I already have.  Here's what it says:

Your "power" is in your leadership and your ability to organize those around you.  You may not be able to fly or have incredible strength, but your great knowledge makes you a real superhero.

Psh, I wanted to be able to bend time!

Peace,
+Tom