Rambling Intro, Nostalgia, and Crankiness
This week I got a request troubleshoot a legacy Microsoft Access application that has been floating around our company for ages, but still gets used daily because dang it, it does the job and always has. Seems like most companies that are standardized on MS Office have a few of these lurking out on the network.
Earlier in my career I did a ton of work in MS Access and have garnered a reputation within my company for being an expert in this oft maligned platform so I got the call to look into the problem. It had been quite a while since I’d done any real work on MS Access and I’d forgotten about how quirky it could be. Also, I am more than a little disappointed at how Microsoft has mangled the UI of my old friend Access in the 2007 version. It is almost painful to work with it as a power-user in the current incarnation.
The Problem
So anyway, the issue turned out to be that someone increased the length of a field in the underlying SQL Server table linked into the Access application. They increased it past the magical border (255 characters) between what Access considers a text and a memo field, which imposed new limits on how it could be used. In particular, Access doesn’t allow either end of a join in a query to be a memo field.
This won't fly, McFly
The Solution
The solution is painfully simple. So much so that I have to wonder why Access doesn’t just do it behind the scenes. Perhaps it is just trying to discourage you from building databases that link on big text fields for your own good (see “The Caveat” below).
The trick is to move the join into the WHERE clause of the query like so:
SELECT Table1.*, Table2.* FROM Table1, Table2 WHERE (Table1.MemoField=table2.MemoField);
Here is the same query in the query builder for those of you who prefer it to the SQL view:
Remove the join between the tables and add a criterion
Access will raise nary a complaint if you run this query which is logically equivalent to the one it abhorred. That’s all there is to it.
The Caveat
A final note. It is a definite database smell for an application to be joining tables on long text fields and will likely be the source of some performance issues in a database of non-trivial size. However, as was the case for the application I was tweaking, joining on long text fields is sometimes necessary in queries used for data clean-up, validation, or replication. Still, use this type of join with caution avoiding it whenever possible.
Filed under: How To..., Technology Tips | Tagged: MSAccess, Queries, tips |
Improving Software 
Not a fan of the Ribbon UI, huh? Definite a love/hate with that.
Not a fan of the ribbon, but that isn’t my main beef. The object navigation window is a disaster
Overall, it just seems like the UI has taken a major shift towards novice users at the expense of power users.
no, sry. there’s no “love”.
the only ppl who could “love” the (built-in MS) ribbon(s) are utter novices and others for whom the actual “getting work done” is a secondary function to the “ooh shiny!” factor.
I won’t go into detail here. there’s not room and it’s not even an appropriate place. but their ribbons are SO bad. 😦
…. OK ….. I’ll give you just one peeve to mull over:
Not only is there a toolbar button that mimics the function of the delete button on the keyboard – i.e. delete character(s) in a text field (O_o) – but it *hides* the real “delete” button (i.e. “Delete Record”) with a stupid drop-down. (And to add to the confusion, it also mimics the “Delete Record” icon from previous access versions.)
WHY? WHY?! 1,000,000x WHY?!?!
and yeah, I’m here for the real topic which is “why can’t I join on a memo field?” answer: no good reason. alternate answer: cuz MS a) is lazy b) is incompetent c) just doesn’t like you d) all of the above
thanks for the workaround though!
…now back to my regularly scheduled trying-to-get-some-work-done-today-and-MS-software-fighting-me-every-step-of-the-way.
PS solidarity w/ johnfx. I hear you about the object navigation pane. the “nav pain” as I call it! 😉
“…the UI has taken a major shift…” extra “f” in there. ;-P
quick follow-up in the interest of returning to practicality (feel free to merge.)
if the UI is supposed to be so novice-friendly, why are my users constantly asking me how to perform such a *fundamental* operation as “delete record”???
that’s a rhetorical question. unless Ballmer himself wants to step up and answer.
I got a memo field problem that seems not to be seen around yet. I explain: Within a memo field (lots of text) I need to put 2 or 3 placeholders e.g. [p1]… Now, i need to be able to choose from a drop down menue, select specific records that will fill the space in the memo field. Just like you can do in Word. Is it possible?
Suggest you post that question over at StackOverflow.com with more details. You should get help lickety split there.
Thanks for this Johnfx,
I’m still having trouble though – When I attempt this, I get a “Enter Parameter Value” pop-up after clicking “run”. Any idea what I should enter?
The most common cause of this is a misspelled a field or table name in your query (or a reference to one that doesn’t exist.
THANK YOU! That solved my problem!
Very easy to understand and replicate on my side.
Thanks again!
Thanks for the solution. This helped. However I have another problem also with the memo fields. I need to perform a left join. Is there any workaround for that situation
I guess this solution would work only for equi-join. What if we have a situation where we have to do a left join?
You can also use CAST to change the field length in a SQL view then link to that view in Access. I did this in the Project Server reporting database on an Enterprise Custom Field that I needed to use to link to some Access goo.
Yes, you wrote that a long time ago. It just proved very helpful to me. Thanks!
Thank you … this headed me down the right path. I ended up being able to change both my fields to Short Text and then link them.
Not working for me. Both fields are Long text and I still get the error message. I was using the exact format of the screenshot.
This answer is not exactly correct. Because join have three types. Left /right Join, Inner Join (Equal join) and full outer join. This solution is simply for inner join. For other joins, you will have to append the missing population using union statement.