Code to Upload and Save Attachments External to Access 2007 (1 Viewer)

leblanc9425 · May 15, 2013

Background: I created a simple database that my office uses to capture the outcome of meetings. Users input about 25 lines of text to summarize the meeting and then can add Attachments. I use Access 2007, so I have incorporated an Attachment field for users to upload files (Powerpoint, PDF, Word, Excel, etc) that were part of the meeting. It's working great and people in the office can quickly search the db for a meeting and view all the related files in one place.

Problem: the db is getting large - nowhere near the 2GB limit but I am looking to the future. I currently have only 200 records and the db is already 60mb. I believe the problem is with adding the attachments to the db.

Help Needed: I am looking for any sample code or solutions that will do the same thing as the Attachment field but stores the actual attachment outside or external to the db. From the Users perspective they would do the same thing as before by simply Adding an Attachment to the record.
allows the users to browse to a file which then

I expect that a solution is complicated because I assume the code would have to do the following:

Allow User to browse to the file location and select a file
Append a unique ID to the file (ie 157-A Sample File.docx)
Save the file with new appended name to a default directory
Record in a related table (tblFiles) the location of the file
Display to the User all files associated to the record as a hyperlink

I am hoping that others have already addressed the problem of a bloated Access database with attaching too many files and can offer solutions, articles, sample codes......anything.

mdlueck · May 15, 2013

You could dynamically name the "attachments", store then on a file server, and retain in the DB the path\filename of the file.

leblanc9425 · May 15, 2013

Wow! That was fast!

Yes, that sounds to be exactly what I am trying to do. However, I don't know the first place to start.

Can you point me to any tutorials, sample code, etc. to help me get started? Although I am a novice, I am pretty good at picking apart code to get it to work for me.

Thanks again for the prompt response!

Dave

mdlueck · May 15, 2013

I know of no "tutorial" of how to do this... I am one that sets a vision, and then methodically researches / develops to reach that goal.

Was there any aspect in my proposed scenario that particularly seemed challenging to you? If so I might be able to assist in that specific area.

leblanc9425 · May 15, 2013

Well...the whole thing! I have been searching the forum (this and others) because I am sure this issue has been addressed before. As I uncover information I will try to update this thread.

AlphaMike · Oct 26, 2018

Any luck so far?

I'm building a similar database for my workplace. Forms will have pictures as attachments and I'd rather have the pics be saved in a specific folder and have access "retrieve" them than having a 2Gb db.

Micron · Oct 26, 2018

Sounds to me it would be like this

Access user clicks on form button. Code looks at table for path last used by this user. If not found, msofildialogfilepicker simply opens, else opens to last folder. Chosen path becomes the last path. User chooses a file. Either the file is copied to a predetermined folder and the new path is stored in table and the old file is killed, or the file stays where it is and the known destination path is stored in a table, or a 2nd dialog opens to choose the destination folder, or some combination thereof. Killing the file would be much more difficult to do safely as the transfer should be verified first. When the process is complete, you could display a thumbnail of the item on a form, along with pertinent data. However, if there could be more than one document associated with a record, you'd need a way to cycle through them if you want to see a thumbnail. IMHO, the only reason to complicate it with a thumbnail is to quickly see a document field for some small piece of data, such as when completing form data related to document data. Otherwise, not sure I see the point. The more bells and whistles you add, the more complex it becomes. It's pretty safe to say that if you can imagine it, it can be done, subject to the limitations of time and knowledge.

The choice to allow multi file select would be up to you, but would complicate the code - especially if the file names are not all related to the same record PK. You probably should review the msofiledialog and note that the file picker is for what the name suggests. Same for the folder picker - thus it doesn't show files in a folder.

leblanc9425 · Oct 28, 2018

Yes, after some searching, I did locate a script that does what I wanted. Basically, it allows the user to browse/select a file, enter a description (optional) and then upload it to the database but in reality, it just copy and saves it into another directory with the record number appended to the file.

A separate table stores the path to the attachments.

I have the script at work and I will post it to this thread next week (should have done this earlier).

psyc0tic1 · Oct 29, 2018

Looking forward to seeing this code.

AlphaMike · Oct 30, 2018

I could use it as well...

GinaWhipp · Oct 30, 2018

Hmm, perhaps this...
https://www.access-diva.com/d15.html

psyc0tic1 · Oct 30, 2018

That looks promising... going to try that tomorrow. That was a great find... I am surprised I never turned that up during my searches. Maybe I didn't know what I was looking at.

GinaWhipp · Oct 30, 2018

Standing by... (in case you run into issues)

Gasman · Oct 30, 2018

That is not a find? That is Gina's site, she has written all of that code.

psyc0tic1 said:
That looks promising... going to try that tomorrow. That was a great find... I am surprised I never turned that up during my searches. Maybe I didn't know what I was looking at.

GinaWhipp · Oct 30, 2018

@Gasman

Well, it's a find to the OP

psyc0tic1 · Nov 1, 2018

GinaWhipp said:
@Gasman

Well, it's a find to the OP

I am working on trying to meld different code together to use this and it work from my forms command button to submit the record update.

