Results 1 to 10 of 10
  1. #1
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    I have the attached code that i use to link to a SQL Server database, however when i try to change anything in the table it wont allow it.
    Why?
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Access requires that there is a unique index on the SQL Server table for the linked table to be updateable.

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    [quote name='patt' post='780593' date='18-Jun-2009 21:30']I have the attached code that i use to link to a SQL Server database, however when i try to change anything in the table it wont allow it.
    Why?[/quote]
    Han's is correct - that is the most common reason why attached SQL Server tables aren't updateable - however there can be several other reasons as well. SQL Server security is one of the other most common issues. Also I'm curious why you are linking the tables in code - about the only time we ever do that is if we are switching users dynamically from a Test version of the database to a live one.
    Wendell

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='HansV' post='780631' date='19-Jun-2009 23:35']Access requires that there is a unique index on the SQL Server table for the linked table to be updateable.[/quote]
    Even if i go into tables and try to change a field there? Access has no such restriction.However i will ensure it has a unique key.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='patt' post='780698' date='19-Jun-2009 23:27']Even if i go into tables and try to change a field there? Access has no such restriction.[/quote]
    Yes, indeed!

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='WendellB' post='780654' date='20-Jun-2009 01:28']Han's is correct - that is the most common reason why attached SQL Server tables aren't updateable - however there can be several other reasons as well. SQL Server security is one of the other most common issues. Also I'm curious why you are linking the tables in code - about the only time we ever do that is if we are switching users dynamically from a Test version of the database to a live one.[/quote]
    That is precisely the reason i am doing it, we are in a heavy mods mode at work and it helps enormusly if i can just relink them in code.

    I had thought of linking all the tables under different names and just renming the linked tables.

    Have you any other ideas on this?

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    [quote name='patt' post='780700' date='19-Jun-2009 15:30']That is precisely the reason i am doing it, we are in a heavy mods mode at work and it helps enormusly if i can just relink them in code. I had thought of linking all the tables under different names and just renming the linked tables. Have you any other ideas on this?[/quote]
    If you are simply changing table designs, and all of your linked tables are in the same SQL Server database, we just use the Access Linked Table Manager. If you have tables in different SQL Server database, or a mixture of SQL, Oracle, Excel, Access, etc. then we have a handy little utility that you are welcome to download. See our Data Source Manager - you will need to register first before you can download it. Some of our most complex applications may have 200 to 300 linked tables, mostly SQL Server tables and views, which is what prompted it's development.
    Wendell

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='WendellB' post='780799' date='21-Jun-2009 03:47']If you are simply changing table designs, and all of your linked tables are in the same SQL Server database, we just use the Access Linked Table Manager. If you have tables in different SQL Server database, or a mixture of SQL, Oracle, Excel, Access, etc. then we have a handy little utility that you are welcome to download. See our Data Source Manager - you will need to register first before you can download it. Some of our most complex applications may have 200 to 300 linked tables, mostly SQL Server tables and views, which is what prompted it's development.[/quote]
    It's not so much for changing tables but for linking to a different set of tables in the samesql server database, depending if it's test or live.

    I make many changes then copy the FE to peoples PCs and so when they sign in it links to the live tables.

    I sometimes need to run the FE against the live system so i need to relink my FE to the live tables as well.

    Then of course i need to relink them back to test mode for more changes i mke to the FE.

    I am loathe to test a FE against live data.

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    [quote name='patt' post='780846' date='21-Jun-2009 00:41']... I am loathe to test a FE against live data.[/quote]
    Absolutely agreed - we take a different approach by using a separate SQL Server test database that mirrors the production database. Then when we deploy to the workstations, we simply use the Linked Table Manager to update the links to the production database. The only thing that gets me in trouble from time to time is to remember that we do have some ADO processing that goes on, and it has Connect Strings in the code that I have to remember to change!
    Wendell

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='HansV' post='780631' date='19-Jun-2009 22:35']Access requires that there is a unique index on the SQL Server table for the linked table to be updateable.[/quote]
    You are absolutely right, i added unique keys on each of these tables and voila it works.

    Thank you Hans.

    Thanks for your input too 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
  •