Results 1 to 10 of 10

Thread: Using Nulls

  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    Bethel, CT, USA
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using Nulls

    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?

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Columbus, Ohio, USA
    Posts
    286
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Nulls

    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).

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    The Netherlands
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Nulls

    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!

  4. #4
    Star Lounger
    Join Date
    Jan 2001
    Location
    Bethel, CT, USA
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Nulls

    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. [img]/w3timages/icons/baby.gif[/img]

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Using Nulls

    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 [b]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.
    Charlotte

  6. #6
    Star Lounger
    Join Date
    Jan 2001
    Location
    Bethel, CT, USA
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Nulls

    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?

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Using Nulls

    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.
    Charlotte

  8. #8
    Star Lounger
    Join Date
    Jan 2001
    Location
    Bethel, CT, USA
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Nulls

    Excellent, thank you very much.

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    The Netherlands
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Nulls

    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.....

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Using Nulls

    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.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •