Why does Access tell me "blank" text is being regarded as "NULL" by my SQL Server database?

colinasad
colinasad used Ask the Experts™
on
I have recently had to migrate an old Access Project (.ADP) to an Access .ACCDB front-end database, using a DSN file and Linked Tables to connect with a SQL Server database.
This all seems to work OK so far, but when I edit an Access Form text control box that is bound to a field in a SQL Server Table and leave it "blank", Access throws up a run-time error telling me NULLs are not permitted.
This is indeed the case - in my SQL Server Table definitions I have confirmed that NULLs are not allowed and hae a '' default, but I don't understand why an empty field in Access (or even several "space" characters) are being interpretted as NULL.
If I tab through "empty" controls in my Access Form, no problems are reported. It is only if I try to "blank out" a filled text box and leave it empty that the "NULLs" error appears.
I did not have this problem with my old Access .ADP front-end.

Can anyone tell me why Access is interpretting "blanks" as NULLs and being (presumably) rejected by the SQL Server database,
and how to work around it?

Many thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
HainKurtSr. System Analyst

Commented:
in access, remove that constraint somehow...
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
Top Expert 2015

Commented:
if the data is linked and you do not have permission to change the structure/definitions, try this:

on the AfterUpdate event of the control*, see if the 'value' is null (actually Null means 'no value'). If so, then write a value of spaces (or ZLS if allowed)
if isnull(me.controlname) then 
      me.controlname.value = space(#)   'or maybe you can just use ""
end if

Open in new window

WHERE # is the length of the field in the definition
controlname is the Name of the control

*value is done being entered

Author

Commented:
Thanks for the first two prompt responses. My initial thoughts :

1. I want to retain my "no NULLS" restriction on my data, but I don't understand why empty strings are being treated as NULLs.
2. Having to code an "AfterUpdate" sub procedure for every text box would be incredibly time-consuming. I hoped there was a more elegant and simple solution.

Regards.
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
Top Expert 2015

Commented:
Access truncates text when it is changed -- and a value of only spaces becomes nothing.  As long as you leave it alone, all will be ok -- but Access has no way to save a value of only spaces (or zls, zero-length-string) in the user interface; that must be done with code.  You probably want to write a generic function that gets called as this will most likely happen in other cases.

The code could also check the old value and restore that if it was ok.

>> don't understand why empty strings are being treated as NULLs <<
it isn't an empty string once you edit it -- it becomes Null

>> I hoped there was a more elegant and simple solution. <<
well there is ... the form BeforeUpdate could loop through controls that might have this problem and make corrections

... but before you run, you need to walk -- and take the first step. Best to get it working "long-hand" for one control and then generalize it. Is empty string ok? If so, then that could be set for all.
   Ctl.Value = ""

Open in new window

WHERE Ctl is a control object

Author

Commented:
Thanks again for the further comments.
Another option might be to "Allow Nulls" at the SQL Server end of things and just make sure I handle them in other places.
I will also explore Crystal's most recent suggestion.
But it is now late here, so I'll leave that 'till tomorrow.
Many thanks.
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
Top Expert 2015

Commented:
you're welcome

>> Another option might be to "Allow Nulls" at the SQL Server end of things and just make sure I handle them in other places. <<

That might break things in places you don't know about.

... better might be to make sure Null is converted to "" (zls, empty string) in Access -- unless, of course, this is a new database and you are the one defining the rules. FWIW (for what its worth), I usually find it is better to allow all kinds of data, and enforce rules in the user interface -- that way, you can import anything without issues and then create reports to see what is wrong and fix or delete it.

Sometimes the string may need to be a set length (that could also be in the Tag for generalizing) --  -- for instance, if a text field has a set number of characters, it might be so "c" doesn't find "charlie" ... for "c      " is definitely <> "charlie"

I am just guessing because I do not know the rules and structure of your database ... btw, if you want, I can link you to a free tool to analyze it in case you want to know more too ... ask if you want it

The Steps:

  • First, get it working for a control
  • Then, that control, and other controls that need to be checked, could have an indicator in the Tag property
  • Then, the procedure for the specific control can get moved to a standard module and generalized -- send the form object as a parameter, loop through all controls (maybe just in the Detail section) and when it finds the indicator (for instance, ~NoNull~) in the Tag property, change the value to "" if it is Null (0 for number, etc).

This standard procedure can be called from the form BeforeUpdate event -- so for each form, there is only one line of code to include (in addition to filling Tag for each control you want considered (alternately, you could loop through the tabledef and look but that is more complicated code)).

~ sleeping on these thoughts is good  ... as is looking when you first begin your day
Distinguished Expert 2017

Commented:
ZLS apply ONLY to text fields.  As the name implies, this is a STRING and therefore cannot be inserted in a numeric or date field.  I prefer to never allow ZLS in text fields.  That makes my treatment of fields the same regardless of whether they are text or numbers.  They either have a value or they are null.  Some fields are required and sometimes they have defaults but sometimes they don't.  For example, a foreign key to the parent record is required but you would never define a default value.  MaritalStatus might be required but you would probably define some value for unknown so the field is never empty.  Keep in mind that if you don't allow nulls in numeric or date fields, you will always have bogus data.  Zero has a meaning and an amount field should not be 0 unless that is the actual amount.  If a date field is zero, the date is actually 12/30/1899 (Jet/ACE) or 1/1/1900 (SQL Server).  The two databases use different origin dates, hence the difference.

When a form is bound to a Jet/ACE table or query there of, the default values are applied by the Access form as soon as the record is dirtied.  However, when the form is bound to a SQL Server (et al) table or view, the defaults are not populated until the insert is sent to the server.  This can be a little disconcerting and negatively affect the logic behind the form if the app started in one environment and got converted to another.  For this reason, I sometimes define defaults at the form level so I can make the form act the same way regardless of the BE (some of my apps are hot swappable and the BE can be stitched from ACE to SQL Server or back again).

I can't explain exactly what is causing the problem but it has to do with the fact that if you modify a control and  then decide to back out the typing by backspacing or using the space bar, you leave ZLS in the control and Access knows the field is not null.  It seems likely that it is sending something different to the server when it knows the field icontains ZLS rather than being null.  To return a control to its original null state, select the contents and use the delete key.  A similar issue occurs with Excel.  You may have wondered why sometimes when you import worksheets from excel you get "empty" rows or columns.  Well the answer is the same as above.  At some point there was data in the cell and rather than selecting it and using the delete key, the user spaced or backspaced over it.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial