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

[shannonsims]

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.

[zmbd]

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.

[shannonsims]

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.

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\....."
'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
'store the selected file to a variable
strSelectedFile = fd.SelectedItems(1)
'parse out the filename from the path
strFilename = Right(strSelectedFile, Len(strSelectedFile) - InStrRev(strSelectedFile, "\"))
'build the destination
strDestination = "C:\Users\....." & strFilename
'copy the file to the new folder
FileCopy strSelectedFile, strDestination
'store file as part of the record
Me.AdminDocPath = strFilename
End Sub