Ms Access UNION ALL with Attachment data type

[Anthony98364598]

Hi All,



I was just wondering if anyone can help regarding using attachments when utilising the union all fucntion.

This is my SQL code as follows:
SELECT Table1.[Folder #], Table1.[Part #], Table1.[Lot#/ SN], Table1.[Inspection Date], Table1.[Expiry Date], Table1.[SHIP #]
FROM Table1;
UNION ALL
SELECT [GII Register Data2].[Folder #], [GII Register Data2].[Part #], [GII Register Data2].[Lot#/ SN], [GII Register Data2].[Inspection Date], [GII Register Data2].[Expiry Date], [GII Register Data2].[SHIP #]
FROM [GII Register Data2];

(In the above code the attachment column was not included as I would receive an error) This SQL code works great though - without the attachments of course, and want to now add the attachments to my newly merged table.

Now I am wondering how I can use the LEFT JOIN function or anything else to add the attachment column for both Table1 and GII Register2 into the ONE table please.

Any help would be really helpful. Thank you all!

[Micron]

Not exactly sure what you're asking for. You created the union query then created a new query linking the union and the table with the attachments? Then what?
Whether the join needs to be left, right or equal depends on which table is guaranteed to have data. Whether or not you can join at all depends on the attachment table having a related field with the union query. Seasoned developers will rarely put attachments in a table rather than linking to them. Maybe what you really need to do is rethink that approach.

[isladogs]

Multivalued fields (of which attachment fields are an example) CANNOT be used in UNION queries.
As already suggested, you can create and save the union query then use that in turn to link to the two sets of attachment fields.

OR you can treat this as another reason for not using attachment fields (or any MVFs). Instead store the path to the files and store them outside your database

[Anthony98364598]

[QUOTE=isladogs;470228]As already suggested, you can create and save the union query then use that in turn to link to the two sets of attachment fields.

Hi, thanks so much for your help so far. I understand that you can't add attachments due to the "multivalue field" error- but with your comment above, would you be able to elaborate/ show me what you mean? Sorry, I am all new to this sort of work, and I am trying to get my head around what you're proposing I do.

So, if I've created a union query to join two tables together (like I state in the original post), would I then need to create another query solely for the attachments? How would this be done?

Thanks so much for your assistance.

[Anthony98364598]

And @Macron, I am attaching files to "future proof" my dataset containing all my part numbers- as if in the future someone decides to change to location of all the pdf files, at least access will have them saved. I am using the union query to join together linked tables from other access documents as I am close to exceeding the 2gb limit in each of them.
So, at the moment I am only able to join the tables together, but that's without the attachments (as if I include the attachments in the union query- like you said- it wont work). I'm just trying to find any way possible to try and join these tables with the attachments as I spend days attaching over 800+ files to my data.
Thanks again for your response and assistsance.

[Micron]

Attachments will rapidly bloat your file size. You can't protect against stupid or reckless and you shouldn't try - unless you're willing to migrate the be to SQL Server. That will give you a lot more storage space. Anyone who would move/delete such a folder deserves the ultimate punishment. I've said it before and I'll say it again: you hang them by their eyelids and beat them until they blink. The attachments are no more secure in Access than they are in a folder. Such a noob could simply move/delete your db so how effective is your approach at protecting attachments?

[Anthony98364598]

Thanks for the swift reply Micron. You're completely right, it's a damned if you do damned if you don't kind of situation. I will need to rethink how I'm going to go about this.

In saying that though, if you don't mind explaining or showing me an example, what do you mean by "migrate the be to SQL Server"? Anything to give me more storage space please. Thanks so much!

[Micron]

There is SQL Server and MySql. Either has more storage space than Access so attachment storage is not so much of an issue. AFAIK, neither will accept lookup fields in tables or multi value fields (other than attachment fields, which are a mv field type) so do your research before you make the jump. Your question makes me think you didn't pick up on what "be" meant in that context. Back End database file to which you connect your front end. If that is foreign to you, you must research splitting an Access database regardless of whether or not you pursue sql server. It might actually help with your file size if you haven't split your db.

[Anthony98364598]

Okay thanks for that, I have never heard of those before. To be completely honest with you, I only started using access this week!

And yes, I have tried splitting my database- which does improve performance- but my back end database file is still huge and close to exceeding the 2gb limit which is my worry. I have hundreds of pdf files attached in that database - which is why I tried using multiple to link them into one front end file.

Anyway thanks for your help, it's a shame the attachments nor hyperlinks can be merged with 2 tables

[Anthony98364598]

In saying all that though, isladogs' solution sounds promising. I will wait and hope for some good news

[isladogs]

OK bear in mind this is NOT my recommended solution. Its convoluted and doesn't solve the database bloat issue ....
BUT if you want to go ahead anyway:

1. Create your UNION query based on table1 & table2 without the attachment fields - I'll call this qryUnion
2. Now create a new query joining qryUnion to table1 and add the attachment field - qryAtt1
3. Finally create another query joining qryAtt1 to table2 and add the attachment field

I've tested this on a test db with MVFs but not attachments as I don't have any databases that use them
It should work fine. Let me know if not!

NOTE: you may be able to do steps 2 & 3 in one query joining qryUnion to both table1 & table2 but that would result in 2 separate attachment fields

[Micron]

OK bear in mind this is NOT my recommended solution.

I was willing to let you take the credit though. It's remarkable how many times I make a suggestion - even post code, only to have someone come after and say the same thing or post the same code and they get the thanks for it. Oh well, it could just as easily be the blame as credit I suppose.

[Anthony98364598]

Thanks for the reply isladogs. I was able to complete your first step as follows:

SELECT "T1" & ID, [Folder #], [Part #], [Lot#/ SN], [Inspection Date], [Expiry Date], [SHIP #]
FROM Table1
UNION ALL SELECT "GII" & ID, [Folder #], [Part #], [Lot#/ SN], [Inspection Date], [Expiry Date], [SHIP #]
FROM [GII Register Data];

However, I am unsure on how to do steps 2 and 3. Any assistance would be greatly appreciated! As I stated before I've only started using access this week and am pretty clueless as to what I am doing haha.

And Micron I thanked you prior, lets wait and see who replies first to get a thanks in return .

Cheers guys

[Micron]

I meant to say "credit" but I wrote thanks. Oops!
Did you ever divulge the design of the attachment table or did I miss that too? I don't see how anyone can be specific without knowing anything about that. AFAIK, you need to link on common fields between the Union query and the attachment table(s).

[isladogs]

As you only started using Access this week, you may not realise but table and field names should never contain spaces or any special characters such as # or /.
Field Lot#/ SN has all of those ....
Before you go any further, you should change all the names shown in both tables.

Next, why do you have two tables with the same field structure. Again this is a design error. Combine the records into one table - if necessary add an extra field to identify the data source e.g. field Location with some records marked as GII.

If you do that, there is no need for a union query

And whilst doing all that, you should scrap the attachment field and just store the file path as already stated.