I hate Access 2007 #1
I'm just trying some of my recent Access 2003 code in Access 2007 for the first time, just to get a feel for how it's going to port, and to test my version identification code. Things are getting screwy already, so it's time to start a new venting log...
So, in summary, 'Field2' objects come out of the 'TableDef.Fields' collection instead of 'Field' objects, although some references I've found suggest that this is only the case for Access 2007 tables, so presumably the objects that come out depend on what version of database file you're getting schema information from. That's incredibly stupid. And the 'CollatingOrder' property seems to be Missing In Action in the 2007 table design, even for old-format database files, despite the fact that the underlying JET database engine still has it. I expect supporting any language other than "dbLangGeneral" somehow aids and abets Terrorists. Who cares about Iceland anyway.
Oh, and the documentation is a whole new kind of awful, with almost no example code, no quick access to method lists or return types for top-level objects like 'TableDef' or even 'Database' for goodness sake! Developing in Access 2007 feels like there's someone unpleasant constantly looking over your shoulder, and regularly beating you with stick, for having the temerity to do outlandish things like write some VBA code instead of just using the Wizards.
I want the last three hours of my life back, please. And a long shower, to wash away this dirty, dirty feeling.
- Access 2003 persisted in re-casing my 'fld as Field' declarations to 'fld as field'. VBA in 2007 seems to be case sensitive, so of course this failed to compile. This might be less annoying if I hadn't fought 2003 for ten minutes to get the case right.
- WTF? I can't delete properties from property collections in 2007? 'fld.Properties.Delete "Name"' worked fine in 2003, but they've removed the method in 2007! I'm trying to set the property to NULL as a compromise.
- Oh Great. The field property 'CollatingOrder' has been deprecated, as an explicit object property anyway. Wonder if it's still in the Properties collection.
- Ah, there's the other shoe. The 'Append' method for Properties collections has been removed as well. That's going to break a lot of of the more advanced code out there which attaches extra metadata to schema objects.
- Oh, and according to the documentation, intrinsic 'built-in' properties no longer appear in the properties collection. This is going to get nasty. So what, exactly, is the point of the collection if (a) the built-in ones aren't listed, and (2) you can't manage dynamic properties?
- Of course the one thing I'd hoped they would drastically improve is the one thing they left completely unchanged in a sea of interface 'upgrades': Table Index design uses the same old dodgy datasheet as always. In face table design in general looks mostly the same.
- Well, it looks like the documentation lied, and the intrinsic properties are still in the collection. So is 'CollatingOrder', although it's no longer accessible through the table design interface.
- As an aside, I found an interesting little blog entry by a microsoft developer called Extending collation support in SQL Server and Jet, Part 0 (HISTORY) which talks about how the collation engine in JET came to be. I'd read more of his blog if I had the time. Love the "Terror Alert Level" in the sidebar.
- Apparently the 'Field' and 'Property' objects aren't what I think they are, since I can't assign items from the relevant collections to variables of these types. So I've made all variables and function parameters Variants, and now things work, even if I don't like it.
- Aha! 'TableDef.Fields' returns a collection of 'Field2' objects. And the 'Properties' collection on that object has 'Append' and 'Remove' methods. Well, obviously! If the documentation for 2007 wasn't another degraded step downwards from the already awful 2003 docs, I would have been able to figure that out a lot sooner. There's no equivalent 'Property2' object to fix my type problem with properties, but IntelliSense does seem to be telling me there are two 'Property' objects in scope. No idea why.
- Right, it seems to be working now.
So, in summary, 'Field2' objects come out of the 'TableDef.Fields' collection instead of 'Field' objects, although some references I've found suggest that this is only the case for Access 2007 tables, so presumably the objects that come out depend on what version of database file you're getting schema information from. That's incredibly stupid. And the 'CollatingOrder' property seems to be Missing In Action in the 2007 table design, even for old-format database files, despite the fact that the underlying JET database engine still has it. I expect supporting any language other than "dbLangGeneral" somehow aids and abets Terrorists. Who cares about Iceland anyway.
Oh, and the documentation is a whole new kind of awful, with almost no example code, no quick access to method lists or return types for top-level objects like 'TableDef' or even 'Database' for goodness sake! Developing in Access 2007 feels like there's someone unpleasant constantly looking over your shoulder, and regularly beating you with stick, for having the temerity to do outlandish things like write some VBA code instead of just using the Wizards.
I want the last three hours of my life back, please. And a long shower, to wash away this dirty, dirty feeling.