Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941

    Unanswered: Updateable Recordset

    I had some problems with a DB, and had to import all of the objects into a new DB, and relink all of my SQL Server tables.

    I have a query in the "old" database that is updateable, but when I imported it to the new DB it is not updateable. The individual tables are updateable, and the connection strings for both tables are identical. As far as I can tell, all of the properties on the linked tables are the same. Any ideas on what else to look for?

    Here's the sql, FWIW:
    Code:
    SELECT PRODUCT_LINES.PRODUCT, FACILITY_LINES.MANUF_SITE, PRODUCT_LINES.PROD_LINE, PRODUCT_LINES.PRIORITY 
    FROM FACILITY_LINES INNER JOIN PRODUCT_LINES ON FACILITY_LINES.PROD_LINE=PRODUCT_LINES.PROD_LINE 
    ORDER BY PRODUCT_LINES.PRODUCT, FACILITY_LINES.MANUF_SITE;
    Thanks
    Mark
    Inspiration Through Fermentation

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Are you sure that all the linked tables are updateable? You should try to use the Linked Tables Manager to refresh the links.
    Have a nice day!

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    This probably won't resolve the problem but when I create a new mdb (to import objects from an old mdb), I'll first link in the SQL Server tables and then import all the objects from the old mdb. I noticed that sometimes if the tables are linked in after the objects (ie. queries) are imported, it can affect some of the queries (ie. since the queries don't see the tables when they are imported and MSAccess 'tries' to correct them when importing).
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    That's a good point. I'll keep that in mind next time I have to do that (which may be this afternoon if I can't figure this out).

    If I create a query on each table seperately, they're updateable. As soon as I add the other table, though, I can't add or update.
    Inspiration Through Fermentation

  5. #5
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    RESOLUTION:

    The index on the prod_line field in the facility_lines table must've gotten changed at some point after I had linked to the "old" database. When I linked it to the new database, it picked up that change to the table. I went into SQL Server, changed the index on the field back to UNIQUE, and then updated the table in Access with Linked Table Manager.
    Works perfectly again.

    Thanks
    Inspiration Through Fermentation

Posting Permissions

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