Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Error saving changes (Access 2K, Win 2KPro, SQL Server 2K)

    I have a linked table in Access to a SQL Server table that is used to capture data from a webform. I linked the table with the PK identified so that users can edit the data in the table via a form. However, when I attempt to make a change it says that the data cannot be saved since someone else has access to the table. Is there something I can do to prevent this from happening? FYI, the row I will be editing cannot be re-written via the webform as validation prevents the same record from being entered twice. The Access db can and will be opened by more than one person at a time, but the error occurs when I have the db opened on my own. I get the same error on trying to make changes directly on the linked table as well.

    TIA

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error saving changes (Access 2K, Win 2KPro, SQL Server 2K)

    thanks for the reply. you suggestion to put a timestamp on the table does seem to solve the problem, but -- this strikes me as one of those 'secrets of the trade'... on looking up timestamp on BOL, it is used to mark when a row was last modified, as a tool for data recovery. Never heard of it... But, it works! I feel tempted to submit this as a Tip to DevX.com (crediting you and the lounge, of course) but I'm having trouble seeing WHY it works.

    In my naivet

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

    Re: Error saving changes (Access 2K, Win 2KPro, SQL Server 2K)

    I'm not sure I understand you situation entirely, but one possible problem that is common with ODBC linked SQL Server tables is the message that says another user has edited this record and your changes cannnot be saved. One step that often solves these kind of issues is to put a timestamp field in the SQL Server table. Another potential problem area has to do with triggers - does you SQL Server have triggers defined?
    Wendell

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

    Re: Error saving changes (Access 2K, Win 2KPro, SQL Server 2K)

    Not sure I can add a great deal of information about why timestamps solve the issue with ODBC connections - we picked it up from a consulting group in Seattle called J Street Technology a few years ago and found that it solved our issue with ODBC connected tables where a trigger was making changes to record the who and when of record editing.

    You might also want to look at a couple of topics in Books Online that give some hints in terms of the usefulness of timestamps. They are "Logical Database Design" and "Special Data" - hope this sheds a bit more light on the subject.
    Wendell

  5. #5
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error saving changes (Access 2K, Win 2KPro, SQ

    I ran into this problem a couple of years ago

    Quoting Rick Brandt from the following link comp.databases.ms-access thread :

    <hr>First you have to understand what a Timestamp field is in SQL Server. It is not
    the same as a Timestamp in other database engines. It is a binary value that is
    guaranteed to be unique across the entire database and which is updated on every
    edit to the row. This column will always "appear" empty and is not for use by
    any process other than the engine.
    When you commit a change to a SQL Server linked table Access will check to see
    if the record has been altered by other processes since being loaded into the
    edit buffer. This is done by examining the Timestamp column's value (if one is
    present) because a Timestamp is changed every time the record is changed (it's a
    nice shortcut).
    When there is no Timestamp column Access has to compare the value in every field
    to what's in the buffer. If the table includes memo (SQL Server text) or any
    DataType that doesn't translate exactly to an Access/Jet DataType Access can
    come to the conclusion that the record has been altered even when it has not.
    Since the problem depends on DataType differences you don't need one in EVERY
    SQL Server table, only those with certain DataTypes in them. Many developers
    though have gotten into the habit of including one all the time.
    <hr>

    This was found via this search google newsgroup search
    for some added information also see the following search: comp.databases.sqlserver thread

    Hope this helps you understand this "problem" a little better.
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

Posting Permissions

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