Access DB attachments form and VBA

[rdab100]

Might be a daft question but the functionality of the attachments on an Access DB form. Double click on the field and it opens a form with a listbox (guess) and 7 button on it...

Is this form and code documented anywhere?

It will be quite a pain to have to recode it so thought I'd ask.

I not gone down the route of filestream etc but storing pointers in the DB instead, right now SSMA will just give the filename, no path information. 80%+ are in the same location so didn't want to hard code it. 100% are on shared drives.

I use the split function to take the multi entry attachments field to populate variables etc, and checked with msgbox that I have done it right...
The ideas came from this forum so kudos to those involved.

I stripped out the error handling (used the on error goto for now which I am sure might not be the best option)

So I sharing... this is how I had done part of it... if say a 'add' button were selected.

'Taken from threads here unc-filepath-from-file-selector-dialog.308912
' This works every time.

Option Compare Database

Private Sub cmdFileDialog2_Click()

'It's a shared application and some don't always map the same drive letter this means will have to go to UNC paths

'High level overview:
'First opens a file selector to allow the user to select a file
'Second, the filepath of the selected file is stored into variable "RawHyperlink"
'Next it extracts the drive letter and stores it to variable "DriveLetter"
'The remaining subfolders and filename are stored to variable "FolderFile"
'It converts the value in DriveLetter to a UNCPath using the GETNETWORKPATH module
'Finally it merges the UNCPath and FolderFile back into a usable hyperlink, and populates it into a clickable textbox "TxtFileHyperlink"

'need to substitute this in for the normal browse and add the filters

'this will then populate the  original attachment link

Dim f As Object
Dim RawHyperlink As String

'for the delimiter
'currently use different ones but will get them to standardise on ;

Dim strchar As String

'I added some filters so only cerain filetypes could be chosen
Set f = Application.FileDialog(3)

'maybe add the filters:
  f.Filters.Clear
  f.Filters.Add "Text/CSV files", "*.txt, *.csv"
  f.Filters.Add "Excel/Word files", "*.doc;*.docx;*.xls;*.xlsx;*.xlsm"
  f.Filters.Add "PDF files", "*.pdf"

'I think this might need attention ?
f.AllowMultiSelect = True
'The non UNC used f.AllowMultiSelect = True as they were allowed to select several files at once

If f.Show Then
    For i = 1 To f.SelectedItems.Count
        sFile = nameoffile(f.SelectedItems(i), sPath)
        RawHyperlink = sPath & sFile
        DriveLetter = Left(RawHyperlink, 2)
        FolderFile = Mid(RawHyperlink, 3)
        UNCPath = GETNETWORKPATH(DriveLetter)
        txtFileHyperlink = UNCPath & FolderFile
'this msgbox is just here as debug
'Note select a file local then there is no UNCPath

'The msgbox for the different variables to see how they get built up.

MsgBox "RawHyperlink: " & RawHyperlink & vbCrLf & _
         "DriveLetter: " & DriveLetter & vbCrLf & _
         "UNCPath: " & UNCPath & vbCrLf & _
         "File: " & sFile
        MsgBox "Filename:" & txtFileHyperlink

'need to add in logic to add ; character if there is more than one of these as there are in almost all cases
'do not forget to make the variable equal what the data is the textbox
           txtFileHyperlink = filename
           If txtFileHyperlink = "" Then
           strchar = ""
           Else
           strchar = ";"
           End If
           txtFileHyperlink = txtFileHyperlink + strchar & "" & UNCPath & "" & FolderFile
      
'this msgbox is just here as debug
MsgBox "Filename:" & txtFileHyperlink
       filename = txtFileHyperlink
       Next

End If
End Sub

