Using Nulls

Topic

New Reply

A friend of mine who programs in SQL Server and C++ told me that I should not use Nulls in my Access database and related code. He says that if I link to other Dbs that I may run into problems. I can understand that there may be problems if I set a value to Null. But if I test for Null (IsNull), will that cause a problem? Also, when would IsEmpty be used? Any suggestions?

Reply | Quote

Replies

Checking for nulls is not bad. As a matter of fact, it is recommended. IsEmpty is used to check to see if a variable has been initialized.(see IsEmpty Function in Access Help).

Reply | Quote

If you stick to Access, working with Null values is no problem. If you want to migrate to other DBMS-es, you may bump into a DBA who is only implementing fields that do not allow NULLS.
Also there are (not much) some database systems that won’t allow NULL values into their fields.

Then you will have problem….

But: hey, what’s wrong with Access, works fine for me and my clients!

Reply | Quote

Hi Bart,
I’ve made an Access database for product testing results. My boss would like me to find a way to link to the SQL server inventory maintenance database to read and write material usage allocations. I’m just trying to find out how I might avoid problems before they occur.
I like to investigate all the angles before I tackle a new problem. But I’m still new to all this.

Reply | Quote

SQL Server doesn’t have anything against nulls per se. However, you can’t include null values in indexes, so you aren’t allowed to create a primary key with a field that might be null. If you create a unique key in Access with a field that can be null, you’ll discover that any record that contains a null in one of the fields in that key can be entered over and over again.

From that perspective alone, it isn’t a good idea to allow null values in an indexed field or a field that’s part of an index. It’s a better idea to use a default value that means[/] no value has been assigned, rather than allowing a null. For example, I usually append a record that contains a zero for the autonumber field and things like “(Unknown)” for text fields to tables with autonumer keys . Then you can still enforce referential integrity if you make the default for the connecting field in another table insert a zero for that field until the user actually enters a value.

Reply | Quote

Thanks Charlotte,
That is the kind of info I need. So I would imagine that to be on the safe side, I need to be sure that any blank field I send back to the SQL Server Db would need to contain an empty field marker of some sort. Will the SQL Server automatically do this or will I need to know the specific way it was done for that particular Db?

Reply | Quote

When you send a Null field back, you are sending an “empty field” marker. There is no such things as an empty field, really. There are populated fields and unpopulated fields, and the unpopulated fields are Null by definition. SQL Server translates them that way. If you want to be doubly sure, set the default property for all fields to Null for text and date fields and 0 for numeric fields.

Reply | Quote

Excellent, thank you very much.

Reply | Quote

You can have SQL server do this for you. You can put a trigger on the table and have a stored procedure checking whether a particular field is empty or not. If empty then the stored procedure can set the empty field marker.
This is very save way of working with SQL server because it also works if data is changed by another application then you Access application.

Oh boy, triggers, stored procedures, I realy hope they will come available in the next version of MS Access…..

Reply | Quote

You can create stored procedures in Access 2000 ADPs because they have a SQL Server back end. Of course, you have to write T-SQL to do it, but that goes with the territory. I’d say the closest equivalent to a stored procedure in Access itself would be a user-defined class.

Reply | Quote