Currently the form that I am modifying has a dropdown with current records in the database that need updating. Some fields are auto populated to show the user information on the record that is already in the database that needs more information added to it. Currently there is an attachment field in the table (tbl_auditdata) and on the form.

I am trying to figure out your code but there are fields mentioned in the code that are not shown or explained on the page you linked.

So I am eating the elephant one bite at a time.

I have modified code I found that will create a directory with the value in a currently populated field (PONumber) and that works fine.

Next I need to figure out in your code how to be able to choose the files to be uploaded and hold them until the "Submit" command button is clicked to save the record changes.

I created the table as you described and I created a one-to-one relationship from the "AuditID" field in my tbl_auditdata to the ILinkID field in the tblLinks.

I do not know if this is the correct way for the newly generated link to be linked to the record in the tbl_auditdata.

Right now I made a temporary command button for generating the directory for the attachments to go in for testing purposes. The code looks like this:

Code:

Private Sub Command66_Click()
    Const strParent = "\\aa.com\afi-dfs\arcadia\Dept\International\Engineering - JA\QC Lab\Electrical Audit Database BE\Attachments\"
    Dim strPONumber As String
    Dim strFolder As String
    Dim fso As Object
    ' Get PO Number from control
    strPONumber = Me.PONumber
    ' Full path
    strFolder = strParent & strPONumber
    ' Create FileSystemObject
    Set fso = CreateObject("Scripting.FileSystemObject")
    ' Check whether folder exists
    If fso.FolderExists(strFolder) = False Then
        ' If not, create it
        fso.CreateFolder strFolder
    End If
    ' Open it
    'Shell "explorer.exe " & strFolder, vbNormalFocus
End Sub

This is the code for the actual command button that submits the changes tot he record:

Code:

Private Sub Vis_Input_Submit_Click()
    Dim strMsg As String
    
    blnGood = True
    
    If (validate) Then
        Me.Recordset.Edit
        Me.Recordset.Fields("status").Value = "Waiting On Lab"
        Me.Recordset.Fields("VisualInspectorUserId").Value = Credentials.UserId
        Me.Recordset.Update
        If Me.CurrentRecord < Me.Recordset.RecordCount Then
            Me.Recordset.MoveNext
        Else
            Me.Recordset.MoveFirst
        End If
    Else
        strMsg = "All Fields are required."
        Call MsgBox(Prompt:=strMsg, Title:="Before Update")
    End If
        Application.Echo False
        DoCmd.Close
        DoCmd.OpenForm "frm_home"
        Form_frm_home.Visual_Inspection_Input_Form.SetFocus
        Application.Echo True
    blnGood = False
End Sub

psyc0tic1 · Nov 1, 2018

The reason I am creating directories using the value of the PONumber field is because the users might name their images the same from record to record like "Image1" Image2" and that would get confusing if they were all in one directory so since the records are mainly identified by us using the PO Number... I chose to separate them that way.

psyc0tic1 · Nov 1, 2018

Can you tell me how to integrate the 3 sets of code into one?

It seems I need to create a form as the dialog popup for selecting the files to upload.

I do not know for your page where txtPath is on the form you showed but I would like to have the images upload and the links created when the record form is submitted rather than having to submit a dialog form then submit the record update form.

Am I confused on this or making sense?

psyc0tic1 · Nov 1, 2018

I think I am confused...

I need to build the form that is used for the attachment file choice. from your code:

Code:

Private Sub cmdLinks_Click()
 
    Dim fDialog    As Object
    Dim varFile As Variant
    Dim strPath As String
 
   ' Set up the File Dialog.
    Set fDialog = Application.FileDialog(1)
 
    With fDialog
      'Set the title of the dialog box. '
        .Title = "Select the File..."
        .InitialFileName = "C:\"
 
      ' Show the dialog box. If the .Show method returns True, the
      ' user picked at least one file. If the .Show method returns
      ' False, the user clicked Cancel.
        If .Show = True Then
                varFile = .SelectedItems.Item(1)
                [COLOR="Red"]Me.txtPath[/COLOR] = varFile
                Me.txtDescription = GetFilenameFromPath(Me.txtPath)
                Me.txtTimestamp = FileDateTime(Me.txtPath)
        Else
                MsgBox "You clicked Cancel in the file dialog box.", vbOKOnly, "Select a File"
        End If
   End With
      Call CopyFile(Me.txtPath, "C:\adBEs\links\" & GetFilenameFromPath(Me.txtPath))
      
End Sub

I do not know where this is on your form (test in red) or where the command button is that uploads the files and writes the links to the table.

GinaWhipp · Nov 1, 2018

I am tied up at the moment and I can see I need to READ this. If no on else comes along... please be patient while I do my day job.

Code to Upload and Save Attachments External to Access 2007 (1 Viewer)

Registered User.

Sr. Application Developer

Registered User.

Sr. Application Developer

Registered User.

New member

AWF VIP

Registered User.

Access Moron

New member

AWF VIP

Access Moron

AWF VIP

Enthusiastic Amateur

AWF VIP

Access Moron

Access Moron

Access Moron

Access Moron

AWF VIP

Similar threads

Users who are viewing this thread