Union (all) doesnt like Multi-Value Field?

[Roon3y]

To keep things simple,

I have got a db and that has 10+ ref tables, and i need to show everything from those tables in one, and these tables includes Attachment field. I have tried union but did not help.

How can i achieve this?

EDIT: i may have posted in wrong section.

 

[Roon3y]

Do you really need them in a single query or are you trying to make a report? To make a report, you can use subreports for each of the reference tables.

So i could use a subreport on a form with some sort of search function?

 

[Solo712]

To keep things simple,

I have got a db and that has 10+ ref tables, and i need to show everything from those tables in one, and these tables includes Attachment field. I have tried union but did not help.

How can i achieve this?

EDIT: i may have posted in wrong section.

Hi Roon3y,
I have run into this issue recently in building a utility that exports a database. Bottom line seems to be that multi-value fields are not referenceable by a regular Select query. The message says that " SELECT * FROM" is not supported if a table has m-v fields. As a workaround I use select list with named fields and leave out the attachment field for tables that have one. Then I transfer the FileData and FileName attachment sub-fields using the technique shown by Andrew Couch in chapter 5 of A2010 VBA Programming. You can download the code from

http://examples.oreilly.com/9780735659872-files/

Good luck and let us know if you find a simpler way.

Best,
Jiri