Varbinary(MAX), BLOBs and linked MSSQL tables in Microsoft Access

It is time to catalogue more of the crimes against humanity that is the evolution of Microsoft Access.
I've had document storage for OLE object fields in MS Access linked tables working fine for a while now, so swapping to SQL Server linked tables shouldn't be a problem, right ?
Wrong.
I started off trying to solve a seemingly innocent error message that occurred only when storing a document over a certain size, about half a megabyte. This snowballed into most of a weekend taking a tour through the entire history and inner workings of linked SQL tables.
I present the results here in the hope that it may save others.

(1) Overview of the MAX types

In MSSQL2005, Microsoft introduced the varchar(MAX), nvarchar(MAX) and varbinary(MAX) types. These replaced text, ntext and image. The advantages were that the new text types could be operated on with LIKE, LEFT and other text functions, and could be used in sorting, and that all the new types were stored in the database more efficiently.
The old text, ntext and image were listed as deprecated, and still have warnings that they will be removed completely in a future version of MS SQL Server, although they are still present in MSSQL2014.

MSSQL2000 did not have these MAX types, and was the last version in which text, ntext and image were not deprecated.

(2) Overview of MS Access Linked SQL Server Tables

Linked tables with an MSSQL back end in MS Access always use the ODBC driver, and always use DAO, which is the Access native data API. The DAO tabledef object stores the ODBC connection string in its .connect property.
There is the option to use an ODBC DSN to make the connection, in which case the connection string holds the name of the DSN, but not the server, database name or authentication details, since they are stored in the DSN. The table linker wizard always uses DSN based connections.
The connection string looks something like this:

ODBC;DSN=MyDSN;DATABASE=MyDB;Trusted_Connection=Yes

Another option is the DSNless connection, which specifies the server and database directly. The table's connection strings must be set using code, something like:

ODBC;DRIVER=SQL Server;SERVER=TESTSERVER\SQL2008EXPRESS;Trusted_Connection=Yes;DATABASE=MyDb;QuotedId=no;

(3) ODBC Drivers

There are essentially three ODBC drivers, which must be either chosen when creating a DSN, or specified as the DRIVER parameter in a DSNless connection:
a. SQL Server: the historical driver, released prior to 2005
b. SQL Native Client: the driver released with SQL2005
c. SQL Server Native Client 10.0: the version-specific driver for SQL Server. Other versions include 11.0, 12.0, etc.

You might expect because that the 'SQL Server' ODBC driver came out before MSSQL2005 existed, it might not handle the MAX datatypes gracefully, and you'd be correct.
What was surprising was that none of the other drivers handle it gracefully either.
Also, because MS Access 2003 came out before MSSQL2005 existed, we might also expect it not to work even with the later drivers (although Access 2003 SP3 added a lot of back-patched for things like this and DID come out after 2005).

However, I can confirm that MS Access 2007 and 2003, none of these drivers handle the MAX types correctly.

I suspect that this may be an Access version issue, or OS version issue, or driver version issue, but I don't have several days to go down that particular rabbit hole.

(4) Editing large data in MS Access

There are is a well documented way to write binary data to a back end table in MS Access. Essentially, a DAO or ADO recordset is opened and the .AppendChunk method is used to append byte arrays of data until the whole binary image is written.
Generally a 'chunk size' is specified and the image is broken up and written in chunks of that size, with a final leftover smaller chunk at the end or start.

However editing varchar(MAX) columns will almost always be through a table bound to a form.

As mentioned, writing to linked tables, whether an MSSQL back end or MS Access back end, and whether using DAO or ADO, ultimately uses DAO and the ODBC drivers because that's the base mechanism for linked tables.
This applies to all linked tables bound to forms.

The only way around the ODBC drivers is to open an ADO connection using OLEDB direct to the back end MSSQL database. This is by necessity a code-based activity. It cannot be used for bound data.

(5) Putting it all together

Now that we have had the overview discussion, we can proceed with the actual problem

Point 1: The Old DataTypes work Fine

Sticking with text, ntext and image appears to be the best option.
While this solution is flagged as NOT RECOMMENDED anywhere you google it, for reasons discussed earlier, it actually appears to be the only fully supported and tacitly recommended solution.
For example, the upsizing wizard has an option for this, and it's recommended by Microsoft to choose it.

So simply, if you want large data types in SQL Server to work across all scenarios for linked tables, use the old data types.

Point 2: The ODBC Drivers for the new MAX datatypes don't work at all

Using the 'SQL Server' ODBC driver, the varbinary(MAX) column linked as an OLE field in MS Access 2003 and 2007, but trying to insert a BLOB of more than about half a megabyte gave me the following:

Error: 7125 - ODBC Error:[Microsoft][ODBC SQL Server Driver][SQL Server]The text, ntext, or image pointer value conflicts with the column name specified. (in xf_Test_RWBlob:TestBlobRW)
Error: 0 - ODBC Error:[Microsoft][ODBC SQL Server Driver]Warning: Partial insert/update. The insert/update of a text or image column(s) did not succeed. (in xf_Test_RWBlob:TestBlobRW)

Using either of the 'Native' ODBC drivers, the varbinary(MAX) column linked as a fixed binary(255) field in MS Access 2003, and as an OLE object in MS Access 2007, but either Access version still gave me:

Error: 0 - ODBC Error:[Microsoft][SQL Native Client]String data, right truncation (in xf_Test_RWBlob:TestBlobRW_DAO)

As I said earlier, this may be an Access version issue, or OS version issue, or driver version issue. But long story short, I couldn't get it to work reliably, or, in fact, at all.

Point 3: The Workarounds are Partial

For just the case of the BLOB, it is possible to open an ADO connection in code using OLEDB direct to the back end MSSQL database, using a connection like:

Provider=SQLOLEDB;Data Source=TESTSERVER\SQL2008EXPRESS;Initial Catalog=MyDB;Integrated Security=SSPI;

This works. 

However this doesn't help us with bound linked tables containing nvarchar(MAX) and varchar(MAX) columns. They'll still throw errors if we add too much text to the control.

This would be workable if editing was done by unbound controls and data was saved with an ADO connection.

Summary

We are far better off sticking with the old, deprecated types. The alternatives are at best a messy, and at worst a massive, workaround.

Thanks, Microsoft, for once again breaking everything and leaving us to pick up the pieces.

I'd love it if someone proves me wrong here, and tells me how to do it properly. But I don't think that's going to happen.

Final Note

After writing this, I kept getting errors in MS Access 2003 on a table with an nText and an Image column using the 'SQL Server' driver. Eventually I discovered that if I moved the nText column to occur after the Image column in the table, the error disappeared. Then I tried using the later drivers. This also fixed the problem.
I have a motive for going with the old driver: many of my clients don't have the newer one installed, whereas the old one is always there. If I can make it work, it's less headaches for everyone.
Also, you'll probably laugh, but I still use Access 2003 for all my development. It's the last useable version, IMO. The newer version double the amount of clicks necessary to do anything. I can't stand them. I suppose one day I'll have to update. It's the rare issues like this that will probably force my hand.