Attach a file to a record 1

[BeallDon]

using Tigerlili3's advice in thread702-1137528,

"You can have a field in the table which I'll call Attachment for this example. The data type needs to be OLE Object. On your form you need to have a bound object frame which I'll call tvwAttach for this example. You can have a button on the form that will call up the insert object dialog box by placing the following code in the on_click event:

CODE
tvwAttach.Action = acOLEInsertObjDlg

From there, the users can select to 'create from file' where they navigate to the file using the Browse button and then may choose whether or not to link to the file. It is best to link as long as the file will not change locations. This will save a lot of space in the database. In addition, any changes that are made to the files outside of the database will be reflected when they are viewed using the link inside the database. I also recommend using 'display as icon'. It just looks better. The file can be opened by double-clicking on the icon.",

I've been able to embed a pdf form in a record, allowing me to delete the original pdf from the folder it was stored in. I would like to fine-tune the form now:

- when I click the button to call up the insert object dialog box, I am always given a choice of creating a new object or create from file. I would like it to only give the option to create from file AND to always default to a specific directory.

 

[1DMF]

have a look here :

http://msdn.microsoft.com/en-us/library/office/ff836267(v=office.15).aspx

and here :

http://support.microsoft.com/kb/209990

Though I cannot find any info on how you set any defaults for the dialog box.

There is this .NET info :

http://msdn.microsoft.com/en-us/library/aa975201(v=vs.71).aspx

What have you tried?

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[MajP]

- when I click the button to call up the insert object dialog box, I am always given a choice of creating a new object or create from file. I would like it to only give the option to create from file AND to always default to a specific directory

I think you will have to roll your own controls and get rid of the standard OLE picker control. To do this first disable the bound object frame. If not they will still be able to double click in it. Add a button to Add and one to View.

Private Sub cmdAdd_Click()
  Dim strFile As String
  strFile = GetFile
  If Len(strFile) > 0 Then
    InsertDoc Me.ole, strFile
  End If
End Sub
Private Sub cmdShow_Click()
  Dim ctl As Access.BoundObjectFrame
  Set ctl = Me.ole
  If Not IsNull(Me.ole) Then
    ctl.Action = acOLEActivate
  Else
    MsgBox "OLE is empty"
  End If
End Sub

Public Function GetFile() As String
 ' This requires a reference to the Microsoft Office 11.0 Object Library.
  Dim fDialog As Office.FileDialog
  Dim strFile As String
   ' Set up the File dialog box.
   Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
   With fDialog
      .AllowMultiSelect = False
      .Title = "Select File"
      .Filters.Clear
     'Only allow certain file type
      .Filters.Add "PDF Files", "*.PDF"
     ' .Filters.Add "Word Documents", "*.DOCX"
     ' .Filters.Add "All Files", "*.*"
       
     'start in this folder
       .InitialFileName = "C:\"
      If .Show = True Then
         ' Loop through each file that is selected and then add it to the list box.
         strFile = fDialog.SelectedItems(1)
      End If
      GetFile = strFile
   End With
End Function

Public Function InsertDoc(ctl As Access.BoundObjectFrame, strDocPath As Variant) As String
  On Error GoTo Err_DisplayDoc
  Dim strResult As String
  Dim strDatabasePath As String
  Dim intSlashLocation As Integer
  Dim strImagePath
  With ctl
          ' Specify what kind of object can appear in the field.
          .OLETypeAllowed = acOLELinked
          .Class = "PDF"
          .SourceDoc = strDocPath
          .Enabled = True
          .Action = acOLECreateLink
          ' Optional size adjustment.
          .Enabled = False
          .SizeMode = acOLESizeZoom
          strResult = "Document found and displayed."
  End With
      
Exit_DisplayDoc:
      InsertDoc = strResult
      Exit Function
Err_DisplayDoc:
      Select Case Err.Number
          Case 2101       ' Can't find the doc.
              ctl.Visible = False
              strResult = "Can't find document."
              Resume Exit_DisplayDoc:
          Case 2455
              Resume Next
          Case Else       ' Some other error.
              MsgBox Err.Number & " " & Err.Description
              strResult = "An error occurred displaying document."
              Resume Exit_DisplayDoc:
      End Select
  End Function

 

