migrated field lookups from Access to SQLServer back end, but when change/edit not affecting ODBC linked copy?
Hi
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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.
You realize that you cannot modify anything in the SQL Server table from the linked table in Access.
ASKER
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.
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.
Regards,
Jim
Until I have worked with a person for a while I have no idea as to their skill level.
Regards,
Jim
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.
ASKER