Results 1 to 13 of 13
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    This recordset is not updateable? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    I made the following query with Find unmatched query wizard:

    It displays 63 records, howevere it won

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: This recordset is not updateable? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Does it become updateable if you change SELECT to SELECT DISTINCTROW?

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: This recordset is not updateable? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    That worked fine, however this is part of another problem I

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: This recordset is not updateable? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Queries joining two or more tables are not updateable if there is no unique key (index) on the join field on the "one" side of the join. Have you removed an index from one of the tables? If not, try a compact and repair on each of the databases involved, this recreates all indexes.

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: This recordset is not updateable? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Thanks for finding the needle in my haystack

    Come to find out earlier in the week BtfTables.mdb (supported by another developer) had a corrupt record and he did a compact and repair whiched fixed the bad record however removed the primary key from tblPersonal

    Dose this make sense?

    John

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: This recordset is not updateable? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    This can happen, yes. An index can become corrupt too, and Compact/Repair will then (try to) remove it.

    After recreating the primary key, the query should be updateable again.

  7. #7
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: This recordset is not updateable? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Can compact/repair also remove relationships?

    What do other developers do to document indexes, primary keys, relationships, etc so when corruption happens down the road the correct indexes, primary keys, relationships, etc can be restored without rethinking the entire application?

    Thanks, John

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: This recordset is not updateable? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Yes, indexes and relationships are stored in the database in the form of invisible tables. Just like ordinary tables, they can become corrupt.

    Access has a built-in documentation tool (Tools | Analyze | Documenter), and there are third-party tools. <!profile=MarkLiquorman>MarkLiquorman<!/profile> has a free utility, you can download it from DataMagic: Access Tips, Links, & Downloads.

  9. #9
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: This recordset is not updateable? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Thanks Hans

    Nice job Mark

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: This recordset is not updateable? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Another way is to keep a copy of the backend database, one that is used purely for this reason.

  11. #11
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: This recordset is not updateable? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Hi Pat

    Just think, you

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

    Re: This recordset is not updateable? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Yes - a nice thought - but how do you propose to implement it? You could put all of your indexes, keys and relationships into a VBA module - but think of the pain of trying to maintain that code each time you make a design change! As an alternative, you could store that in a separate database - but suppose it goes corrupt, and then your code mangles your live database.

    My <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15> - if your database and the data it contains is critical enough that you can't restore from a recent backup when corruption occurs, then you need to look at a different back-end - the MSDE or SQL Server comes to mind. We work with remote databases for years at a time with no corruption problems when the back-end is SQL Server based.
    Wendell

  13. #13
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: This recordset is not updateable? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Hi Wendell

    Thanks for your imput, I didn

Posting Permissions

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