Access Front End Linked To SQL Server Database

Hi,

I created a database in Microsoft Access and then upsized it to SQL Server afterwards.

I now need to add a new column to a table in the database. So I added the new column in SQL Server using ALTER TABLE command. This column is a foreign key which has an associated parent key in another table which contains a description of the column.
e.g.
Main_Table
Case_ID Foreign_Key_Field_ID
1234 78

New_Foreign_Key_Table
Foreign_Key_Field_ID Foreign_Key_Field_Desc
78 Describes the ID 78


I also setup a CONSTRAINTREFERENCE between the fields in these 2 tables.

I then relinked my tables using the Linked Table Manager to pick up the change to the table I altered (Main_Table). I also linked to the new table I created (New_Foreign_Key_Table).

My problem:
In Access if I create a new table I can select the lookup value for a field in the Table Design view Lookup tab. However, I altered my table in SQL Server and then linked to it again to pick up the change. Now when I view the linked table in Access it does not have any lookup value AND it's not possible to change this for a linked table.

Is there some piece of SQL I can use in SQL Server to change the lookup value for the field in my table OR have I approached this the wrong way. Should I have modified the local table in Access and upsized it again?

Otherwise if I select all values from my Main_Table in a form it displays the Foreign_Key_Field_ID instead of the Foreign_Key_Field_Desc because the lookup value has not been set to Foreign_Key_Field_Desc

This posting is a bit long - I hope it makes sense.

Any ideas or suggestions would be greatly appreciated.

Many Thanks