migrated field lookups from Access to SQLServer back end, but when change/edit not affecting ODBC linked copy?
Posted on 2008-10-17
I migrated one of my main master data tables and its 4 lookup tables from a BE Access MDB to a new SQLServer BE. Everything migrated ok.
Q1 : In the SQLServer BE when I open the main data table and go over to a field that has a lookup, there is not a drop down box for choosing data listed in the lookup? I checked the Column Properties and the MS_RowSource is there and filled in correctly with the SQL SELECT statement.
Q2 : In the SQLServer BE when I change the MS_RowSource to nothing and save the Column Properties and then go back to my Access FE the lookup for that primary field has NOT changed? It still has the old lookup definition? I did re-link the SQL table again to make sure.
Q3 : If I do oppostie to above, change the lookup for a field in the primary data table from within the Access FE and close it, it gets saved, fine. But when I go to SQL Mgnmt Studio the change is not showing? Going back to Access FE and checking the lookup set there is still what I changed it to.
Q4 : give above, and wating answers, what is best way to handle lookups for tables in my FE? Set the lookups in the FE and delete out the the SQL BE?