Getting a null column value in a form recordset that links to a SQL Server not nullable column

Question

I have an application using a SQL Server backend and linked tables using the ODBC Driver 18 for SQL Server. I have a table with a particular field that is defined as not nullable (because it is used in a unique composite index) that I populate with a zero length string when I don't have a specific value.

I have a form bound to a query that uses this table and it is not on the outer side of an outer join and therefore should never be null.

If I query either the table or the query in Access for any records where TABLE_MEMBER_NAME IS NULL no records are returned, whereas if I query for TABLE_MEMBER_NAME = "" I get records (as expected).

In my form, I call a function that I pass the form recordsetClone to. It is in the form recordsetClone (and recordset - I tried both) that the field value is returning NULL and I can't work out why. The form field control (me.form!TABLE_MEMBER_NAME) also returns null, even after I enter a zero length string into the field and save the record.

I running:

Microsoft® Access® for Microsoft 365 MSO (16.0.14326.20900) 64-bit

SQL Server Express 15.0.2080

ODBC Driver 18 for SQL Server, version 2018.180.01.01

**** I also tried it with the SQL Server Native Client version 11.00.7462 - same result.

Answer 1

I see the same thing.

currentdb.Execute "update dbo_myTable set myString = ''", dbFailOnError

?DLookup("myString", "dbo_myTable", "Id=1")

Null

While in SSMS:

select myString, iif(myString='', 1, 0), isnull(myString, 'it is null') from myTable where Id=1;

myString (No column name) (No column name)

        1	

I think the ODBC driver or maybe Access itself is trying to be helpful but isn't.

One more reason to avoid ZLS.

If you want true SQL Server behavior, use a passthrough query.

Answer 2

Hi Tom. Thanks for the response. The intent is to support a range of different backend database via a combination of DAO (linked tables) and ADO, so using passthru's would not be an ideal solution. It seems you have confirmed via your tests that it is indeed a bug (although whether in Access and/or the ODBC drivers is still not clear). Would you be able to log this as a bug with Microsoft (and whether there is a workaround)?

Answer 3

Hi Tom. Did you have a chance to raise this with the Microsoft ?

Answer 4

No I have not. Still letting it marinate a bit longer. Hoping others will chime in as well. The new recordset bug took up all my bandwidth this week. But I don't mind you raising the issue occasionally.

For now I think if I were in your shoes I would want to prepare for a world where this does not get fixed, or even is a "by design" feature.

Avoid ZLS.

Answer 5

Hi Tom

While I can work around this particular scenario in my code, my application is a data reconciliation and quality metric tracking tool - I can't stop a user entering, or a source system containing, a ZLS. i.e. I don't think it is either practical or even possible to avoid ZLS's. I consider this a serious bug that the data in the backend database is not being rendered faithfully in a linked table. I'm hoping it may be as simple as changing a property in the ODBC connection but if not, I think this warrants being fixed.