How to insert a file system path/filename exclusively with ACCESS macro / expressions / built-in functions (no VBA) RRS feed

  • Question

  • Hi all - would like to figure out how to insert a path/file string in a table using only macros \ expressions \ built-in functions.

    The Hyperlink datatype provides the 'click to open file' function i need, but i'm looking for some mechanism to initially locate and insert the path/file string.

    The attachment field widget 'file lookup' would be a best-case comparison, but since I don't want to store the file in the database, the attachment field is not ideal.

    many tia  for any enlightening comment !

    Friday, March 31, 2017 12:40 AM

All replies

  • Hi,

    Unless I just can't think of it at the moment, I am not aware of any built-in "file browser" function in Access. If so, you may have no choice but to use VBA.

    Even if we use the built-in file browser for an OLE or Attachment field, I think you'll still need to use VBA to "extract" the file path from it to store in a field rather than the actual file.

    Just my 2 cents...

    Friday, March 31, 2017 1:51 AM
  • Hello,

    We could use CreateRecord Data Block or EditRecord Data Block to insert or update a field and insert a string. The CreateRecord data block can only be used in the After Insert, After Update, and After Update data macro events. For more information about Access built-in macro actions, please visit Access Macro Actions (Access Developer Reference)

    The following table shows the values returned by the HyperlinkPart method for data stored in a Hyperlink field.

    Hyperlink field data

    HyperlinkPart method returned values


    acDisplayedValue: http://www.microsoft.com  acDisplayText: acAddress: http://www.microsoft.com acSubAddress: acScreenTip: acFullAddress: http://www.microsoft.com


    acDisplayedValue: Microsoft  acDisplayText: Microsoft acAddress: http://www.microsoft.com acSubAddress: acScreenTip: acFullAddress: http://www.microsoft.com

    Customers#http://www.microsoft.com#Form Customers

    acDisplayedValue: Customers  acDisplayText: Customers acAddress: http://www.microsoft.com acSubAddress: Form Customers acScreenTip: acFullAddress: http://www.microsoft.com#Form Customer

    ##Form Customers#Enter Information

    acDisplayedValue: Form Customers  acDisplayText: acAddress: acSubAddress: Form Customers acScreenTip: Enter Information acFullAddress: #FormCustomer



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, April 3, 2017 4:14 AM