Results 1 to 10 of 10
  1. #1
    Join Date
    May 2009
    Posts
    5

    Unanswered: ODBC update fails but the same update succeeds in Access 2003

    Problem summary: I'm attempting to programatically (via ODBC & Perl) update a record in an Access 2003 table. The field I'm attempting to update is defined as not being required, not indexed and with zero length allowed. However, it is set up to perform a lookup to another table if a value is indeed entered. Using MS Access directly, I have no problems going into the table and entering a NULL value for that field. However, whenever I attempt to update the field with a NULL value with the Perl script, it returns an error stating that:

    [Microsoft][ODBC Microsoft Access Driver] You cannot add or change a record because a related record is required in table 'VERIFY_TABLE'. (SQL-23000)

    I SHOULD be able to do the same kind of update programatically as I do from within access, but it seems that ODBC doesn't look past the lookup to see that a zero length field is OK.

    I'm successful at updating non-blank records, this is the only facet of accessing the DB/table that fails to work as advertised.

    Thoughts? Suggestions? Solutions?

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Send it a zero length string instead of Null?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    If Perl is like php, then ST is correct in that it'll be the value that you pass. In php, I had to pass it as "''".

    You can also check in the design of your tables and make sure you're not using any combobox queries in the Lookup tab for any of the linking fields. Setting a combobox/listbox query in the Lookup tab property could cause some problems possibly if you're using that field to update.

    It's most likely the value you're passing though. You may want to google for items like "Perl Null Values" and see what you can find.
    Last edited by pkstormy; 05-04-09 at 23:20.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    May 2009
    Posts
    5
    I probably could have been a little clearer in my original post.

    What I'm doing on the update is actually passing back to the DB the value for that row that I had read in previously.

    Basically what this routine does read in a row from the database and compare it to a line from a file, prompting the user to update any fields that don't match up. If the user doesn't change it, any empty string read in will be written back to that row. This is were the failure occurs (although I suppose it could occur if a user emptied out a string too).

    So actually, what I'm passing back is technically an empty string, not a NULL value. My bad on trying to be too fancy when describing the problem.

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Does the same thing happen if you open the said Access database, open the table and go to the record and replace the string with "" ?

    Perhaps you need a related record for that entry?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    May 2009
    Posts
    5
    Quote Originally Posted by StarTrekker
    Does the same thing happen if you open the said Access database, open the table and go to the record and replace the string with "" ?

    Perhaps you need a related record for that entry?

    No it doesn't. That's the point (and my frustration). If I go in to access and edit the table directly I can enter a blank, empty field. When I updated programatically I can't.

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I didn't say a blank, empty field. I said "" ◄- a zero length string; two double quotes.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  8. #8
    Join Date
    May 2009
    Posts
    5
    I went back and tried entering "" into Access directly and low & behold it "failed" similarly to how it failed programatically. Although this does make things somewhat consistent, I was really hoping for a way to get the programatic approach to work.

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Ok, so you have made good progress! You have identified the problem

    It seems to me that you might just be able easily fix this by entering a record into your VERIFY_TABLE, which has a PKF of a zero length string. Not sure if SQL Server will allow that though ? If not, you could add one specifically for representing "empty" and send the key for it across instead of the zero length string.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  10. #10
    Join Date
    May 2009
    Posts
    5
    I tried your solution (blank field in the verify table) and it seems to have worked. There's part of my head that keeps saying this wasn't the solution I was looking for (although, if I knew what I was looking for I wouldn't have had to ask in the first place!) but I can't argue with the results.

    Thanks everyone for the help.

Posting Permissions

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