You are here:

Using MS Access/Saving attachments to external drive from Form

Advertisement


Question
QUESTION: In an effort to digitize filing, I am currently building an Access database that tracks/stores personnel information, to include admin documents, training docs etc.  After attending an Advanced Access user class, I discovered that Access databases are limited to 2GB.  Because each document must be uploaded as a PDF file, loading them into the database tables will not be sustainable using the attachment feature in Access 2010 for long term use.
My goal is to make the previously created forms that had been built to save the attachments within the Tables, save instead to a shared drive and create a hyperlink to those stored files within the table using VBA.  I'm a Novice with VBA but have slowly started picking some of it up.
An example of the fields existing in my Admin Document Upload Form "AdminDocUploadfrm" are:

Employee- combo box
Document Description- combo box
Doc Date - Date
Doc Upload - Attachment
Submit - button

My intent is for the VBA programming behind the "Submit Button"  to format the save name of the doc by combining input/selections from the Doc Date+Employee name+Doc Description, save it to a designated location on the shared drive and create a hyperlink in the AdminDocTbl Access table that can be clicked to access the documents after upload.  

I found and embedded the following code in the Submit button, but am unsure of some of the required entries and if the format is correct (ex: What goes in the m_strFieldFileName area).  Iíve absolutely no idea where to start with automating generation of the hyperlink for the saved file asÖ. and insertion in the AdminDocTbl.  I can attach a copy of the stripped down database if that would help.

Iíve been searching the internet and reading books for weeks to try to decipher this with no luck
Any help in where to start on this is appreciated.  Thanks.

' ----------------------------------------------------------------
' Sub/Func : SaveAttachments
' Purpose  : Saves the attachments at the current row of the open Recordset
' Arguments: rstCurrent - The recordset open at the current row to save
'          : strFieldName - The name of the attachment field
'          : strOutputDir - The folder to put the files in (e.g. "C:Foo\")
' ----------------------------------------------------------------
Sub SaveAttachments(ByRef rstCurrent As DAO.Recordset, ByVal strFieldName As String, ByVal strOutputDir As String)
   Const CALLER = "SaveAttachments"
   On Error GoTo SaveAttachments_ErrorHandler

   Dim rstChild As DAO.Recordset2
   Dim fldAttach As DAO.Field2
   Dim strFilePath As String

   If Right(strOutputDir, 1) <> "\" Then strOutputDir = strOutputDir & "C:locationname"
   Set rstChild = rstCurrent.Fields(AdminDocUpload).Value ' The .Value for a complex field returns the underlying Recordset.
   While Not rstChild.EOF ' Loop through all of the attached files in the child Recordset.
       strFilePath = strOutputDir & rstChild.Fields(m_strFieldFileName).Value 'Append the name of the attached file to output directory.
       If Dir(strFilePath) <> "" Then ' The file already exists--delete it first.
         VBA.SetAttr strFilePath, vbNormal ' Remove any flags (e.g. read-only) that would block the kill command.
         VBA.Kill strFilePath ' Delete the file.
       End If
       Set fldAttach = rstChild.Fields(m_strFieldFileData) ' The binary data of the file.
       fldAttach.SaveToFile strFilePath
       rstChild.MoveNext ' Go to the next row in the child Recordset to get the next attached file.
   Wend
   rstChild.Close ' cleanup

   Exit Sub
SaveAttachments_ErrorHandler:
   Debug.Print "Error # " & Err.Number & " in " & CALLER & " : " & Err.Description
   MsgBox Err.Description, VbMsgBoxStyle.vbCritical, "Error # " & Err.Number & " in " & CALLER
   Debug.Assert False ' always stop here when debugging
   Resume Next
End Sub 'SaveAttachments

ANSWER: First, I think you may be making this more complicated than necessary. I'm not a big fan of the Attachment datatype either, though it does not carry the bloat that previous methods did. A couple of questions before I suggest a solution.

The documents that you are attaching to an employee record are they already descriptively named? Will you be using the Access app to find and launch these attachments or will there be a need to open the documents directly from Windows Explorer?

You refer to "fields existing in my Admin Document Upload Form "AdminDocUploadfrm". You don't have fields on a form, you have controls that may or may not be bound to fields in a table. This is a subtle but important distinction. So what does your Attachments/Documents table look like?


Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA


---------- FOLLOW-UP ----------

QUESTION: Scott,
 Glad to hear that there may be an easier way as this has been beating me down.  I'm not in love with the attachment datatype but didn't know any other way to do it.
 The attachments will already be descriptively named by date/employee name/doc type (ie: 20130403_SimsShannon_Admin.pdf).  The dbase will not be via Explorer but rather a frontend on required users computer with the BE residing on a shared drive so I'm assuming that it will be via the Access app.
 As for the Table, it contains the following fields/datatypes.