Public Function nameoffile(ByVal strNetPath As String, sNetPath) As String

    sNetPath = Left(strNetPath, InStrRev(strNetPath, "\"))
    nameoffile = Mid(strNetPath, InStrRev(strNetPath, "\") + 1)
  
End Function

Private Sub cmdFileSelector_Click()
MsgBox GETNETWORKPATH(txtNetworkDriveLetter)
End Sub


Public Function GETNETWORKPATH(ByVal DriveName As String) As String
  
    Dim objNtWork   As Object
    Dim objDrives   As Object
    Dim lngLoop     As Long
  
  
    Set objNtWork = CreateObject("WScript.Network")
    Set objDrives = objNtWork.enumnetworkdrives
  
    For lngLoop = 0 To objDrives.Count - 1 Step 2
        If UCase(objDrives.Item(lngLoop)) = UCase(DriveName) Then
            GETNETWORKPATH = objDrives.Item(lngLoop + 1)
            Exit For
        End If
    Next

End Function

 

[theDBguy]

Is this form and code documented anywhere?

The LoadFromFile and SaveToFile methods are documented.

Work with attachments in DAO

Office VBA reference topic

docs.microsoft.com

 

[rdab100]

Thanks was sort of aware of the DAO page, I looked at that page first trouble is it did not seem to play nice with UNC paths, or multi entry fields, or more likely was my really bad VBA coding, anyway as was pulling out hair when found the other thread which worked for the save to and adding the delimiter needed

The remove seems to be an issue, the logic I am working on seems to be a bit nuts, maybe someone could comment?
First you read in the whole field, split it and find all the entries, save to some array
Then either delete the ones from the array you don't want to keep and write it back or build (assuming more than one) the list of those you removing and use that list as the driver, almost same thing. It might be fun to see how M$ did it.

The list (split of the original attachments field) is that just a simple list box, I couldn't find any reference to what it is?

Opening the file from the list box I am sure there are n+1 more ways than DBA/Programmers in a room I was using the simple hyperlink method and passing the value to it like this once the focus captured the value...... is there an alternative?

Private Sub Command8_Click()

On Error GoTo ErrorHandler ' Enable error-handling routine.
Application.FollowHyperlink [filename]
'if need to add in multiple files just add them as extra files (a loop through array seems easiest)
'populate array then loop through and pass to the same
'Application.FollowHyperlink the first arraymember, 2nd, 3rd etc.
'This didn't seem to need any extra coding and relying on the users machine having the application installed locally you can open doc, docx, xls, pdf etc...

'I put this error handling in to see what happens if say tried to open a file without the application installed locally
'thank fully you get a helpful error code and it carries on, and doesn't end in a smelly heap and bomb out with the typical end/debug msgbox.
ErrorHandler:
    LogError Err.Description
    MsgBox "UNKNOWN ERROR  - Error# " & Err.Number & " : " & Err.Description
    Resume Next
    Debug.Print "Continue execution"
End Sub

 

[rdab100]

I played with creating a DAO recordset from a form as well as the array I mentioned.

Dim attachmentfiles As DAO.Recordset
 
Set attatchmentfiles = Forms!Attachments1.filemame

Then used the more familiar DAO record set methods to allow you to perform on them.

 

[theDBguy]

It might be fun to see how M$ did it.

Do you mean how MS is using a hidden table to store the Attachments?

 

[rdab100]

Do you mean how MS is using a hidden table to store the Attachments?

You mean the FileData, FileFlags, FileName, FileTimeStamp, FileType, FileURL fields?

 

[Pat Hartman]

Attachments are another of the "abomination" data types that support a 1-m relationship by using a hidden table. Like all the rest of them, they handle very specific fields and you have no expansion or modification option. You get what you get. They are also not upsizeable so if you see SQL Server in your future, best to use the old method rather than attachments.

 

[theDBguy]

You mean the FileData, FileFlags, FileName, FileTimeStamp, FileType, FileURL fields?

Yes. Attachments are stored in a hidden table usually labeled "f_" something.

 

[rdab100]

It is all the code off those 7 buttons (the pop up form that appears if you double click an attachment field on a report) that would be interesting. I note that the paper clip and number appear in some and a little icon saying what 'type' of file it is word, excel, pdf etc... so I guess there must be some code that uses those 'hidden fields'.

 

[rdab100]

Attachments are another of the "abomination" data types that support a 1-m relationship by using a hidden table. Like all the rest of them, they handle very specific fields and you have no expansion or modification option. You get what you get. They are also not upsizeable so if you see SQL Server in your future, best to use the old method rather than attachments.

Sadly this is a case of cannot change the application look or feel but have to swap the back end out (that bit was easy) and redo all the code/functionality with SQLServer. Was all going too easy until noted they using attachments. I had kinda hoped given this is all M$ they had this easily in some knowledge pages etc... err no. Fortunately only 2 attachment fields in two tables but appear on user 17 forms and a few use the view attachment functionality This is going to last a few years.

 

[Gasman]

See if this link can throw any light on the topic.

UtterAccess.com

www.utteraccess.com

 

[rdab100]

See if this link can throw any light on the topic.

Thanks. Looks like we are going to go the route of NOT storing the files in the DB just the pointers (not going Var Binary aka BLOB or filestream) about 25% of the records have one or more 'attachments', <5% have more that 4, but have to sort the UNC path etc to the original (which would always exist elsewhere anyway) posted the UNC add method previously. So trying to work out what those buttons from the attachments 'should do' as well as 'actually do'.

 

[theDBguy]

It is all the code off those 7 buttons (the pop up form that appears if you double click an attachment field on a report) that would be interesting. I note that the paper clip and number appear in some and a little icon saying what 'type' of file it is word, excel, pdf etc... so I guess there must be some code that uses those 'hidden fields'.

Looks like all those buttons can be simulated by the two methods I mentioned earlier.

 

[Pat Hartman]

If you are going with storing the UNC, then the interface would be different unless you still want to show the images. If you do, you can use a bound image control. But, you need a child table since there is a 1-m relationship with images so the images need to be displayed either in a subform or a popup form.

Take a look at Northwind to see how they handle images. They use pictures of employees and pictures of products. However, the images are 1-1 so you won't see a subform or popup form.

 

[rdab100]

Looks like all those buttons can be simulated by the two methods I mentioned earlier.

Thanks.

If you are going with storing the UNC, then the interface would be different unless you still want to show the images. If you do, you can use a bound image control. But, you need a child table since there is a 1-m relationship with images so the images need to be displayed either in a subform or a popup form.

Take a look at Northwind to see how they handle images. They use pictures of employees and pictures of products. However, the images are 1-1 so you won't see a subform or popup form.

Looks like will just go with ONE image if file or files there.

 

[rdab100]

The code isn't rocket science... but I know how some like little snippets....

Private Sub Command199_Click()

'this counts the number of values of attachments and returns that number

Dim v        As Variant
Dim sOne     As Variant
Dim i        As Integer

i = 0

If IsNull(Attachments) Then

MsgBox "No Attachment on record"

End

End If

v = Split(Attachments, ";")

For Each sOne In v
  i = i + 1
     
Next
MsgBox i & " Attachments on record"

End Sub

The button being hidden/enabled or not depends on if the I is >0
Used the form_current event.

 

[rdab100]

The delimiter I used is ; you could choose something else but this is what SSMA used to separate attachment filenames. There is code out there to use multiple different or even multiple delimiters.

 

[Pat Hartman]

I'm not sure what buttons you are talking about. On my form, they are just navigation buttons so you can move from one image to another. They are the same as what you would see on a subform except they are smaller.

If you have only one image per record, there is no advantage at all in using the attachment data type. Just use a bound image control to show the single picture.

 

[rdab100]

I'm not sure what buttons you are talking about. On my form, they are just navigation buttons so you can move from one image to another. They are the same as what you would see on a subform except they are smaller.

If you have only one image per record, there is no advantage at all in using the attachment data type. Just use a bound image control to show the single picture.

Apologies... the button is just a simple count of how many exist, for now. 11K rows total. c490MB total.
Images might be easier but no these are pdf, word, excel and text document attachments. Versions going back 10+ years too so not just one extension either and if there might be mixed too. The data is present only in about 15% of the rows that have any at all (so most have none) of those that do, most have 2 some have up to 6. The button is really just a stub for now and a way of checking how many attachments.

 

[Pat Hartman]

I'm using a table with OLE objects rather than an attachment field. So, I'm using a subform to show multiple images. Here is what it looks like. This picture is part of the data entry form for a application that helps to evaluate investment properties.