Best method to store files

[Leo_Polla_Psemata]

Hi
In our access database that holds and serves several activities in our small size team, we keep as attachment important files, emails, contracts, pictures etc.
Some of these files are quite large, as a matter of fact, the database is more than 100 mb. If i remove the attachments then is less than 10 mb.
Is there any better method that we could follow to keep these files ?

 

[pbaldy]

I just store the path to the file, in a plain text field.

 

[RuralGuy]

I second PBaldy's suggestion!

 

[The_Doc_Man]

I'll agree with Paul and with Allan, but will add this: Make your life easier. Set up a predictable infrastructure where files will likely go. You've got a couple of obvious choices. Either (a) segregate all of the files into different folders by file type, or (b) segregate all of the files into different folders by some variant of the project name. Then if you are not using it already, use the File System Object's file picker dialog box to find the device, path, name, and type of the file you want. Store that in a text string to point to the file.

If the files in question are based on Word, Excel, Power Point, etc., you can even launch an application object if you want to open the files, since you can use automation on App objects to open files read-only (or not RO if you really wanted to modify stuff...)

 

[HiTechCoach]

I agree with all the other that you do not want to store the files within an access database. You will hit the .accdb/.mdb file size limit very quickly.

It is becoming practical to store files within some of the newer versions of SQL Servers and NoSQL/DocumentDB Servers.

I have created an example from some of my Access based applications that have integrated Document Management.

Check out: Document Links 2

This is an update to the basic example of how to store the path to a file and also be able to view the file. You can browse using the standards windows common dialog to select the file. You can view the file using the application defined with the Windows File Associations. It uses the ShelExec API to open the file. It does not use the .Followhyperlink method.

This example is a continuous form that could easily be used as a sub form to attach multiple documents to a record.

***** Because it doesn't use the hyperlink data type, this new version stores the path in a way that allows the folder to be easily moved.

 

[Leo_Polla_Psemata]

Could we link access with specific emails that are stored in an Outlook.PST file ?

 

[isladogs]

I agree with all the other that you do not want to store the files within an access database. You will hit the .accdb/.mdb file size limit very quickly.

It is becoming practical to store files within some of the newer versions of SQL Servers and NoSQL/DocumentDB Servers.

I have created an example from some of my Access based applications that have integrated Document Management.

Check out: Document Links 2

Just out of interest, what advantages does this have in your view over the standard method of external storage with the file path saved in Access?

I realise SQL Server has a 10GB limit for the free version and more for paid versions but I'm not sure what advantages that approach would have

 

[Andy74]

Storing only the path of the file in the database is certainly good to keep the database size small. Anyway if the database has several users and not all users should see all the files then the management of folder permission becomes complicated or impossible. If you use a SQL server backend there is a great feature called filestream which allow you to store the attachments in the SQL database but there in fact linked and stored in a folder so that the database don’t gets bloated. I run a database with about 60 users and the database files is less than 100 Mb while attachments are several Gb’s. Each user may have limited access to only certain records in the table (managed by the front end) and the user can see only files of those records and there is no way he can see other files.



Inviato dal mio iPhone utilizzando Tapatalk

 

[The_Doc_Man]

Could we link access with specific emails that are stored in an Outlook.PST file ?

Not easily, because Outlook (like other members of Office) uses the "collection" method of exposing elements to your usage, and if the number of elements in the list is not constant, there is no guarantee that the index will be constant either (across activations).

Since there is a preferred presentation order in Outlook (i.e. sort by one of the relevant message properties), the ability to add new messages (by receiving them) or remove old messages (by deleting them) means you would have no usable index. I have to think you would do better to extract the message to a file and remember the file's location, name, and type.

What bothers me most about this is that you are beating around the bush to reinvent the wheel. Outlook already stores messages for you and manipulates them. The .PST file that holds postal folders (Inbox, Outbox, Sent, etc.) already tracks your messages and attachments.

If you really have to have this info in two places at once, I would track exported copies of the attachments and perhaps export the message itself. Then your DB files can track extracted things via <device, path, name, type> strings. I would presume this is for a project history. Your greatest efficiency with respect to Access is keeping the file specification, not the whole file. Let Windows and the file system work a little, too.

 

[bastanu]

Actually there is an unique EntryID for every message and you can use that to attach messages to your database entities (using a linking table ProjectID/Message-ID). See https://msdn.microsoft.com/en-us/library/office/aa211865(v=office.11).aspx or
https://social.msdn.microsoft.com/F...ow-to-get-unique-id-of-email?forum=outlookdev. Please note that the EntryID might change if you move the message; so simply link it once it arrives in its final location/folder.

Please feel free to try my free utility (http://forestbyte.com/ms-access-utilities/fba-outlook-companion/) which should allow you to quickly implement this in your db. You can simply import the objects into your db and customize it as needed. I usually add a subform to show all related messages and use a double-click event (of one of the text boxes in the subform, usually the Subject) to open the message in Outlook.

Cheers,
Vlad

 

[Cronk]

I agree with Doc in respect of storing emails in Outlook.

I have a number of organizations using application processing databases where the application data is in an attachment or in the email body. After extracting the data, the email is moved to a process folder which contains year/month sub folders because in all cases there has been a user requirement to keep the source email.

So far, in all cases there is a unique value contained in the Subject line that I also import and store with the email date.

A button on the form displaying the application will either open up the email or the pdf attachment should the user wish to refer to the original application.

 

[The_Doc_Man]

bastanu,

Your suggestion of using EntryID has merit, but the following is a quote from the relevant Microsoft Office description of that property:

Solutions should not depend on the EntryID property to be unique unless items will not be moved

Leo, the question is going to be "how often do these mails potentially move once received & stored?" If the answer is that movement is possible, then I return to my original position that either let Outlook track it OR completely extract it and let Access track it, but doing both is double-entry bookkeeping and prone to error. Not to mention having two places that need to synchronize with each other.

 

[bastanu]

Yes, I agree Doc, I even mentioned that (that the EntryID can change) in my original post.
The way I was doing this was to invoke a pop-up form that was showing all messages from a certain folder (sometimes inbox, but usually another folder where the messages would be moved manually or by an automatic rule). Once you were seeing the messages in Access you could "attach" the message (again, usually to the current record from the main form calling the pop-up). The code was storing the sender, subject and body in a table along with the EntryID. When double-clicking the subject text box the code was opening the original message in Outlook; I found that usually it worked even If I moved the messages around, not sure about it in a corporate Exchange environment. So technically the messages were still "stored" in Outlook but I was giving the user an easy way to see them and open them from Access.

Cheers,
Vlad