469,086 Members | 1,235 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,086 developers. It's quick & easy.

Using VBA to save an attachment to shared drive via Access 2010 forms

In an effort to digitize our personnel files, I am currently building an Access database that tracks stores employee information, to include admin documents, leave requests, 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, loading them into the database will not be sustainable using the attachment feature in Access 2010.
My goal is to make the previously created forms that had been built to save the attachments within the Tables now save 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 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 the Doc Date+Employee name+DocDescription, save it to a designated location on the shared drive and create a hyperlink in the Access table. And complicating things further, the file format is PDF for all saved docs (Although I don't think I'll need to open the PDF's within Access.

Any help in where to start on this or where I can find some source code to start is appreciated. Thanks.
Mar 13 '13 #1
2 6808
5,400 Expert Mod 4TB
We all feel your pain here!
Even if you were using a "mature" RDMS in the long run, storing large amounts of data would become a headache and eventually lead to data corruption that you couldn't easily recover from.

The search on this site is a tad difficult at times; however, you should do one on hyperlinks, go to the bottom of this page and click on the VBA Insights Sitemap, look at article #26 and #88 as that could save you some time fetching the file paths. In fact, you might just spend some time scoping out that entire list.

As for the actual code.... please understand that we can help you with specific problems (one question per thread please) as you encounter them.
Mar 13 '13 #2
Actually found some code and customized it a bit to work. Still looking for an answer on the best way to automate the generation of a hyperlink to the filename so I can touch the file from Reports or querries.
Expand|Select|Wrap|Line Numbers
  1. Private Sub AdminDocPath_DblClick(Cancel As Integer)
  2. Dim fd As FileDialog
  3. Set fd = Application.FileDialog(msoFileDialogFilePicker)
  4. 'the number of the button chosen
  5. Dim FileChosen As Integer
  6. FileChosen = fd.Show
  7. '1) To set the caption of the dialog box,
  8. ' set the Title property
  9. fd.Title = "Select Admin Document to Upload"
  10. '2) Set the oddly named InitialFileName property to
  11. ' determine the initial folder selected
  12. fd.InitialFileName = "C:\Users\....."
  13. '3) Set the InitialView property to control how your files
  14. ' appear on screen (as a list, icons, etc.)
  15. fd.InitialView = msoFileDialogViewSmallIcons
  16. '4) To set the filters (you can have as many as you like)
  17. ' first clear any existing ones, then add them one by one
  18. fd.Filters.Clear
  19. fd.Filters.Add "PDF macros", "*.pdf"
  20. fd.Filters.Add "Excel macros", "*.xlsm"
  21. ' if there's more than one filter, you can control which
  22. ' one is selected by default
  23. fd.FilterIndex = 1
  24. ' if there's more than one filter, you can control which
  25. ' one is selected by default
  26. fd.FilterIndex = 1
  27. '5) Set the ButtonName property to control the text on
  28. ' the OK button (the ampersand means the following
  29. ' letter is underlined and choosable with the ALT key)
  30. fd.ButtonName = "Choose PDF file"
  31. If FileChosen <> -1 Then
  32. 'didn't choose anything (clicked on CANCEL)
  33. MsgBox "Upload Cancelled"
  34. Else
  35. 'display name and path of file chosen
  36. MsgBox fd.SelectedItems(1)
  37. End If
  38. 'store the selected file to a variable
  39. strSelectedFile = fd.SelectedItems(1)
  40. 'parse out the filename from the path
  41. strFilename = Right(strSelectedFile, Len(strSelectedFile) - InStrRev(strSelectedFile, "\"))
  42. 'build the destination
  43. strDestination = "C:\Users\....." & strFilename
  44. 'copy the file to the new folder
  45. FileCopy strSelectedFile, strDestination
  46. 'store file as part of the record
  47. Me.AdminDocPath = strFilename
  48. End Sub
Apr 4 '13 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by c_shah | last post: by
13 posts views Thread by Pamela via AccessMonster.com | last post: by
reply views Thread by =?Utf-8?B?SmltSGVhdmV5?= | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.