Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Oct 2001
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Linked Tables - fields

    Years ago I split a database with the one table on our SQL server. So a 'local' table and the SQL table are listed in the tables in Access 2010. I added a field to the local table expecting that it would appear in the SQL table but it hasn't.

    It has been such a long time since I have worked with this that I don't know how to get that new field in the SQL table. I did re-link the tables.

    Ideas?

    Thanks.

    Jane

  2. #2
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,397 Times in 1,220 Posts
    Hmmm... I am afraid you should have added the new field in the SQL table, not the local table. To solve the situation, here is what I would do:

    1. Add a new field to the SQL table, with a slightly different name and the same time.
    2. Relink the tables.
    2. Write an update query in Access to update the new, slightly different named column with the values from the column in your local table
    3. Confirm that the new column in the SQL table has the values you want
    4. Delete the column in the local table
    5. Relink the tables

    I will admit that I no longer remember where the table relinking steps are needed. I think they are, but there has been some time since I used linked tables in Access.
    Rui
    -------
    R4

  3. #3
    Star Lounger
    Join Date
    Oct 2001
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I tried to add it to the SQL table but within Access. Are you saying I need to go into the SQL manager and do it there?

  4. #4
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,397 Times in 1,220 Posts
    You can't change a linked table from within Access, you need to do it at the source, so yes, you need to do it from whatever app you use to edit the original "SQL table".
    Rui
    -------
    R4

  5. #5
    Star Lounger
    Join Date
    Oct 2001
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts
    thanks. Got it.

    Jane

  6. #6
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,397 Times in 1,220 Posts
    You're welcome.
    Rui
    -------
    R4

  7. #7
    3 Star Lounger HiTechCoach's Avatar
    Join Date
    Sep 2011
    Location
    Oklahoma City, OK
    Posts
    200
    Thanks
    0
    Thanked 31 Times in 30 Posts
    FYI: After making any design change to a table in the SQL Serer you need to refresh the linked table within Access before the changes will be visible when you look at the table. The easiest way yo to this is to use the Link table manager. Select the table then click the OK button.
    Boyd Trimmell aka HiTechCoach (Access Information here)
    Microsoft MVP - Access Expert
    "If technology doesn't work for people, then it doesn't work."

Posting Permissions

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