KenB - 6:24 pm on Jan 5, 2010 (gmt 0)

It's funny how people view software differently. KenB hates Office 2007, yet I love it. I guess that is because I use Word 2007 and Excel 2007 exclusively - I don't need all the other stuff in it. I swore off Outlook ages ago and my databases are far simpler than what many people here use.

If I used Word or Excel 2007 I might feel differently, but Access database development, and especially developing Access database projects, which use a MSSQL backend is much easier under Access 2003 because of easier to access menus (sorry the new strip thingy is just convoluted and confusing in Access 2007), plus many things you regularly need in Access development are now buried deep into the new menu that comes off the office icon at the top left. Worse yet certain tasks like hiding/unhiding the object browser now require restarting Access, which was never necessary before and is wholly asinine.

By default I like my databases to open with the object browser hidden as end users don't need it and hiding it removes clutter. When I'm working on a database, however, I need to unhide the objects so that I can work on them. Prior to Access 2007 I could set the object browser to be hidden by default in the database start up options and then when I needed to access it I would unhide it, the next time someone opened the database it would still be hidden. Now I have to open a database, dig deep through several layers of menus/popup windows to find the option to show the object browser, change the setting and then restart the database. Once I've completed my work I have to remember to repeat the process to hide the object browser again before I build a encrypted production version of the database that end users actually use. Something that was simple and automatic before is now complex and requires a checklist to make sure I don't forget the extra steps.

Then there is the issue with the way MSSQL 2008 and Access 2007 play with each other via Access database projects. Lots of really stupid stuff that works just fine under Access 2000/2003 get broken in really stupid ways under Access 2007. For instance, as a programming practice if I'm going to reuse a SQL statement in different objects (e.g. a form and report) I generally save as a SQL view. If I'm going to use a SQL statement once and only once for a specific object (e.g. a form) I place it directly in that object's record source. The beauty of this is that if I ever need to change a SQL statement for a specific object I know that if it is in the record source it is safe to change as needed, but if it is stored as a view other things might be depending upon it.

The problem is that now under Access 2007 if a subform (a form nested in another form) contains a SQL statement in the record source that references multiple tables and/or views, records in that subform can no longer be edited or added to. To get around this problem I have to save the SQL statement as a view on the SQL server and reference the view in the record source for the subform. The catch is that sorting instructions in a stored view are ignored by Access 2007, as a result the output to the subform is not properly sorted. To get around this issue I now have to take the newly created view and create a select statement from it in the subform's record source that includes the necessary sorting instructions.

So in short:

In Access 200/2003:
Create select statement in subform's data source and everything works just fine. No worries.

In Access 2007:
One must create a view on the MSSQL server using a naming scheme that denotes the view is only to be used for a specific subform. Said view must then be referenced from the subform in the record source. If the data needs to be sorted, one must create a select statement in the subform's record source referencing the new view with the sorting instructions in this new select statement.

FLY IN THE OINTMENT:
Sometimes in Access 2007 creating a select statement with sorting instructions off of a view in a subform's record source will still make it impossible to edit data in that subform. To get around this you have to reference the view directly from the record source sans any select statement and then add sorting instructions to the subform's "order by" field. The catch is that users can override these instructions so to make sure it always works right I must put the sorting instructions into the order by field via a VBA function when the subform is opened.

Oddly enough, Access 2000 & 2003 do not have this subform record source issue when running on a MSSQL 2008 backend and Access 2007 does not have this issue when it is running on a MSSQL 2000 backend this problem only seems to crop up for me when Access 2007 is running on a MSSQL 2008 backend. Tell me that this isn't messed up.

I've been running into all kinds of similar odd behaviors like above with Access 2007, which has required me to spend many hours fixing what shouldn't have broken for one of my clients since they migrated to MSSQL 2008 & Access 2007. Sure it has given me a lot of billable hours, but it still really irks me to have to fix, what was working just fine before. Of all of Microsoft's Office applications, MS-Access has always been my favorite since I started using it with Office 95. In general I really enjoy building Access databases and find Access Database Projects to be a great development platform. Access 2007, however, is that it is an abomination and Microsoft should be ashamed of it. With Access 2007, Microsoft has butchered what was one of the jewels of Microsoft Office.

Boy sometimes it just feels good to vent! ;-)