Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Melbourne, Australia
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ODBC Table and primary key (Access 2000)

    Hi Everyone,

    If you have a ODBC table and it has several fields as the primary key, it is possible to delete anyone of the fields in the primary key and enter another value?

    Thanks Kindly

    Kerrie <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

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

    Re: ODBC Table and primary key (Access 2000)

    What exactly do you mean by an ODBC table? And what do you mean by deleting one ot the fields? Are you talking about clearing the value in that field in a record or about deleting the field from the table?
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Melbourne, Australia
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ODBC Table and primary key (Access 2000)

    Hi Charlotte,

    Sorry I didn't explain it well. What has hapened, someone has created a database and it has a form which is based on a query and the query is getting its data from a linked table(ODBC), (Table:linked ODBC). When the person trys to change the value of one of the fields in this table (via the form)
    it changes but does not save, and an error appears about a dynaset. I have discovered from the linked ODBC table that it has a primary key of 5 fields and that the field she is trying to change data in is one of the primary key fields. I wanted to know whether this is possible or any areas to look at to solve the problem.

    Thanks Kindly,

    Kerrie<img src=/S/smile.gif border=0 alt=smile width=15 height=15>

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

    Re: ODBC Table and primary key (Access 2000)

    Original question still stands--is this an Access table or something else. ODBC in this context simply refers to the drivers used to link external data to Access, but it doesn't tell us what kind of table you're linked to.

    Assuming this is an Access table, why are they trying to change a value in a primary key field in the first place? Primary keys are not intended to be changed, although it is sometimes possible to do so. Without more information about the specific error, though, it's pretty hard to tell what might be causing it.
    Charlotte

  5. #5
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Melbourne, Australia
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ODBC Table and primary key (Access 2000)

    Hi Charlotte,

    I have discovered, the table is a text table located on sql server?? and the field that needs to be changed is not part of the primary key.
    The problem, the field is named error-code, and it is part of a linked table. The field needs to be changed
    to the correct error code. This table has a form, and when you try and change the field via the form on the status bar the error "The recordset is not updateable" appears and does not allow you to edit it.
    However, if you try and edit it via the table, datasheet view,it accepts it.

    Any ideas?

    Thanks

    Kerrie <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

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

    Re: ODBC Table and primary key (Access 2000)

    SQL Server is a database, so I assume you mean the link is to a SQL Server table. If it has a globe icon in the database window in Access, then it's a SQL Server table.

    If you edit the table itself (I'm assuming that's what you mean by datasheet view) and you change the value, what happens to the linked fields in the other table? It sounds to me like you might have a problem unless your SQL Server has triggers to update the other table ... assuming that the links are between SQL Server tables and not between SQL Server and an Access table.

    The form is in Access and the "recordset is not updateable" message means what it says, the recordset behind that form is not updateable from the form. In other words, the form is read-only. Without more information on how the form is built and what you're trying to do with it, I haven't a clue as to why that is true, but it's only marginally related to your question about changing the field unless you *want* to change the field from the form.
    Charlotte

  7. #7
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Melbourne, Australia
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ODBC Table and primary key (Access 2000)

    Hi Charlotte,

    I have checked the form and it allows deletion, additions etc, and the recordset is set to dynaset.
    I did again try and edit the table in datasheet view and then exit the table and it displays "singlerow updatedelete affected more than one row of a linked table. Unique index contains duplicate values " so I had to exit without saving. Previously I edited the field and tabed to the next field in the same row without error - I didn't try to exit or move to a new record.

    I have tried to view the relationships of the tables that exist in the database and out of 30 or so tables it only displays 3, is there a simple way to display the rest.

    So at the moment I can't edit the contents of the field in datasheet or form view.
    I may have a problem with the sql server table.

    Thank you for all your help,

    Kerrie<img src=/S/smile.gif border=0 alt=smile width=15 height=15>

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

    Re: ODBC Table and primary key (Access 2000)

    Hi Kerrie

    We've seen that error in a SQL Server project that we work on. In our case what appeared to be a table was actually a SQL Server view joining three tables. In our case the error only occurred when you tried to update two fields in the view that actually resided in two different tables. If your situation involves a SQL Server view, you may have to resort to linking to the individual SQL Server tables directly and doing the update that way. If you aren't into SQL Server and views, this may not help you much, but perhaps you can chase down the people who set up your SQL Server database and get some assistance from them. If not, I can try to help, but it's not a trivial task to learn SQL Server and it's idiosyncrasies in a problem solving mode. Good luck!
    Wendell

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

    Re: ODBC Table and primary key (Access 2000)

    A form can be set up allow edits, etc., but if its recordset is not updateable, you still can't update records from it. Even if you try to build a dynaset, the structure of the query may make it non-updateable.

    That sounds like a SQL Server error. By definition, a unique index *can't* contain duplicate values, in SQL Server or in Access. This is also why you can't normally edit primary keys. You aren't being allowed to save that edit because it would create a duplicate "unique" index, which is not allowed. Editing a field and tabbing to the next row might not give you an error because you haven't tried to save the record yet.

    You have to understand that you can't do what you're trying to do because it would destroy your data integrity. If you changed the field in the primary key, you would break the connection between that table and any tables linked to it. If you were allowed to change it to a key that already existed, you would no longer be able to tell which records in the linked table belonged with which records in the primary table.

    Are you trying to view the relationships in Access? You probabloy won't see relationships between your SQL Server tables because they usually aren't enforced that way. SQL Server generally uses constraints and triggers to enforce relationships.

    Datasheet *view* of a form is not the same as looking at a table, it's just a different way of looking at the form. If you can't edit it in form view, you can't edit it in datasheet view either. Even though it may look like the table, it isn't the same.
    Charlotte

  10. #10
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Melbourne, Australia
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ODBC Table and primary key (Access 2000)

    Hi Wendy,

    Thank you for your help. Since the last post I have discovered that the sql table is not linked to any other tables, and that if I try and edit the datasheet view, some records give an "..unqiue index error "
    and other records allow you to edit and save them.
    I have discovered that the reocrds that give the error have duplicates, and I have been told that the keys
    are different. Do you know what they mean? Primary key?

    Thanks Kindly,

    Kerrie

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

    Re: ODBC Table and primary key (Access 2000)

    Hmmmm - I still suspect what you think is a table is actually a view in SQL Server. Views look like a table to Access, but are actually two or more SQL Server tables joined together. When you link to a view from Access, it will ask you to specify the primary key. If that doesn't happen, then you are indeed working with table, in which case Access will get the primary key info from SQL Server.

    If indeed you do have duplicate records, then Access is hopelessly lost. For Access (or any ODBC linked table) to be updateable, it must have a unique key, and you must use that to identify the record being updated. I'm a little puzzled as to how you can have duplicate records, and not have a duplicate key for the primary index, as indexes are built using one or more fields to define the index. Primary keys must by definition be unique. I'll follow up a bit with one of my associates who experienced a specific situation that was causing an error message similar to what you are getting and then post a further response. In the meantime, if you find additional information please post it.
    Wendell

Posting Permissions

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