Code to Upload and Save Attachments External to Access 2007

[psyc0tic1]

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.

No worries at all... thank you for your time.

 

[psyc0tic1]

I have been doing a lot on this since I posted this morning... stuck on a small part.

I have a command button on my frm_visualinspectioninput that opens the form frm_visattachments. Here is the code for that form:

Private Sub cmdBrowseToFile_Click()
    Dim fDialog    As Object
    Dim varFile As Variant
    Dim strPath As String
    Dim partNum 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)
                Me.txtPath = 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
   
        strPath = "\\aa.com\afi-dfs\arcadia\Dept\International\Engineering - JA\QC Lab\Electrical Audit Database BE\Attachments\" & Forms!frm_visualinspectioninput!PONumber

    If Len(Dir(strPath, vbDirectory)) = 0 Then
        MkDir strPath
    End If

        strPath = "\\aa.com\afi-dfs\arcadia\Dept\International\Engineering - JA\QC Lab\Electrical Audit Database BE\Attachments\" & Forms!frm_visualinspectioninput!PONumber & "\" & "Visual"

    If Len(Dir(strPath, vbDirectory)) = 0 Then
        MkDir strPath
    End If

        partNum = DLookup("PartNumber", "tbl_parts", "ID = " & Forms!frm_visualinspectioninput!PartNumber)

        strPath = "\\aa.com\afi-dfs\arcadia\Dept\International\Engineering - JA\QC Lab\Electrical Audit Database BE\Attachments\" & Forms!frm_visualinspectioninput!PONumber & "\" & "Visual" & "\" & partNum

    If Len(Dir(strPath, vbDirectory)) = 0 Then
        MkDir strPath
    End If
   
   Call CopyFile(Me.txtPath, "C:\adBEs\Links\" & GetFilenameFromPath(Me.txtPath))
   
End Sub

Function GetFilenameFromPath(ByVal strPath As String) As String
' Returns the rightmost characters of a string upto but not including the rightmost '\'
' e.g. 'c:\winnt\win.ini' returns 'win.ini'
 
    If Right$(strPath, 1) <> "\" And Len(strPath) > 0 Then
        GetFilenameFromPath = GetFilenameFromPath(Left$(strPath, Len(strPath) - 1)) + Right$(strPath, 1
    End If
End Function

What that is doing is creating a directory under Attachments based on the value in the form field PONumber in the frm_visualinspectioninput
Then creates a directory under the directory created by the PONumber field value called "Visual".
Then creates a directory under the "Visual" based on the value of the PartNumber field on the form frm_visualinspectioninput

Using fictitious values for posting purposes, it creates the path like this blablabla\P0BD154\77842\images chosen go here. This works... all directories are created correctly.

The part I am having trouble with is this line:

Call CopyFile(Me.txtPath, "C:\adBEs\Links\" & GetFilenameFromPath(Me.txtPath))

I do not know how to make that path work to put the attachment in that final directory and add the hyperlink to the tblLink.

There is a good reason why I create the directories the way I do which is to avoid all images going into one directory because people may reuse the same image name from time to time.

The end result seems like it needs a submit button to create the directories and then upload the file and create the links in the table.

I also changed my original table relationship from one-to-one between the AuditID of the tbl_auditdata(where the records are stored) and the ILinkID (where the links are stored) to a one-to-many between the AuditID and the IRecordID

After all that is figured out I do not know how your code allows me to pick more than one file at a time. They could have anywhere from 1 to 5 files to upload.

 

[GinaWhipp]

Looks like you are moving right along! Good to hear.

The CopyFile() copies from the existing location, Me.txtPath, to the new location, C:\adBEs\Link\..., so you need to...

Call CopyFile("Files Existing Location", "Files New Location"))

That said, this code was not designed to grab more than one file at a time. You would need a loop for that.

 

[psyc0tic1]

Looks like you are moving right along! Good to hear.

The CopyFile() copies from the existing location, Me.txtPath, to the new location, C:\adBEs\Link\..., so you need to...

Call CopyFile("Files Existing Location", "Files New Location"))

That said, this code was not designed to grab more than one file at a time. You would need a loop for that.

The first part seems ok

Call CopyFile(Me.txtPath,

The next part is what I am having trouble with

"C:\adBEs\links\"

The last part doesn't come into play due to the second part

& GetFilenameFromPath(Me.txtPath))

So I am creating a directory under the "Attachments" directory that is using the value in the form field (PONumber) populated from the table (tbl_auditdata) via the query that is the forms recordsource

         strPath = "\\aa.com\afi-dfs\arcadia\Dept\International\Engineering - JA\QC Lab\Electrical Audit Database BE\Attachments\" & Forms!frm_visualinspectioninput!PONumber

    If Len(Dir(strPath, vbDirectory)) = 0 Then
        MkDir strPath
    End If

Then I am creating a directory under that using the name of (Visual)

        strPath = "\\aa.com\afi-dfs\arcadia\Dept\International\Engineering - JA\QC Lab\Electrical Audit Database BE\Attachments\" & Forms!frm_visualinspectioninput!PONumber & "\" & "Visual"

    If Len(Dir(strPath, vbDirectory)) = 0 Then
        MkDir strPath
    End If

Then I am creating a directory under that using the value in the form field (PartNumber) populated from the table (tbl_auditdata) via the query that is the forms recordsource

        partNum = DLookup("PartNumber", "tbl_parts", "ID = " & Forms!frm_visualinspectioninput!PartNumber)

        strPath = "\\aa.com\afi-dfs\arcadia\Dept\International\Engineering - JA\QC Lab\Electrical Audit Database BE\Attachments\" & Forms!frm_visualinspectioninput!PONumber & "\" & "Visual" & "\" & partNum

    If Len(Dir(strPath, vbDirectory)) = 0 Then
        MkDir strPath
    End If

So at this point I do not know how to complete the second part of the CopyFile string of code the "Files new location"

I have tried this which throws a run-time error '-2147024809 (80070057)': sorry something went wrong

Call CopyFile(Me.txtPath, strPath & GetFilenameFromPath(Me.txtPath))

I have tried putting in the full path from the last strPath string but this tells me a compile error Expected list seperator or ) message

