Solved Alternate to Attachments MVF? (2 Viewers)

[nashaz]

Hi all

I have tried to search for a possible solution to this with no luck so far. I need to attach documents to people's records in the database. With a standard MVF, you can use an inner join between two tables to make it work for you but what can we do with attachments? I am having issues when trying to create audit trail (of which I have tried to implement at least 3 different solutions).

Much appreciated.

 

[jdraw]

Review the link by Gregory Long in the Similar Threads at the bottom of the page. It may not be your solution, but may give you some insight.

You could post your material and/or tell us more about your audit trail attempts.

 

[CJ_London]

not a good idea to store documents in a table - they lead to bloat. Better to store them in a folder and store the file name in the table, plus path if that is not easily determined

 

[Pat Hartman]

I don't use Attachments. Except for the cool control, there is no advantage. Also, most of my BE either start out as SQL Server or end up converting and Attachment is not supported so I just don't ever use it.

If you store the images in a separate table as an OLE object, it will work as any other 1-m relationship and you won't have difficulty with your logging. Keep in mind that storing images in Jet/ACE will rapidly bloat the database so, I also am more inclined to store the documents in a folder on the server and just store a link to the document.

PS - I never use MVF for the same reason and others as well.

 

[nashaz]

Thank you for both for your responses. I have gone through the (https://learn.microsoft.com/en-us/o...a-Access-Objects/work-with-attachments-in-dao) and it looks like a good solution. My only question is if it will work for different people on different machines? I'm assuming this method only loads files from the local machine?

 

[nashaz]

As for audit trail attempts, I have tried Scott Gem's method re data macros, and also one advised by Richard Rost which is using before update event to copy data being changed to a separate table using INSERT INTO commands in vba. Neither works for MVF

 

[Gasman]

As for audit trail attempts, I have tried Scott Gem's method re data macros, and also one advised by Richard Rost which is using before update event to copy data being changed to a separate table using INSERT INTO commands in vba. Neither works for MVF

I believe a MVF is merely a disguised 1 to many relationship, so you would need to walk the recordset of the child set and then INSERT the relevant data.

 

[nashaz]

I believe a MVF is merely a disguised 1 to many relationship, so you would need to walk the recordset of the child set and then INSERT the relevant data.

Would you be kind enough to guide me how I can implement that into the following code?

Private Sub Form_BeforeUpdate(Cancel As Integer)
    DoCmd.RunSQL "INSERT INTO ChangeEmployeeT SELECT * FROM EmployeeT " & _
        "WHERE EmployeeID =" & EmployeeID
End Sub

 

[Pat Hartman]

Neither works for MVF

Both data types are "abominations" and will Not upsize to SQL Server or other RDBMS so experts do not use or recommend these data types. There are simple alternatives that work for all RDBMS as I described above. Both of these are 1-m relationships where the many-side tables are hidden from you. They exist, but they are hidden. This forces you to use convoluted SQL and do extra work if you want to do anything except simply use a form to display and add them.

 

[nashaz]

Both data types are "abominations" and will Not upsize to SQL Server or other RDBMS so experts do not use or recommend these data types. There are simple alternatives that work for all RDBMS as I described above. Both of these are 1-m relationships where the many-side tables are hidden from you. They exist, but they are hidden. This forces you to use convoluted SQL and do extra work if you want to do anything except simply use a form to display and add them.

Apologies, I completely missed your comment, Pat. Stupid question; can I store documents as OLE in another table as well, just as images? And would I link each document to an employee by introducing EmployeeT PK into this new table as FK?

 

[CJ_London]

I'm assuming this method only loads files from the local machine?

No - store them on the server. Commonly this would be in a docs folder in the same folder as the back end

 

[nashaz]

No - store them on the server. Commonly this would be in a docs folder in the same folder as the back end

Makes sense. Thank you CJ, this gives me another option to work with. Much appreciated.

 

[Gasman]

Would you be kind enough to guide me how I can implement that into the following code?

Private Sub Form_BeforeUpdate(Cancel As Integer)
    DoCmd.RunSQL "INSERT INTO ChangeEmployeeT SELECT * FROM EmployeeT " & _
        "WHERE EmployeeID =" & EmployeeID
End Sub

Not really as I have never used them.

However a quick Google shows

Work with attachments in DAO

Office VBA reference topic

learn.microsoft.com

process access attachments recordset vba access - Google Search

www.google.com

 

[mike60smart]

Makes sense. Thank you CJ, this gives me another option to work with. Much appreciated.

The attached is an example of how to link to documents

 

[The_Doc_Man]

I'm assuming this method only loads files from the local machine?

Actually, not true. In order for you to have a shared back-end file, every user must have Modify permissions on the back-end file AND on the folder that holds it. Which means you could create a child folder for storage of files on the back-end server and have it inherit the permissions of its parent folder. That means your users would be able to see the folder and drop things in it. There are various ways you can find here to get the path to the back-end folder, which means you could then add "child-folder-name/" to a path string to specify the special folder. Then all you have to do is figure out some scheme to keep the names unique among multiple users.

 

[Pat Hartman]

Apologies, I completely missed your comment, Pat

You missed BOTH parts of my comments. The part about NOT Using the abomination data types for attachments and MVFs AND the part about storing objects in Jet/ACE results in bloat and so the better solution is to store the path and just link to them.

If your BE is SQL Server, it is far better able to store OLE objects but as I said, it does NOT support attachments OR MVFs.

 

[The_Doc_Man]

Just one man's opinion, but unless you are painted into a corner, there is almost (I DID say "almost") no time to store files in an Access database. File specifications (as a string)? YES, emphatically YES. Whole files? No. Access memory bloat will eat your lunch quickly.

 

[nashaz]

You missed BOTH parts of my comments. The part about NOT Using the abomination data types for attachments and MVFs AND the part about storing objects in Jet/ACE results in bloat and so the better solution is to store the path and just link to them.

If your BE is SQL Server, it is far better able to store OLE objects but as I said, it does NOT support attachments OR MVFs.

After reading your comment, I had actually decided to store the link to the server folder. I have removed the "Attachment" field completely from the db. Thanks, as always, for your input Pat

 

[nashaz]

Just one man's opinion, but unless you are painted into a corner, there is almost (I DID say "almost") no time to store files in an Access database. File specifications (as a string)? YES, emphatically YES. Whole files? No. Access memory bloat will eat your lunch quickly.

Yep, I got the message loud and clear about the db bloating thanks to you, and everybody else for your time and input

 

[nashaz]

The attached is an example of how to link to documents

this is very helpful! thanks!