Having experienced this pain. Let me impart my small view of the world regarding Acces and SharePoint.
First: If you go to your Main View or rather record set view Select / Actions / Open with MS Access, it creates one or more tables within MS Access. One table is the Main Data Table, the other tables maybe linked tables that SharePoint relies upon for example a User's Table. Save the new Database to My Documents or a Share drive somewhere.
Re-Open the database right click and open the main data table in design view. Go to the Look Up Tab at the bottom and you will notice that it is a straight forward look up and the row source are the values taken from the "Combo Box" in SharePoint. Sadly, Access inherits the properties of SharePoint which lends itself to a non normalized database. The table name tends to be an alias that includes spaces in the name, also the fields are also not normalized and have spaces in it for example Last Name, First Name instead of a best practice of "Last_Name".
I thought initially that MS Access was the "dog" wagging the "tail", the tail being SharePoint. But it is exactly opposite.
You can create new views and datasets from Access into SharePoint and then link them to get to a more normalized state. This may in fact reverse the batting order of the "Dog and Tail wagging". But remember, you are going from a desktop application to a server based application.
The bottom line, is there is some inheritance issues from SharePoint to Access that cause grief that eventually make you take the long way around with respect creating VB scripts to automate some functions in Access.
Re-reading your post, the answer to your question is that SharePoint as an Enterprise Application should be your original source of record. You create the "lookups" in Settings / List Settings / Column. Define the new column (***caution****) you have the ability to select multi-value columns. NOT a best practice. Single value select only is the best option. See above inheritance issues. Also in SharePoint, unless you do some fancy wrangling, the Text Fields have a bad quirk in that you can not search or sort on Multi-line text. So unless your end user is writing a novel, keep it to single line text and you can get the customized filter for that field when you are looking for a value in a text field in a record such as = Contains "Texas".
Last edited by jstpierre; 03-01-12 at 00:21.
Fighting Terror One Query @ a Time