UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Cannot Reference A Table With A Multi-valued Field Using A From Clause That Refers To Another Database, Office 2010    
 
   
larai
post Nov 29 2017, 08:47 PM
Post#1



Posts: 1,064
Joined: 8-February 02
From: California, USA


Hello,

I've been searching for a workaround or fix for this, but having no success. I'm trying to set a connection to a table that has a multi-value field. I've even tried NOT bringing in the column with the multi-value field with no success. Does anyone have any suggestions?


Thanks!
Go to the top of the page
 
DanielPineault
post Nov 29 2017, 08:57 PM
Post#2


UtterAccess VIP
Posts: 5,450
Joined: 30-June 11



I could be wrong, so hopefully someone will correct me, but I believe this is one of the numerous limitations/issues with MVF. The work around is to properly normalize your design by create your own 1-M tables ... then you'd be able to reference the table(s) in your queries.

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...
Go to the top of the page
 
jleach
post Nov 30 2017, 05:50 AM
Post#3


UtterAccess Editor
Posts: 9,801
Joined: 7-December 09
From: Staten Island, NY, USA


I expect Daniel is correct. The MVF violates sound database principles that have been in place for 47 years and should not be expected to work except within the isolated area for which they were created (e.g., Access).

Even though you don't pull that column through in your SELECT, the engine still needs to recognize the structure of the database, and MVFs breaks that.

(this is possibly one of the worst-thought features that Access has ever received...)

--------------------
Go to the top of the page
 
jleach
post Nov 30 2017, 05:53 AM
Post#4


UtterAccess Editor
Posts: 9,801
Joined: 7-December 09
From: Staten Island, NY, USA


You can attempt to create a query within Access that reads all fields except the MVF, then have your external connection read from that query. That may be enough to hide the wound... the external connection may treat the abstraction query as a black box, thus inferring the required schema based on that instead of the table (this would work for SQL Server Views, but not sure it's enough in this case).

--------------------
Go to the top of the page
 
GroverParkGeorge
post Nov 30 2017, 06:52 AM
Post#5


UA Admin
Posts: 31,195
Joined: 20-June 02
From: Newcastle, WA


The irony of the MVF (along with Lookup Fields in tables) is that experienced, knowledgeable developers who might be able to work around many of the limitations they impose almost always avoid them entirely, while inexperienced newer developers are more far more likely to actually try to use them.

--------------------
Go to the top of the page
 
GroverParkGeorge
post Nov 30 2017, 07:06 AM
Post#6


UA Admin
Posts: 31,195
Joined: 20-June 02
From: Newcastle, WA


Also we should note that you are trying to establish this connection from Excel, which has no way that I know of to interpret an MVF in an Access table.

That means, as I see it, you have two choices. Restore sanity by converting the MVF to a properly normalized table design. Create a query -- perhaps with a VBA function that does the conversion of the MVF fields into fields in the query -- which allows you to return all of the buried MVF fields, and then link to that query. I think the latter can be done, although it's not something I have ever tried.

--------------------
Go to the top of the page
 
jleach
post Nov 30 2017, 07:37 AM
Post#7


UtterAccess Editor
Posts: 9,801
Joined: 7-December 09
From: Staten Island, NY, USA


(OT)
>> experienced, knowledgeable developers who might be able to work around many of the limitations they impose almost always avoid them entirely <<

In aviation we have a saying: A superior pilot uses his (or her) superior skills to avoid situations in which (s)he must use them.

Another: You start with an empty bag of experience and a full bag of luck... the trick is to move everything from one to the other before you get yourself killed.

--------------------
Go to the top of the page
 
DanielPineault
post Nov 30 2017, 08:23 AM
Post#8


UtterAccess VIP
Posts: 5,450
Joined: 30-June 11



QUOTE
The irony of the MVF (along with Lookup Fields in tables) is that experienced, knowledgeable developers who might be able to work around many of the limitations they impose almost always avoid them entirely, while inexperienced newer developers are more far more likely to actually try to use them.

Very true George!

Many of these new "features" meant to simplify things for novice users often lead to headaches and are best to be avoided. The old tried and tested approaches remain the way to handle most things.

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...
Go to the top of the page
 
River59
post Nov 30 2017, 08:33 AM
Post#9



Posts: 1,344
Joined: 7-April 10
From: Detroit, MI


Where is the data for the MVF coming from? Is the field a hyperlink field? There is a way for Excel to strip out the hyperlink address if that is what you need.

--------------------
Remember ... Armstrong, Aldrin and Collins flew to the moon and back with a computer system less complex than a modern, programmable toaster ...
Go to the top of the page
 
larai
post Nov 30 2017, 12:39 PM
Post#10



Posts: 1,064
Joined: 8-February 02
From: California, USA


Interestingly enough, I'm not even trying to grab the MVF. I have a query with only 3 fields, none being the MVF but it won't allow any connection that queries a table with an MVF. I'll change it to it's own table. And no, it's not a hyperlink field. MS Query just doesn't allow it.

Thanks for the feedback.
Go to the top of the page
 
GroverParkGeorge
post Nov 30 2017, 12:54 PM
Post#11


UA Admin
Posts: 31,195
Joined: 20-June 02
From: Newcastle, WA


And that adds one more argument against ever sullying a table with an MVF....

--------------------
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    12th December 2017 - 01:40 AM