Microsoft Access encourages developer carelessness

After evaluating a problem that a teammate at work was having today with regards to importing data from a Microsoft Access database to a Microsoft SQL Server database, I came to the conclusion after solving that problem that Access allows a user to do things that may not be Kosher when it comes to proper programming.

I will admit, that when I first started out in dynamic web development, an Access database is all I had access to (please mind the unintentional pun). As most young developers, I did not have the money/knowledge/resources to develop using a higher end database. Microsoft Access was designed for the basic user in mind, and though it is capable of working with web applications I would not suggest using it for an application that is larger than the scope of your personal database needs ( i.e. DVD Catalog, Pet's Medical history etc.).

The issue at hand is my teammate has been recently converting an application within our organization to the new CLAF (Common Look and Feel). Our organization has also been converting all of the Access databases that are owned by its customers into SQL Server databases. With this, he was attempting to import the old Access database to a new Sql Server 2000 database. He ended up calling me because he was having so many problems. The application is a time tracking application that notifies the rest of the team when people are logged in. The original developer of the application created three columns In, Out, and TodaysDate. The In and Out fields are TimeDate datatypes but they only stored time data, not the date. The date of the entry was stored in TodaysDate which was the same datatypes as In and Out. Normally I leave well enough alone when it comes to the way people design their applications. But the issue was that my teammate spent a lot of time trying to figure out why the database was not transferring over. He gave me a call and I eventually figured out that the problem was that SQL Server was trying to import the In / Out data into the SQL Server database. In and Out only has time data and SQL Server does not allow for only the time data to be imported. What should have been done was that the date and time data should have been included in the In and Out columns and not had the TodaysDate column at all. Then the developer should have received the date information by parsing it from the In and Out fields. This also makes more sense because in the old way, if someone logged out the next it would break the application.

My point in all this ranting is that Access if at all possible should not be used for Internet application development. If you do choose to use, make sure you use it wisely. Again, I am not saying don't use Access cause if you don't have the resources to use Microsoft SQL Server or similar solution it is a decent solution.