Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2008-10-17
8
Medium Priority
?
429 Views
Last Modified: 2013-11-29
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?
0
Comment
Question by:LukeB
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
8 Comments
 
LVL 1

Author Comment

by:LukeB
ID: 22747113
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
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22747179
0
 
LVL 18

Accepted Solution

by:
jmoss111 earned 2000 total points
ID: 22748271
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?

Jim
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 1

Author Comment

by:LukeB
ID: 22750477
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
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22750561
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.
0
 
LVL 1

Author Comment

by:LukeB
ID: 22750643
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.

0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22750686
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
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22750813
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.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question