jump to navigation

The Right Tool 03/02/2009

Posted by TBoehm30 in Database.
Tags: , , , ,
add a comment

We all know that when all you have is a hammer, everything looks like a nail.

My database skills gravitate toward MS Access because that is what I have the most experience in. If somebody needs something quickly, I point them toward Access because it would take longer for me to learn a new tool.

Oh I know that there are plenty of database tools out there – Toad, SQL, Oracle, Table, etc. (Forgive me if I don’t know your favorite.) I know that MS Access may not be the best simple tool for my problems, but it is the one I can use the quickest because I already know it.

So, when I got an IM from a colleague with the query “How good are you at Access?”, I had to wince. Do I say the truth knowing I’ll get roped into some project, or do I brag so I can help my friends, seem really good and get on another billable project? OK, I answered ‘Great’. Of course, I got roped into the project – luckily it turned out to be pretty simple.

She needed a form to enter data that would create MTM records. MTM is Many-To-Many. For anyone who doesn’t study databases for a living, here is the explanation. If you have 2 tables in a database and want to link them (for example link the state table to the country table) in a 1 to many relationship (each country has multiple states, but each state only has 1 country) then that is simple. The first table has a field which points to the ‘many’ table (the state table has a country field).

However, if you want to create an MTM relationship, then you need a new table. The new table can be small because it just points to each of the original 2 tables. For example, you have a Teacher table and a Student Table. You would need an MTM to list out the students who are taking a class with a single teacher, or list the teachers any given student is taking a class from.

She already had her database design with relationships defined and data in some of the tables. She just needed an easy way to select in ‘initiative’ and link it to several project records. She had spent some time in Access, but felt she was burning too many project hours providing no value to the client. Since she is a good responsible consultant, she called in for help.

MS Access has a fantastic wizard. If you are building a form for a 1 to Many relationship system, it will create your form with numerous options. This is out-of-the-box. After a couple of false starts, I used the wizard to create a form that had the initiative table on a form with the MTM table on a subform. Then I created a form with just the project table on it, and added that as a subform to my first form. Now I could choose an initiative, and easily scroll through the projects to find the one(s) I wanted to link to.

The only problem was that when I selected a project, it didn’t automatically feed the ID to the MTM table. For that I needed code:
[Forms]![ExistingProjectInitiatives]![InitiativeProjectMapping Subform]![Project_Title].Value = [Project_Title].Text
That only took a few minutes of research on the web plus some experimenting to get right in Access.

As soon as I started testing it, I realized that the MTM table was getting edited when it should have been creating a new record. I needed to automatically move the cursor to the new record in the MTM table whenever I changed the initiative record. Back to the web. It turned out I needed 3 more lines of code:
DoCmd.GoToRecord acActiveDataObject, , acNewRec

Viola a simple form that she could use for her purposes. I did all of that in under 2 hours, even though I hadn’t so much as looked at Forms in Access in around a decade. I love having good technology.

Having the right tool for the job makes all of the difference. When software works well, it just makes me happy, because It’s a global world out there, and technology makes it happen.

Followup: I have already had to spend some time in Tech Support mode fixing a problem with her database. She made a change to the schema which made one of my new forms misbehave. When will I learn? No good deed goes unpunished.