1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Upload files from users PC to Server

Discussion in 'Business Applications' started by scuzas, Jun 23, 2016.

Thread Status:
Not open for further replies.
Advertisement
  1. scuzas

    scuzas Thread Starter

    Joined:
    Jun 23, 2016
    Messages:
    2
    I have an MS Access application that sites on a server where multiple users can access it using Terminal Server. The business would like the ability to upload image files to a record in the database. Now I've read many places that its not a good idea to add the image to an Access table because eventually the database size will become quite large and the application will take a performance hit.

    What I would like to do is upload the images to the terminal server and save the path in the Access table. The part I'm having a hard time with is that upload. I can use the FileDialog object, but if the application is running on the server, and it pulls up the FileDialog, won't it show the directories of the server and NOT the users machine right?

    Any examples on how to solve this issue is greatly appreciated.
     
  2. draceplace

    draceplace

    Joined:
    Jun 8, 2001
    Messages:
    2,583
    Your image upload could be form driven if the paths (in and out) are static. Naming the file can be an issue and file shares don't like files with the same name. A way around that is to add the recordID (or completely control the name) to the file name when the user saves.

    While storing documents in access is clunky and leads to bloat it can be done. Depending on your volume and the image size(s) it might not be as bad as you think. I have process where I take 1000 records from sharepoint to Access then extract the documents as batch (SharePoint will only let you at them 1 at time). The 1000 limit records to keep the file under 1000MB. You results will vary.

    My thought (without knowing the process) would be, if needed you could store the images in Access and move them out (saving new path) with a nightly\weekly process. Then compact/repair the Access DB. I've got the code to extract and delete the documents.
     
  3. scuzas

    scuzas Thread Starter

    Joined:
    Jun 23, 2016
    Messages:
    2

    here's a little more detail.... The application manages product defects and sends out a report when there is a new defect created. the business would like the ability to upload images of the defected product. They would like the ability to upload multiple images per defect.

    I'm just about out of possible solutions. Maybe I create the form to upload the documents and have them stored in the database then nightly extract them to a location on the server then saving the path in the database. The challenging part would be if the user sends out a defect summary of a product right after they upload the images prior to the nightly job being executed. I guess I can create an identical report, one that would pull the images from the database and another pull from a file on the server? Thoughts?

    can you send over the code to extract and delete documents?
     
  4. draceplace

    draceplace

    Joined:
    Jun 8, 2001
    Messages:
    2,583
    One Possibility would be to allow 'current/active' defects to be pull from the database and archives pulled from the file share. You could define your archives time period based on volume. Another option would be immediately extract the document after upload and save the path to the record.

    Here is the code that extracts the attachments. Its run from a button on a form with a data source of a query with the desired records. This one needs a field called ID (Me.ID is appended to file name to insure uniqueness) and the attachments. The query can include other fields for selection.

    There can be multiple attachments to the same row and this code will extract each 'child'. This code only deletes the attachment not the record. The storage path is hardcoded but could easily be form driven.
    Code:
    Private Sub cmd_attatch02_Click()
    On Error GoTo Err02_SaveImage
        Dim db As DAO.Database
        Dim rsParent As DAO.Recordset2
        Dim rsChild As DAO.Recordset2
        Dim strPath, strFullPath, strSpID As String
        Set db = CurrentDb
        Set rsParent = Me.Recordset
        rsParent.OpenRecordset
        cntFile = 0
        cntDoc01 = 0
     
        strPath = "\\ServerName\ShareName\FolderName\"
        With rsParent
            .MoveFirst 'This ensures that regardless of your current record, the loop will start with the first record
            Do While Not .EOF 'Or cntFile > 20
                 cntFile = cntFile + 1
                 strSpID = Me.ID & "_"
                 Set rsChild = rsParent.Fields("Attachments").Value
               
                With rsChild
                    Do While Not .EOF
                        If rsChild.RecordCount <> 0 Then
                            rsChild.OpenRecordset
                         
                            strFullPath = strPath & strSpID & rsChild.Fields("FileName")
             
                            rsChild.Fields("FileData").SaveToFile strFullPath
                         
                           
                            rsChild.Delete
                            cntDoc01 = cntDoc01 + 1
                            Me.Refresh
                           
                            .MoveNext
                        Else
                            .MoveNext
                        End If
                       
                    Loop
                    .Close
                     '    MsgBox "Record cw"
                End With
                .MoveNext
            Loop
            .Close
             'MsgBox "Recordset cw"
        End With
    Exit_SaveImage:
    MsgBox ("Records Processed: ") & cntFile & vbCrLf & ("Documents Processed: ") & cntDoc01, vbOKOnly
        Set rsChild = Nothing
        Set rsParent = Nothing
        Exit Sub
    Err02_SaveImage:
        If Err = 3839 Then
            MsgBox ("File Already Exists in the Directory!")
            Err.Clear
    '   With rsParent
    '   .Edit
    '   !PF1 = rsParent!ID & "Duplicate"
    '   .Update
    '   End With
    '    Set rsChild = rsParent.Fields("Attachments").Value
            Resume Next
        Else
            MsgBox "There's been an error!" & vbCrLf & _
           " Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
           Err.Clear
            Resume Exit_SaveImage
        End If
        MsgBox "Export complete!" & vbCrLf & "Count = " & cntFile
    End Sub
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1173429

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice