I'd like to get some opinions about where to best store documents related to records in my Access database.
Here's my current setup. First, I should point out I'm more of a hack than a proper Access/VBA developer. Regardless, I've created a custom mini-CRM application using Access 2010 as the frontend with a SQL Server 2008 backend. The application is deployed using Citrix to a half-dozen users who work in different offices. We also have a few SSRS 2008 reports that run over the same SQL backend. So far so good.
We now have a business requirement to store documents related to records in the database. For instance, users would like to store copies of emails or email attachments along with the record where they record their interaction with an outside contact (ContactActivity table). I need to find a user-friendly way to implement this functionality. It's likely that we'll have similar document management requests for other tables in the database in the future.
Based on my research so far, I see the following options:
- Move the entire ContactActivity table to Sharepoint and store the documents as attachments in Sharepoint (our organization already has Sharepoint implemented). Link to the Sharepoint table in Access and use the Access Attachments functionality as the front-end. This seems like the easiest method. The downside that I see is that my tabular data now lives not just in SQL, but also in Sharepoint. SSRS reports would need to be changed to pull data from both locations. Database backups of SQL and Sharepoint are likely out-of-sync, so restoring them would be difficult.
- Variation of #1 where I keep the ContactActivity table in Access but create a simple list in Sharepoint that only contains the Attachment and the ContactActivityId. Then try to enforce a 1-1 relationship between the SQL table and Sharepoint list. I suspect this could work, but I haven't been able to figure out how to implement it in Access.
- Store the documents alongside the tabular data in SQL Server. Add a varchar(max) field to the ContactActivity table to store the attachment. I believe this is limited to one file per ContactActivity record, so this would not work for our users... they need to be able to store multiple files per record.
- Create a new Documents table in SQL Server. This Documents table would contain all the documents for the database and would be related to the ContactActivity table (and other future tables). I've seen this work well in a web frontend application, but I don't know if it's possible to use Access as a front-end to upload/download documents with this sort of backend. Seems like this would require significant custom VBA code, which isn't my strong suite.
- Store the Documents on a file share and only maintain the links in SQL. This would be easy to implement, but the user experience is poor. Users would have to upload document using Win Explorer, copy the file path, then paste the file path into Access. I'm guessing most of our users wouldn't hassle with it.
Are there better options I haven't thought of? If not, what option is preferable? At this point, I'm leaning toward #2, but I'd like to hear if others have had success with it.
Thanks in advance!
Unless you need the transactional integrity of SQL Server storage, storing documents on a file share and maintaining filename info in a SQL Server Documents table would be my preference. Your users don't have to manage the files. You could implement this in your Access VBA code so everything is done via your user interface.
Since you're using SQL 2008 you could also see if SQL Filestream storage works for you. From BOL: "FILESTREAM integrates the SQL Server Database Engine with an NTFS file system by storing varbinary(max) binary large object (BLOB) data as files on the file system. Transact-SQL statements can insert, update, query, search, and back up FILESTREAM data. Win32 file system interfaces provide streaming access to the data."
Thanks for the prompt response, Paul. I like the idea of storing the documents in a file share and maintaing the path and filename in SQL, but again I'm concerned about the Access frontend since I don't have a ton of VBA experience. Is there some example/sample VBA code you could point me to where this is done? I guess the same applies to storing the files in SQL... I'd have to see an example Access DB or some sample code to understand how to implement the frontend.
I'm also interesting in an explanation of stroing files directly on SQL Server side (if this is possible at all). But without FILESTREAM option, I use SQL Server 2005 with A2003 thus no Attachments, please. :)
storing files in a shared folder is a rather simple task. What exactly do you want to know? For example, to let a user choose a file you can make a button and use the following code:
With Application.FileDialog(msoFileDialogFilePicker) .AllowMultiSelect = False .Title = "Choose a file" .Filters.Clear .Filters.Add "Scan files", "*.tif, *.pdf" If .Show Then Me.txt_path.Value = .SelectedItems(1) End If End With
In this example I allow only one file at a time + a filter for .tif and .pdf files only. The path to the chosen file appears in a textbox (txt_path in my example). You can use your control name to store the path.
All after all, you option number 4 is the best way to go. Just create a separate table for these file where which has ContactActivity's primary key field as its foreign key.
Andrey V Artemyev | Saint-Petersburg, Russia
Russian blog artemyev.biztoolbox.ru
Thanks, Andrew. I was able to make that code work to identify a file and capture it's path. However, I'm realizing that I wasn't totally clear about how I'd like to store the documents. Once the user selects the file, I need my application to copy that file either to a file share dedicated to this application, or push it into a SQL database record. The point is that the original file on the user's local machine shouldn't be referenced by the application, but rather a copy. Do you have any further code that does this file copy? At this time, i'm thinking a SQL table for document storage (rather than a file share) would be preferred because it would be backed up with the rest of the database.
Any other code samples (or links to other posts with more information) would be helpful. Thanks!