Call CopyFile(Me.txtPath, "\\aa.com\afi-dfs\arcadia\Dept\International\Engineering - JA\QC Lab\Electrical Audit Database BE\Attachments\" & Forms!frm_labtestinput!PONumber & "\" & "Visual" & "\" & partNum", & GetFilenameFromPath(Me.txtPath))

I do not know how to get that last directory path recognized in the new file location part of the CopyFile code string

If anyone could help me get that code correct I would greatly appreciate it.

 

[psyc0tic1]

It appears it was the actual CopyFile part... it needed to be FileCopy.

Cleaned up the code a bit more and noticed that it is storing the wrong link in the table. It is storing the link to the original file location rather than the new file location.

Working on that and trying to make it so multiple files can be chosen.

 

[GinaWhipp]

Yep, day job again so came for a quick read and not sure what I am supposed to answering. Can you help me out when I return for my next quick read? I just need to know what parts you are still hung up on.

 

[psyc0tic1]

Not hung up on the CopyFile part anymore

Actually making good progress on the rest of the stuff I mentioned. I have our in-house code master helping me right now and the code has changed a lot.

I will post the solution when it is done so anyone else looking for this can use your or mine or a combination of to help their project(s)

 

[GinaWhipp]

Happy to hear! Got tied up so happy you had a back-up plan!

 

[GinaWhipp]

Oops, will have to fix that! Thanks for the heads up!

 

[AlphaMike]

So... I've got that to work (party!!)

but... how do I "start using" the exported file instead??

 

[psyc0tic1]

So... I've got that to work (party!!)

but... how do I "start using" the exported file instead??

What do you mean by "Start using"? Like in a report?

 

[GinaWhipp]

Not sure I understand the question, please elaborate...

 

[psyc0tic1]

Here is the final code for the attachments form. I made a command button on the form (which is in tab control) that opens the attachments form.

remember that my database is used across a WAN so the strPath would be different for you on a LAN

Button code:

Private Sub cmdLabAttachments_Click()
    DoCmd.OpenForm "frm_labattachments", , , , , acDialog
End Sub

Attachment form code:

Option Compare Database
Option Explicit
Dim RecordID As Integer

Private Sub cmdBrowseToFile_Click()
    Dim fDialog As Object
    Dim varFile As Variant
    Dim savePath 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..."
 
      ' 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
            For Each varFile In .SelectedItems
                savePath = GetDirectory() & "\" & GetFilenameFromPath(varFile)
                lstFiles.AddItem 0 & ";" & RecordID & ";" & varFile & ";" & savePath & ";" & GetFilenameFromPath(varFile)
            Next
        Else
            MsgBox "You clicked Cancel in the file dialog box.", vbOKOnly, "Select a File"
            Exit Sub
        End If
   End With
End Sub

Function GetFilenameFromPath(ByVal strPath As String) As String
' Returns the rightmost characters of a string upto but not including the rightmost '\'
' e.g. 'c:\winnt\win.ini' returns 'win.ini'
 
    If Right$(strPath, 1) <> "\" And Len(strPath) > 0 Then
        GetFilenameFromPath = GetFilenameFromPath(Left$(strPath, Len(strPath) - 1)) + Right$(strPath, 1)
    End If
