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
415 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
  • 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 500 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
 
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

757 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now