Results 1 to 15 of 15
  1. #1
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    version anomalies (Access2000+)

    Has anyone else had problems with things that work fine in Access2000, but don't work (or work differently) in Access2002 or 2003?

    For example. Bound form (table is on SQL Server backend). Control chkSystemfield is bound to field SystemField, which is defined as a bit type, with default value of 0 and Nulls not allowed. In Access2000, when I move to a New Record, chkSystemField is False. In Access2002, the value of chkSystemField is Null!
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  2. #2
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: version anomalies (Access2000+)

    <!post=This anomaly bit me,480,803>This anomaly bit me<!/post>
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

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

    Re: version anomalies (Access2000+)

    Does it change to False when you begin to populate the new record? There were some engine changes between 2000 and 2002, including the query engine.
    Charlotte

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: version anomalies (Access2000+)

    I don't know, I'll have to try to check it out. Since I was immediately using the chkSystemField to set the Locked property for another control, and since it was now Null, I was getting a Type Mismatch error before I could do anything!
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: version anomalies (Access2000+)

    I think that's actually an ODBC driver issue. SQL Server in general does not set the default values until the record is saved. Similar behavior is observed when using autonumbers, which can be a pain if you want to save the new key number to create a related record on the fly.
    Wendell

  6. #6
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: version anomalies (Access2000+)

    I know that the Autonumber doesn't get assigned by SQL until the record is actually being saved (rather than when form is dirty, like Jet). However, Access2000 displays the new record with a False value for this field, while Access2002 displayed it as Null.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: version anomalies (Access2000+)

    That sounds to me like an aberration on the part of Access 2000. A2002 and above reflect what's in the SQL Server table. not whatever default you might have set in the form.
    Charlotte

  8. #8
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: version anomalies (Access2000+)

    Originally the control had no default value. In Access2000, when a new record was started, the default value for that control came from the table. In Access2002, it seems to ignore the default value in the table, and used Null. I then added the default value (FALSE) to the control to get around the problem of that control being Null.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: version anomalies (Access2000+)

    This does seem to be an odd situation. For whatever reason, Access 2002 does not show default values for bit fields in SQL Server even at the table level. I'm checking on Access 2003 at the moment, but I expect it will be the same. And that's why I think this is really an ODBC issue - the information has to be conveyed at the design level. I do know however that it works - we use a number of bit fields in some of our large systems and don't even display them, but SQL Server does set them to the default value when we add a record. (<font color=blue>Update</font color=blue> - Access 2003 works the same way - nothing in the Access design view of the table but it's set in SQL Server.)

    For anyone else reading this thread, default values for SQL Server bit fields used by an Access database is an important issue. If you don't set a default value in SQL Server, you get all sorts of problems with updateability, locking errors when you try to append records and so on. So always set a default value for bit fields of attached SQL Server tables in the SQL Server table design.
    Wendell

  10. #10
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: version anomalies (Access2000+)

    >>So always set a default value for bit fields of attached SQL Server tables in the SQL Server table design.<<

    That's what we had been doing, and also unchecking the "Allow Nulls" box. Works fine with A2000. Very annoying.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  11. #11
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: version anomalies (Access2000+)

    Here is what I'm seeing, which completely blew me away!

    From the Access FE database, I just opened one of the linked tables and went to the New Line record. With both Access2000 and Access2002, the default values specified within the SQL tables were completed ignored by Access! Even after starting to make an entry into a field, the SQL default values never appeared, and the values for all fields remained NULL. The exception was for SQL bit fields. Access treats them as Yes/No fields, which don't allow NULL. In Access2000, these fields appeared as 0 in the New Line record even before any entry made. In Access2002, these fields appeared as Null in the New Line record, but where changed to 0 immediately upon start of data entry in the record. However, it did this even when the default value in the SQL field was 1!

    All this was tried on a single computer. Same SQL table, and same FE database. Just switching between different versions of Access. Even relinking didn't change outcome.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: version anomalies (Access2000+)

    We've learned never to rely on SQL Server default values anyhow, even with dot net. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

  13. #13
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: version anomalies (Access2000+)

    Interestingly enough, it appears that the SQL Server DefaultValues are recognized when you do an Append query from Access. And even just a regular Append query from Access, not even a Pass-through Action query!
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  14. #14
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: version anomalies (Access2000+)

    Can you amplify on this? We use default values for fields in SQL Server, and have never seen them fail - hopefully they aren't failing behind our backs.
    Wendell

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

    Re: version anomalies (Access2000+)

    I just meant that the defaults don't kick in when you might expect them to and we've encountered difficulty with nulls. In our dot net coding, we handle defaults in the data tier before we send the record to the table so the issue doesn't arise.
    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
  •