[1DMF]

Nice one MajP

I was also going to suggest 'msoFileDialogFilePicker' instead. This at least gives the type of control required. I just wasn't sure if I had missed something in the docs with 'acOLEInsertObjDlg', I guess you are of the same opinion, this control doesn't appear to give you any API to auto manage the GUI.


"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[MajP]

I just wasn't sure if I had missed something in the docs with 'acOLEInsertObjDlg'

I do not think there is any documentation to miss. Not much written that I can find. The acOLEinsertObj is just a method to brings up the standard OLE object dialog and I do not see anyway to manipulate that object I do not see anyway to expose it. Same issue if using an attachment field instead, no way to control the attachment dialog. So if you want more control you have to roll your own. You can substitute the Office file dialog with the API file dialog (that code is available and has been posted here many times) for more portability.

BeallDon,
Since 2007 Access has become much more efficient in storing OLE objects. However, you are still limited to a 2G database. If you think you will end up with Gigs of documents then a better solution is to store in a common folder and use a link to display them, instead of in the db.

 

[1DMF]

BeallDon,
Since 2007 Access has become much more efficient in storing OLE objects. However, you are still limited to a 2G database. If you think you will end up with Gigs of documents then a better solution is to store in a common folder and use a link to display them, instead of in the db.

Agreed, it is still considered best practice to not store BLOBs in access table columns. If you do decide to store BLOBs, you need to ensure you run front-end-app / backed-data separation, especially if you need to roll out the DB to several users or patch / feature updates. The last thing you want to be doing is storing BLOBs in a single access ACCDB file that you have to keep distributing.

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[MajP]

In the Post 2007 world, if you decide to store files in an access DB the recommendation is to store it in an attachment field not an OLE field. "Bloat" is really no longer an issue for either field type (beyond the sized of the files), so I really do not know why it is preferred. The only thing I have seen written is (assuming not planning to upgrade to a Sql Server or other backend)

In most cases, you should use an Attachment field instead of an OLE Object field. OLE Object fields support fewer file types than Attachment fields support. In addition, OLE Object fields do not let you attach multiple files to a single record

The multiple attachments in a record is a result of a Multi Value Field (MVF), so it can be a benefit or a drawback depending on how you look at it.

Anyone know if there are other advantages of the attachment field over and OLE? (again I fully understand external storage for any large db is the best practice).

 

[1DMF]

The multiple attachments in a record is a result of a Multi Value Field (MVF), so it can be a benefit or a drawback depending on how you look at it.

Doesn't that breach 1NF?

Even if you moved to SQL, it also is now optimised for BLOBs, so migration of SVF shouldn't be an issue. I guess one advantage of storing BLOBs is it is more secure as the source file is not easily accessible.

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[MajP]

Even if you moved to SQL, it also is now optimised for BLOBs, so migration of SVF shouldn't be an issue.

I agree, that was my point too. An OLE would be more advantageous if you plan to move to SQL because that would be supported where attachments would not be supported (AFAIK). So my question was why MS recommends the attachment fields over an OLE field if you choose to store documents. It may be because SharePoint can handle an attachment field better. I know the MVFs were provided for SP compatibility.

Doesn't that breach 1NF?

I do not know if some of these terms will have meaning much longer. With things like object oriented dbs, NoSQL, data virtualization, etc. I do not think that MVFs violate 1NF (multiple values in one column), because how I understand it that data is really not in that column (or appears not to act that way). It acts as if it is in a child table, although behind the scenes. The value of an MVF is a recordset containing those values and some other fields. Not saying it is good or bad, just beyond traditional definitions of normalization.

 

[1DMF]

Yes, I see your point. I guess in the data modelling course I took, in abstract you were allowed to represent 'complex data types' via a single column such as 'Address'. If as you say MVF is simply a simple way to represent another relationship of 1-many, t technically is still what you would call 1NF. It's just not the traditional PK/FK type relationship to concrete relations.

It's not like it is using a string field to store CSV or pipe delimited content to represent multiple values.

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music