migrated field lookups from Access to SQLServer back end, but when change/edit not affecting ODBC linked copy?

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?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LukeBAuthor Commented:
PS: I know that I can set or change the lookup for a field in a form that will be the main user interface in the FE, but I often look and edit the data in the SQL BE in table view or just in a view. So really sort of need the drop downs to appear there and not just in a FE form. Plus is good when moving thru table when developing it to see which fields have lookups and which don't
There is no functionality of this sort in SQL Server tables that I know of and for very good reasons. See my previous post. Why not use pass through select queries to grab your values?


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

LukeBAuthor Commented:
hi Jim,

ok, I read that post and none of them except the last one would affect me; I am pretty careful with lookups, I don't use the Access wizard (my lookups are not set as relationships) and I never use in forms or reports as filters

I don't understand that link's #6 tho; is it saying that I _should_ set up relationships instead of a lookup? Seems to conflict with the #3.

So if lookup fields are 'evil' how to I present a list of choices to users on forms for fields that I must restrict data enter for? I understand, but NOT which is better (so advise please), that I can set the field's row source (what I and MSAcces call a lookup for that field) on a data entry form by way of :
 - a query in Access
 - a SQL statement in Access (essentialy same as above I would think)
  - a pass thru query in Access to the SQLSrvr BE

First time I heard that lookup fields where 'evil' but willing to learn if makes things better. So I need more info/direction if I am going to have to get rid of lookups
Create a table of your restriction values and use a select as the record source of the combo. I prefer to not used linked tables due to network traffic and speed. The paper is referring to the Lookup wiz in a table via the Lookup tab.

You realize that you cannot modify anything in the SQL Server  table from the linked table in Access.
LukeBAuthor Commented:
Thanks Jim, I think that is what I am actually doing, a select query as the record source for the combo.

Not sure on your last remark? I have linked in tables just this morning from SQLServer and can edit them just fine (edit, insert, delete, cascade update, cascade delete). You do need a unique primary key and even better a timestamp field.

I wasn't sure if you understood that doing anything to modify linked tables will have no effect on the backend tables. And when I first read the post I read it as you were trying to build a wiz style lookup column in the SQL Server which won't work at all. Also I misunderstood what you were attempting to do; I thought column lookup wiz.

Until I have worked with a person for a while I have no idea as to their skill level.


I don't know how you upsize Access tables of if someone else does it. If you used Upsizing wiz to migrate the tables you're working with you would have probably been better off using SQL Server Migration Assistant for Access. SSMA would have added a timestamp column  during the migration. It also does a pretty good job in migrating queries, etc.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.