How to join on memo fields in Microsoft Access

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.

Can't join on memo fields

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:

Graphical display of query

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.

Advertisements

Quickly Link SQL Server Tables in Access

Long gone are the days when I did much application development in MS Access.  However, I still find it a darn good tool for querying, reporting and doing quick edits to data stored in other platforms.

I wrote this code ages ago to allow me to quickly create linked tables in MS Access for SQL Server tables/views. It is much faster than the manual UI approach and doesn’t require setting up a DSN on the local system to make the connection

Usage

Use the immediate window (ctrl-G) to enter these commands:

Link every table in a SQL database(“MYDB”) from SQL Server Instance (“MYSQLSERVER”)
? LinkTable(MYSQLSERVER,MYDB,True)

Link a single table(“MySQLTable”) in a SQL database (“MYSQLDB”)  from SQL Server Instance named “MYSQLSERVER”
? LinkTable(MyTableAliasInAccess, MYSQLSERVER,MYDB,MYSQLTABLE,True)

The Code

Just paste this code into a new module in the Access database and you are ready to go.

Sub LinkAllTables(Server As String, database As String, OverwriteIfExists As Boolean) 'Usage Example (link all tables in database "SQLDB" on SQL Server Instance SQO01, overwriting any existing linked tables. 'linkalltables "SQL01","SQLDB", true 'This will also update the link if the underlying table definition has been modified. Dim rsTableList As New ADODB.Recordset Dim sqlTableList As String sqlTableList = "SELECT [name] as tablename FROM sysObjects WHERE (type = 'U')" rsTableList.Open sqlTableList, BuildSQLConnectionString(Server, database) While Not rsTableList.EOF If LinkTable(rsTableList("tableName"), Server, database, rsTableList("tableName"), OverwriteIfExists) Then Debug.Print "Linked: " & rsTableList("tableName") End If rsTableList.MoveNext Wend rsTableList.Close Debug.Print "Done." End Sub Function LinkTable(LinkedTableAlias As String, Server As String, database As String, SourceTableName As String, OverwriteIfExists As Boolean) 'This method will also update the link if the underlying table definition has been modified. 'The overwrite parameter will cause it to re-map/refresh the link for LinktedTable Alias, but only if it was already a linked table. ' it will not overwrite an existing query or local table with the name specified in LinkedTableAlias. 'Links to a SQL Server table without the need to set up a DSN in the ODBC Console. Dim dbsCurrent As database Dim tdfLinked As TableDef ' Open a database to which a linked table can be appended. Set dbsCurrent = CurrentDb() 'Check for and deal with the scenario ofthe table alias already existing If TableNameInUse(LinkedTableAlias) Then If (Not OverwriteIfExists) Then Debug.Print "Can't use name '" + LinkedTableAlias + "' because it would overwrite existing table." Exit Function End If 'delete existing table, but only if it is a linked table If IsLinkedTable(LinkedTableAlias) Then dbsCurrent.TableDefs.Delete LinkedTableAlias dbsCurrent.TableDefs.Refresh Else Debug.Print "Can't use name '" + LinkedTableAlias + "' because it would overwrite an existing query or local table." Exit Function End If End If 'Create a linked table Set tdfLinked = dbsCurrent.CreateTableDef(LinkedTableAlias) tdfLinked.SourceTableName = SourceTableName tdfLinked.Connect = "ODBC;DRIVER={SQL Server};SERVER=" & Server & ";DATABASE=" & database & ";TRUSTED_CONNECTION=yes;" On Error Resume Next dbsCurrent.TableDefs.Append tdfLinked If (Err.Number = 3626) Then 'too many indexes on source table for Access Err.Clear On Error GoTo 0 If LinkTable(LinkedTableAlias, Server, database, "vw" & SourceTableName, OverwriteIfExists) Then Debug.Print "Can't link directly to table '" + SourceTableName + "' because it contains too many indexes for Access to handle. Linked to view '" & "vw" & SourceTableName & "' instead." LinkTable = True Else Debug.Print "Can't link table '" + SourceTableName + "' because it contains too many indexes for Access to handle. Create a view named '" & "vw" & SourceTableName & "' that selects all rows/columns from '" & SourceTableName & "' and try again to circumvent this." LinkTable = False End If Exit Function End If On Error GoTo 0 tdfLinked.RefreshLink LinkTable = True End Function Function BuildSQLConnectionString(Server As String, DBName As String) As String BuildSQLConnectionString = "Driver={SQL Server};Server=" & Server & ";Database=" & DBName & ";TRUSTED_CONNECTION=yes;" End Function Function TableNameInUse(TableName As String) As Boolean 'check for local tables, linked tables and queries (they all share the same namespace) TableNameInUse = DCount("*", "MSYSObjects", "(Type = 4 or type=1 or type=5) AND [Name]='" & TableName & "'") > 0 End Function Function IsLinkedTable(TableName As String) As Boolean IsLinkedTable = DCount("*", "MSYSObjects", "(Type = 4) AND [Name]='" & TableName & "'") > 0 End Function