Attachment Data Types

[lpcrierie]

Hello all,



I have a MS Access database that currently holds file attachments (.pdf, .jpg, etc.) to forms within a table.
They are copies of the file attachments imported from a network folder. We have reached the 2GB size limit for Access databases.

I understand we can display hyperlinks both showing and pointing to the file path, which will open the original file, rather than store a copy.
My question is, is it possible to have access automatically create hyperlinks for file attachments which are already stored in the database?
If so, what code do I have to implement?
Or will I have to start from scratch and replace the attachment fields, then import them all again.
I ask this, because there are over 1,700 stored attachments total.

Thank you.

[ranman256]

If you store the path to the file in the field, you wont need a hyperlink if you use this code.
It will open ANY file in its native application.


Paste this code into a module, then its usage is: OpenNativeApp txtBox


if the item in the text box is a URL, it will open in default explorer

OpenNativeApp "c:\folder\file.doc"
will open the document in word


but it will work for all different types,

write code to scan your list of attached files, export them to a folder, return the path to your table field.

'whatever path is in the textbox will open in its native application
OpenNativeApp txtBox

#If Win64 Then      'Public Dclare PtrSafe Function
  Private Declare PtrSafe Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpszOp As String, ByVal lpszFile As String, ByVal lpszParams As String, ByVal lpszDir As String, ByVal FsShowCmd As Long) As Long
  Private Declare PtrSafe Function GetDesktopWindow Lib "user32" () As Long
#else
  Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpszOp As String, ByVal lpszFile As String, ByVal lpszParams As String, ByVal lpszDir As String, ByVal FsShowCmd As Long) As Long
  Private Declare Function GetDesktopWindow Lib "user32" () As Long
#End If




Const SW_SHOWNORMAL = 1
Const SE_ERR_FNF = 2&
Const SE_ERR_PNF = 3&
Const SE_ERR_ACCESSDENIED = 5&
Const SE_ERR_OOM = 8&
Const SE_ERR_DLLNOTFOUND = 32&
Const SE_ERR_SHARE = 26&
Const SE_ERR_ASSOCINCOMPLETE = 27&
Const SE_ERR_DDETIMEOUT = 28&
Const SE_ERR_DDEFAIL = 29&
Const SE_ERR_DDEBUSY = 30&
Const SE_ERR_NOASSOC = 31&
Const ERROR_BAD_FORMAT = 11&




Public Sub OpenNativeApp(ByVal psDocName As String)
Dim r As Long, msg As String


r = StartDoc(psDocName)
If r <= 32 Then
    'There was an error
    Select Case r
        Case SE_ERR_FNF
            msg = "File not found"
        Case SE_ERR_PNF
            msg = "Path not found"
        Case SE_ERR_ACCESSDENIED
            msg = "Access denied"
        Case SE_ERR_OOM
            msg = "Out of memory"
        Case SE_ERR_DLLNOTFOUND
            msg = "DLL not found"
        Case SE_ERR_SHARE
            msg = "A sharing violation occurred"
        Case SE_ERR_ASSOCINCOMPLETE
            msg = "Incomplete or invalid file association"
        Case SE_ERR_DDETIMEOUT
            msg = "DDE Time out"
        Case SE_ERR_DDEFAIL
            msg = "DDE transaction failed"
        Case SE_ERR_DDEBUSY
            msg = "DDE busy"
        Case SE_ERR_NOASSOC
            msg = "No association for file extension"
        Case ERROR_BAD_FORMAT
            msg = "Invalid EXE file or error in EXE image"
        Case Else
            msg = "Unknown error"
    End Select
'    MsgBox msg
End If
End Sub




Private Function StartDoc(psDocName As String) As Long
Dim Scr_hDC As Long


