Ninja Chat
So, there has been a rare occasion in which boredom has set while I’m working in the office. Granted, after Nick and Robert left, boredom hasn’t shown up in a long while. But just before Nick took off, I decided it would be fun to build a chat program in MS Access. Yeah, Access. I’ve been working on this in any free time at home, so it hasn’t taken more than 8-12 hours of actual coding. But there’s a lot of this I’ve already built for other tools at work, so that’s not a real estimate of how much time went into it.
I probably know what you’re thinking. Things like, “Why would anyone use Access? For anything?” and this is a question I’ve asked many a time. Especially to managers in my company who complain about a lack of scalability and poor performance, though they insist on using Access for everything. I can say I have been rather impressed by what can be done in Access. You actually have the opportunity to build on a true enterprise scale if you’ll take the time to write the code and you don’t depend on access to create the link to the backend. This provides an impressive ability to scale even if you’re using an Access backend instead of a SQL Server (or Oracle, MySQL, etc.) backend. Twenty or more users can all access the application’s data at the same time without any noticeable delay, if you build the architecture. That being said, here’s a little more information about how the database and application work together:
Since they won’t give me a SQL Server to play with, and it’s against policy to put one together myself, I’ve built a fairly useful class to handle all interactions with backend Access files. It deals with location and login information for getting to and accessing the backend, SQL statements for all the updates and returning record sets, and that sort of thing. There’s a wrapper module, that I call mdlDatabase, that the application actually uses that sets the common database path and login information and this is the module that returns all the record sets to the application.
I’ve also got an encryption module that handles the standard issue encryption/decryption/hasing functionality. The login information is stored as a low-bit(40, I think) MD5 hash, and the display names use some weird encryption algorithm that isn’t too bad. No idea if it’s a standard or not, so it’s probably not, but it does use a common seed to start out the encryption and each letter after the first uses the previous letter’s encryption as a seed. Not incredibly sophisticated, but it’s not something people I have to deal with could break. I found both of these functions on the Internet and adapted them to meet my needs in mdlEncryption.
There’s another module that handles all of my common tasks. Here common means tasks that are common to all of my applications. Tasks like making sure the currently running instance is the most up to date version of the application (it checks the backend to see if there’s a new frontend available and where it’s located, then copies it to the local location and creates a VBScript to overwrite itself), some basic error handling, and a few other things.
I’ve got another module that handles application specific functions, so it’s basically a wrapper for any stored procedures (queries, really) that I need in the application. This module (mdlFunctions) is responsible for doing most of the work that the forms are used for like inserting a chat line in the database or getting the most current messages in a chat room.
Then I’ve got my user module. This module handles all my user specific things from informational requests like GetUserDisplayName, to updating passwords and creating new users in the database. This is the part that I’ve had to work most on for this database. I once built a class for handling this stuff that had a complete rights management sub-system that was role and right based. It was pretty slick, but I’ve changed the way UserIDs are based, so I started over. I’ll be adding this back before too long, though.
Right now the tool uses a single form to control and display a single chat room available to all users. I built the structure so that it can handle multiple rooms, but we haven’t had a need to use that yet. Next on the list of things to do include adding a private chat feature so two users can chat securely, adding a buddy list to show who’s active and inactive, adding preferences, adding a rights management system, adding a menu building system that will create a menu based on the user’s rights and preferences, and adding sound and visual indications of new messages.
Once again, I have to say I’m impressed by how much one can do in MS Access. You’re really only limited by your creativity and dll libraries. If there’s any interest, I can post some screenshots and maybe some code. If you’re interested, let me know in the comments.