AdminDocID - autonumber
DocumentDescription - Text (lookup Combo Box)
DocumentDate - Date/Time
AdminDocUpload - Attachment
Employee - number (lookup combo box)
AdminDocLink - hyperlink

 As I stated earlier, if there is no need for the attachment field in the table (since the document is to be stored on the shared drive) I could probably delete that, but I didn't know how else to create a method for uploading a file.  The dbase isn't currently populated with data so making major changes to the table etc is no problem if you have recommendations.  

Thanks for your help in this

ANSWER: OK, I do a similar thing in a training database I have. Whenever we have training, employees are required to sign a roster. That roster is scanned and then linked to the employee. Scanned rosters can be retrieved from the employee or training class record.

Here's what I would recommend:

First, Your table would be like so:

AdminDocID - autonumber
DocumentTypeID - Number/Long Integer (Lookup to a table of document types, i.e Training certs, application, admin, etc)
DocumentDescription - Text a detailed description of the document
DocumentDate - Date/Time
Employee - number (lookup combo box)
AdminDocPath - Text

Second, On your form use the Double Click event of the control bound to AdminDocPath to run code that uses the FileDialog property to open the Windows File Open dialog and select the file. You can search on FileDialog to find sample code. Once selected the full path and filename are stored in the AdminDocPath field.

Third, I would add a button to the form to launch the document. The code behind the button would be:

Application.FollowHyperlink Me.AdminDocPath  (or whatever you name that control)

So, whenever the user presses the button, the document loads with the program associated with that document type (i.e PDfs open in the Acrobat Reader, DOCs in Word. Etc.)

Generally I would put this as a subform in Continuous form mode on the Employee form. Linked On Employee ID. So when you add a document, the Employee ID is automatically populated. The subform will display a list of documents associated with the employee.

If you need further help let me know.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: Scott,
 I finally feel I'm heading in the right direction, thanks to you.  I have researched and implemented the FileDialog suggestion and the following code works, but doesn't save the path and filename in the AdminDocPath. This also doesn't have code that determines where the selected file will be stored (shared drive) upon selecting the "save" control for the record.  Any thoughts on what I'm missing.  Apologies in advance for my lack of experience.

Private Sub AdminDocPath_DblClick(Cancel As Integer)
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
'the number of the button chosen
Dim FileChosen As Integer
FileChosen = fd.Show
'1) To set the caption of the dialog box,
' set the Title property
fd.Title = "Select Admin Document to Upload"
'2) Set the oddly named InitialFileName property to
' determine the initial folder selected
fd.InitialFileName = "C:\Users\swsims\Desktop\E-Pers Test\Admin Docs"
'3) Set the InitialView property to control how your files
' appear on screen (as a list, icons, etc.)
fd.InitialView = msoFileDialogViewSmallIcons
'4) To set the filters (you can have as many as you like)
' first clear any existing ones, then add them one by one
fd.Filters.Clear
fd.Filters.Add "PDF macros", "*.pdf"
fd.Filters.Add "Excel macros", "*.xlsm"
' if there's more than one filter, you can control which
' one is selected by default
fd.FilterIndex = 1
' if there's more than one filter, you can control which
' one is selected by default
fd.FilterIndex = 1
'5) Set the ButtonName property to control the text on
' the OK button (the ampersand means the following
' letter is underlined and choosable with the ALT key)
fd.ButtonName = "Choose PDF file"
If FileChosen <> -1 Then
'didn't choose anything (clicked on CANCEL)
MsgBox "Upload Cancelled"
Else
'display name and path of file chosen
MsgBox fd.SelectedItems(1)
End If
End Sub

Answer
Very good. But what you are missing is the assignment of the selected item to the control. You have to end the procedure by assigning some value to the control name.

However, since you also want to move the file to another location, you need some additional code move it to a new location. So first you need to store the selected file to a variable:

strSelectedFile = fd.SelectedItems(1)

Then you need to parse out just the filename from the path:

strFilename = Right(strSelectedFile,Len(strSelectedFile)-InstrRev(strSelectedFile,"\"))

Then you use the FileCopy command to copy the file to the new folder. But first you have to build the destination:

strDestination = "S:\AdminDocs\" & strFilename

Finally:

FILECOPY strSelectedFile, strDestination

If you want to delete the original file you can add:

KILL strSelectedFile

One last point. If you do want to change the name of the file before you copy it, then you can generate a different strFilename before you populate strDestination.

And you close the proc  with:

Me.AdminDocPath = strDestination

So the stored filed is part of the record.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.