Geeks With Blogs


View Anthony Trudeau's profile on LinkedIn

Add to Technorati Favorites

Anthony Trudeau

Today has been one of those days to hate Microsoft Access.  My company has dozens of Microsoft Access applications in the enterprise that do everything under the sun.  Eventually those will be replaced by me, but in the meantime I have to deal with them.  Today was one of those days.  In fact today I had two circumstances to reinforce those feelings.

The first came when I had to correct an ordering of results on a form that wasn't matching a report.  The report was deemed correct, so I needed to view the design of its query to see how it was different.  Immediately, I'm greeted by the warning that the designer cannot represent the join.  (Not unexpected since the query is complex.)  I click okay, get the information I want, close the query, fix the form ordering, and go on my merry way.  That is until the email later.

The email with the attached report showing 18 pages for what should be two was the problem.  What happened?  Microsoft Access decided to fix the problem of its own volition.  And not only that.  Microsoft Access decided to save the query definition without a peep, prompt, or otherwise.  And to make things worse, it did the same thing when I imported the query from a shadow copy and every other method I could think of.  Eventually I had to manually change it.  (I promptly made a backup that I can plop in place if needed; hopefully, Microsoft Access didn't silently fix my backup.)

The second was in relation to an Access project that is being put in place as I push the current data from Microsoft Access into Microsoft SQL Server.  One of the constructs is a report with a simple subreport.  The subreport represents a one-to-many relationship with a simple list of items corresponding to the main report.  This shoud have been easy.  My queries were tested in Management Studio and they run individually in the reports.  However, when the subreport was added to the main report and run I got the error "Syntax error or access denied".  Thanks for the information Microsoft.  Obviously, the problem is in the subreport, because the main report shows.

Well, I start simplifying the reports and eventually get down to one with no joins in each.  I still got the error.  The problem?  The SQL statement on my subreport is terminated with a semi-colon (by ANSI standards interestingly).  I guess that's a syntax error, but in of itself it's not -- just when combined with another SQL statement through the main report.

So at the end of the day, Microsoft decided to change and save a query I didn't change myself, and inconsistently decided not to change the SQL statement that it could have easily changed on the subreport.  My choice is for it not to change anything without me explicitly telling it, but if anything I would like some consistency.

Posted on Tuesday, July 15, 2008 7:56 AM bugs | Back to top

Comments on this post: Why I hate Microsoft Access...

No comments posted yet.
Your comment:
 (will show your gravatar)

Copyright © Anthony Trudeau | Powered by: