Exposing SQL tables so that user can indicate 401(k) participation

A little bit more about the corporate 401(k) application, along with a discussion about how our SQL Server databases are set up.

We have clients set up that for different business reasons we've put into separate databases. We have a dedicated server for all the individual SQL server databases. As the company continues to grow, we've added new databases.

One of the requirements for 401(k) reporting involves looking at participation. When I first developed the application earlier last year, I set it up to define participation as when an employee of a client contributes to the 401(k). I found out that many clients sign a participation agreement often a month or more before any of their employees start contributing. So I needed to change how the program defines participation.

To that end, we decided that it was best to have a single table in each database where we would indicate a client's participation. That way, we would look at all employees for those clients in those tables.

In order to expose these tables to the user, I needed to set up an ODBC link. I had to do this for each SQL Server database individually so that each 401(k) participation table would be exposed. Access provides this utility through its Linked Table Manager.

One thing I realized in doing this, is that the settings are machine-specific, which means that every machine using the functionality has to be set up individually. After doing this a couple of times, I documented the process for when we'd need to do it again.

So, once I'd established the ODBC links, I developed a form with a tab control. Each tab shows each table, and this makes it really easy for the user to maintain the participation information for the clients in the different databases.

In her own words, "have I told you how happy I am that you are back????" Nice.

All of this makes sense as I'm writing this, but maybe it doesn't as you're reading it. Let me know if that's the case.