Scr_hDC = GetDesktopWindow()
StartDoc = ShellExecute(Scr_hDC, "Open", psDocName, "", "C:\", SW_SHOWNORMAL)
End Function

[lpcrierie]

Thank you very much for this.

However, I do not have any coding experience.
Do you also have code I can use for "write code to scan your list of attached files, export them to a folder, return the path to your table field." ?

Unfortunately, we currently only have code in a separate form for scanning a file directory and importing file attachments to the database.

[Gicu]

Lots of links with code examples, here are a couple. Give it a try and post back if you get stuck.
https://www.access-programmers.co.uk...olders.299345/
https://www.access-programmers.co.uk...folder.282135/
https://www.access-programmers.co.uk...folder.228146/
Here is my Google search:
https://www.google.com/search?q=acce...hrome&ie=UTF-8
Cheers,

[lpcrierie]

Actually, after rereading this step from ranman256, it seems to be an additional step provided under the assumption that we no longer have the original file attachments relating to the file attachments in the access database.
We do currently have the original files in a single folder. Because of this, I am currently assuming I no longer have to scan the files attached in the database to recover the originals.

I had created the module with the code he had provided, but I am not sure how to execute this code, as well as "return the path to your table field."

[Gicu]

The code you got from Ranman is needed to open the files once you populate a new field (short text) with the full path to the file (in your original folder). The same thing can be done using

Application.FollowHyperlink YourFieldWithFullFileNameVariable

https://learn.microsoft.com/en-us/of...ollowhyperlink

So you will still need to write a loop to populate the paths to the files in the original folder, but you do not need to extract them. Do you have some naming convention to identify in the original folder which file goes with which record? If yes then you might not even need the field as you could build it dynamically in VBA (for example if the files attached to a specific record contain the records unique ID in its name).

Cheers,

[lpcrierie]

Yes, we do have a specific naming convention for the files. A previous employee wrote the code for a bulk import form button, which is what was extracting and importing copies of the files into the database, and rejected files that did not follow the naming convention.
The files are matched to specific records. The files are pictures of certifications, and the records are employee numbers. Some records have multiple attachments associated with them as well. If I need to re-import all of the files again with the new loop for a file path, then so be it.
I can handle changing the current attachment fields to short text fields for the hyperlinks.

Though my old brain struggles with learning/writing code, unless I am simply adding things that are already there, like an additional field within a file name convention, for example.

[Gicu]

Some records have multiple attachments associated with them as well

Unfortunately I think your task just got a bit more complicated....In this case you will need to create a new table to store the multiple records and use a subform to display them. Can you show us a couple of files and the corresponding record so we could see how the convention works?

I have just helped someone on this forum recently with a similar situation and we came up with a combo (could be a list box) that gets refreshed in the Current event of the main form (your employee records I guess) to scan the file repository folder and populate a local table (in the front-end) with the files associated with the current record; the double-click event of the listbox would then open the files using the FollowHyperlink method.

Cheers,

[lpcrierie]

The first screenshot shows a highlighted record relating to the files and their naming convention in the second screenshot.
We have different certifications and a different field for each.
So in this example, this record has multiple attachments for multiple fields.
Thank you for your help thus far.

[Gicu]

Thanks for the screen-shot. The good think is that you probably don't need a lot of effort to repopulate the links and also to maintain them (deleting\renaming a target file in the network folder will not affect the stored path which will become broken). The bad thing is your form would need to be re-designed, I would suggest using a listbox to list all the files found in the (original) folder that match the employee ID (using a pattern in a recursive search using Dir or a file system object).
With this approach all you need to do is drop files in the folder and if the naming convention is strictly followed they will show up in the employee's record (you can extract the date created timestamp from the files so probably no need for the date fields you currently have.

Cheers,

[Amicron]

The Attachment data type is evil and should have never been added to Access. Get those files out of there!

[Gicu]

They are already out...

[Amicron]

Good man!

[Gicu]

@lpcrierie:
Please review the attached sample to see what I mean about dynamic viewing of the files associated with the employee records; the first listbox uses a value list as the record source so it is limited to the number of files it can list; the second is bound to a local temp table so can list any number of attachments. To try it on your system open the file, double click the textbox on the form header to enter your original folder path (no backslash at the end). Then add a couple ids from your employee table and you should see their records in the two listboxes.

Cheers,