End Function

Function GetDirectory() As String
    Dim strPath As String
    Dim partNum As String
    strPath = "\\aa.com\afi-dfs\arcadia\Dept\International\Engineering - JA\QC Lab\Electrical Audit Database BE\Attachments\" & Forms!frm_home.Lab_Test_Input_Form.Form.PONumber

    If Len(Dir(strPath, vbDirectory)) = 0 Then
        MkDir strPath
    End If

        strPath = strPath & "\" & "Lab"

    If Len(Dir(strPath, vbDirectory)) = 0 Then
        MkDir strPath
    End If

        partNum = DLookup("PartNumber", "tbl_parts", "ID = " & Forms!frm_home.Lab_Test_Input_Form.Form.PartNumber)

        strPath = strPath & "\" & partNum

    If Len(Dir(strPath, vbDirectory)) = 0 Then
        MkDir strPath
    End If
    
    GetDirectory = strPath
End Function

Private Sub cmdSubmit_Click()
    Dim dbs As DAO.Database
    Dim strPath As String
    Dim varFile As Variant
    Dim i As Integer
    
    Set dbs = CurrentDb
    For i = 0 To lstFiles.ListCount - 1
        If lstFiles.Column(0, i) = 0 Then
            FileCopy lstFiles.Column(2, i), lstFiles.Column(3, i)
            dbs.Execute "INSERT INTO tblLinks (IRecordID, IPath, IDescription, Iissue) VALUES (" & lstFiles.Column(1, i) & ", '" & lstFiles.Column(3, i) & "', '" & lstFiles.Column(4, i) & "', '" & txtIssue.Value & "');", dbFailOnError
        End If
    Next i
    
    txtIssue.Value = ""
    
    While Not lstFiles.ListCount = 0
        lstFiles.RemoveItem 0
    Wend
    
End Sub

Private Sub Form_Load()
    RecordID = Forms!frm_home.Lab_Test_Input_Form.Form.AuditID
End Sub


Private Sub lstFiles_DblClick(Cancel As Integer)
    lstFiles.RemoveItem lstFiles.ItemsSelected(0)
End Sub

It pulls information from a couple of fields from the form it was opened from for creating the directories

 

[GinaWhipp]

Great! So it looks like you are all set. And thanks for sharing it's bound to help someone else.

 

[psyc0tic1]

For visual reference...

On the tab on my main form you click the attachments button and the code knows the current record showing on that page

The attachments form opens and you can browse for your file and then add an explanation of the file. Once you choose a file and it is displayed in the top field... you can double click that and it will delete if you so choose.

You can pull a report on the attachments

You can click the preview button and pull up the larger image for review

 

[psyc0tic1]

I forgot to mention that the attachments form will refresh every time you click the submit button and you can continue to add more files one at a time... or.... if you have multiple pictures you want to add with the same explanation you can either keep clicking the browse for file button or select multiple files from windows explorer and it will create multiple entries in the tblLinks

 

[AlphaMike]

Not sure I understand the question, please elaborate...

At work, each person has their computer access. That means we will each have our files in something like

\\location\users$\username\

There are shared drives for common documents.

My goal is that each person can add a picture from their "My pictures" or "my Downloads" into the database and, after it has been moved to the Shared drive (g:\AllEmployees\DestinationFolder\), the db would "fecth/read" the pictures from there, otherwise there'll be a permission error as we do not have access to other people's files in our sessions.

So, after the "FileCopy" line, I need to be able to have Access read from the new file (copy).


(also, I'd love to have the date/time added to the beginning of the copied file).

 

[GinaWhipp]

Well, if I'm getting this right, what you need is to separate the two, FileCopy() moves the file but then the line that puts the path into the database needs to put the where it is going to be not where is copied it from.

 

[psyc0tic1]

but then the line that puts the path into the database needs to put the where it is going to be not where is copied it from.

That has been worked out in the code I posted above above.

@AlphaMike... The code I posted above will do all of that. You will need to change the directory creation code to meet your needs and change it from a WAN address to a LAN address (g:\AllEmployees\DestinationFolder\). From the code above it is creating directories based off of a destination directory called "Attachments", and then creating other directories based off of information on the form that was open when clicking the button to open the attachments form... you will need to change that information to suit your needs.

It uploads the files... creates the links and you are ready to access the pictures through a report or a form from there using a query.

 

[AlphaMike]

It uploads the files... creates the links and you are ready to access the pictures through a report or a form from there using a query.

Yeah... I'll have to play with my search queries after that...

Thanks! I'll test it now!