Solved Open an attachment (1 Viewer)

Kamolga

New member
Local time
Today, 19:39
Joined
Aug 18, 2020
Messages
11
Hi,

I have a table with an attchment and made a form to visualise it. Now I would like to open it as if I double clicked it and pressed open but with a macro on the click event? Any idea how to do that? (It is a Microsoft PowerBI desktop file)
1597760903221.png
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:39
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to AWF!

Not sure if this is the best approach, but one way is to save the attachment to the disk and then open it from there.
 

Micron

AWF VIP
Local time
Today, 14:39
Joined
Oct 20, 2018
Messages
3,476
Save, or open an attachment that's in a table? I think the latter?
See if this helps (specifically the Display Functionality). You might decide it's easier to just go with the Access dialog.
 

Kamolga

New member
Local time
Today, 19:39
Joined
Aug 18, 2020
Messages
11
In the database a devlopper stored an excel as an attachment in a single record table. It is being used to export data for the user and what is perfect is that the user can mess with the exported file, the next user who will want to export will have something working from the orginial "template". My issue if I give a link to a stored pbix is that there is no way to make it read-only (if somebody delete something, it is not working for the next one) so I would prefer the user to open a stored template by clicking a button if possible. My issue with the dialog, user can delete it, save another one, etc....and it is quite heavy to click, to open a window to select a file and click open.
 

Isaac

Lifelong Learner
Local time
Today, 11:39
Joined
Mar 14, 2017
Messages
8,738
Then you probably want to look into saving it to disk, then opening it from there (a copy)
 

Kamolga

New member
Local time
Today, 19:39
Joined
Aug 18, 2020
Messages
11
Disk solution is bad in my case, I would need to overwrite the copy that has to be accessible to an entire corporation with a master each time someone uses it (don't know how to that either)...and worst of all it can't be used by multiple people at the same time. Isn't there a way to easily open an attachment? If we ask a user to attach a contract or an invoice, can't we get other users to just open them by clicking a button without giving them the opportunity to modify, delete or overwrite the attachment?
 

Isaac

Lifelong Learner
Local time
Today, 11:39
Joined
Mar 14, 2017
Messages
8,738
Disk solution is bad in my case, I would need to overwrite the copy that has to be accessible to an entire corporation with a master each time someone uses it (don't know how to that either)...and worst of all it can't be used by multiple people at the same time. Isn't there a way to easily open an attachment? If we ask a user to attach a contract or an invoice, can't we get other users to just open them by clicking a button without giving them the opportunity to modify, delete or overwrite the attachment?
I guess I'm confused. I thought what you WANTED was to have them mess with a copy, not the original.
I'm pretty sure you won't be successful having "multiple people" editing an Access table attachment at the same time ... (why would you want that?)
 

Kamolga

New member
Local time
Today, 19:39
Joined
Aug 18, 2020
Messages
11
The idea is to have a master file in the database (a report). When a user click a button, he opens the file but he can't save it back in the database...therefore the original file is still in the database, user has a temporary file that he can save on his hard disk if he wants to. When next user opens the file from the database on his computer, same thing, they have no way to overwrite the attachment (especially if they got the file through a button). As an example this how to open an excel an excel file stored in an attachment and whatever the user do then with the file in excel, it won't affect next one
Code:
Dim strFileName                 As String
    Dim xlApp                       As Object
    Dim xlWkBook                    As Object
    Dim xlWkSheet                   As Object
    Dim strID                       As String
    Dim qry                         As DAO.QueryDef
    Dim rec                         As DAO.Recordset
    Dim fField                      As DAO.Field
    Dim j                           As Long
    
    strID = Nz(Me!pr_ID, -1)
    If strID = -1 Then Exit Sub
    
    strFileName = Environ("USERPROFILE") & "\" & Format(Now, "yymmddhhnnss") & " - Planning.xlsm"
    'MsgBox strFileName & vbCrLf & ExistFile(Environ("USERPROFILE") & "\", , , False)
    If bSavePJ(strFileName, "1") Then
            
        Set xlApp = Nothing
        On Error Resume Next
        Set xlApp = GetObject(, "Excel.Application")
        On Error GoTo 0
        
        If xlApp Is Nothing Then Set xlApp = CreateObject("Excel.Application")
        
        If xlApp Is Nothing = False Then
        
            xlApp.Visible = True
            
            Set xlWkBook = xlApp.workbooks.Open(strFileName)
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:39
Joined
Oct 29, 2018
Messages
21,358
The idea is to have a master file in the database (a report). When a user click a button, he opens the file but he can't save it back in the database...therefore the original file is still in the database, user has a temporary file that he can save on his hard disk if he wants to. When next user opens the file from the database on his computer, same thing, they have no way to overwrite the attachment (especially if they got the file through a button). As an example this how to open an excel an excel file stored in an attachment and whatever the user do then with the file in excel, it won't affect next one
Code:
Dim strFileName                 As String
    Dim xlApp                       As Object
    Dim xlWkBook                    As Object
    Dim xlWkSheet                   As Object
    Dim strID                       As String
    Dim qry                         As DAO.QueryDef
    Dim rec                         As DAO.Recordset
    Dim fField                      As DAO.Field
    Dim j                           As Long
   
    strID = Nz(Me!pr_ID, -1)
    If strID = -1 Then Exit Sub
   
    strFileName = Environ("USERPROFILE") & "\" & Format(Now, "yymmddhhnnss") & " - Planning.xlsm"
    'MsgBox strFileName & vbCrLf & ExistFile(Environ("USERPROFILE") & "\", , , False)
    If bSavePJ(strFileName, "1") Then
           
        Set xlApp = Nothing
        On Error Resume Next
        Set xlApp = GetObject(, "Excel.Application")
        On Error GoTo 0
       
        If xlApp Is Nothing Then Set xlApp = CreateObject("Excel.Application")
       
        If xlApp Is Nothing = False Then
       
            xlApp.Visible = True
           
            Set xlWkBook = xlApp.workbooks.Open(strFileName)
Hi. I could be off, but I thought that's what I suggested earlier. The user clicks a button, you save a copy of the attachment on their computer and then open it. That keeps the original attachment untouched. No?
 

Kamolga

New member
Local time
Today, 19:39
Joined
Aug 18, 2020
Messages
11
Hi. I could be off, but I thought that's what I suggested earlier. The user clicks a button, you save a copy of the attachment on their computer and then open it. That keeps the original attachment untouched. No?
Sorry I did not understand "programatically"...I thought you meant I had to take the file and copy the file on their laptops. So if I have an attachment in table 'T_Reporting' as first and only record called 'Report.pbix', how can I get the user an dialogbox to save it where he wants?
 

Isaac

Lifelong Learner
Local time
Today, 11:39
Joined
Mar 14, 2017
Messages
8,738
The idea is to have a master file in the database (a report). When a user click a button, he opens the file but he can't save it back in the database...therefore the original file is still in the database, user has a temporary file that he can save on his hard disk if he wants to. When next user opens the file from the database on his computer, same thing, they have no way to overwrite the attachment (especially if they got the file through a button).
Alright...then it's like I already said ... Save a copy to disk, and open the copy for them...
 

Kamolga

New member
Local time
Today, 19:39
Joined
Aug 18, 2020
Messages
11
I try but I can't get the dialog box to open for a user nor the file to be saved if I try to save it on my disk. Can you spot anything wrong? (Note that there is only one record, I loop only because I don't know how to go to the first record)
Code:
Private Sub BI_Attachment_Click()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset2
Dim rsA As DAO.Recordset2
Dim fld As DAO.Field2
Dim strFullPath As String
'Dim fpath As String
        'Get the database, recordset, and attachment field
        Set dbs = CurrentDb
        Set rst = dbs.OpenRecordset("T_Reporting")
        Set fld = rst("BI_Attachment")
       
        'Get the recordset for the Attachments field
         Set rsA = fld.Value
       
        'Navigate through the table
        Do While Not rst.EOF
       
'Let user chose path

fpath = Application.FileDialog(msoFileDialogFolderPicker)
         
  strFullPath = fpath & "\Reporting PMO.pbix"

'Save file
rsA("Reporting PMO.pbix").SaveToFile strFullPath
Loop
'Open the file

End Sub
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:39
Joined
Oct 29, 2018
Messages
21,358
Sorry I did not understand "programatically"...I thought you meant I had to take the file and copy the file on their laptops. So if I have an attachment in table 'T_Reporting' as first and only record called 'Report.pbix', how can I get the user an dialogbox to save it where he wants?
Hi. You can use the FileDialog object for that. However, I wonder why you would care where the user wants to store the file, or even let them. If you were to allow users to double click on the attachment to open it normally, users won't need to select a location where to save it (hint, Access saves it in a default location). So, you can also give them the same experience. When they click on your button, simply save the file "somewhere," they don't need to know where it is. In fact, you may have to delete it anyway afterwards to avoid any potential copy conflicts.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:39
Joined
May 7, 2009
Messages
19,169
Code:
Private Sub BI_Attachment_Click()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset2
    Dim rsA As DAO.Recordset2
    Dim fld As DAO.Field2
    Dim strFullPath As String
    Dim fpath As String
    Dim bolOk As Boolean
    fpath = Application.FileDialog(msoFileDialogFolderPicker)
    bolOk = Len(fpath) > 0
    Do Until bolOk
        If MsgBox("You did not select any folder. Try again?", vbQuestion + vbYesNo) = vbYes Then
            fpath = Application.FileDialog(msoFileDialogFolderPicker)
            bolOk = Len(fpath) > 0
        Else
            Exit Do
        End If
    Loop
    If bolOk Then
        'Dim fpath As String
        'Get the database, recordset, and attachment field
        Set dbs = CurrentDb
        Set rst = dbs.OpenRecordset("T_Reporting")
        Set fld = rst("BI_Attachment")
     
        'Get the recordset for the Attachments field
        Set rsA = fld.Value
     
        strFullPath = fpath & "\Reporting PMO.pbix"

        'Save file
        rsA("FileData").SaveToFile strFullPath
        'housekeeping
        Set rsA = Nothing
        rst.Close
        Set rst = Nothing
        Set dbs = Nothing

        'open file
        Application.FollowHyperlink strFullpath
    End If
End Sub
 

Micron

AWF VIP
Local time
Today, 14:39
Joined
Oct 20, 2018
Messages
3,476
can't we get other users to just open them by clicking a button without giving them the opportunity to modify, delete or overwrite the attachment?
Well, I for one thought the idea was to store a "master" in the table and force users to save any changes to it wherever they wanted if they chose to do so. This means no file dialog code is really needed when you have the Save As feature in the native application. I figured the link I provided would be OK for that but I guess not - and only 19 lines of code.
 

vhung

Member
Local time
Today, 11:39
Joined
Jul 8, 2020
Messages
235
Hi,

I have a table with an attchment and made a form to visualise it. Now I would like to open it as if I double clicked it and pressed open but with a macro on the click event? Any idea how to do that? (It is a Microsoft PowerBI desktop file) View attachment 84342
Hi. Welcome to AWF!

Not sure if this is the best approach, but one way is to save the attachment to the disk and then open it from there.
Hi,

I have a table with an attchment and made a form to visualise it. Now I would like to open it as if I double clicked it and pressed open but with a macro on the click event? Any idea how to do that? (It is a Microsoft PowerBI desktop file) View attachment 84342
hey;
enjoy using attachment
>you've mention that it is open file for reading
but you attached file should not be edited or deleted
>hence: lock the field of your attachement on your form
>if you click/double either to open an excel file they can type anything or delete while it is open but on close can't be save on your attachment
>they can only save the edited file if they the SaveAs for rename a copy on their Disk
>see my attached files
 

Attachments

  • Attachment1.png
    Attachment1.png
    51.9 KB · Views: 127
  • Attachment2.png
    Attachment2.png
    85.2 KB · Views: 124

theDBguy

I’m here to help
Staff member
Local time
Today, 11:39
Joined
Oct 29, 2018
Messages
21,358
hey;
enjoy using attachment
>you've mention that it is open file for reading
but you attached file should not be edited or deleted
>hence: lock the field of your attachement on your form
>if you click/double either to open an excel file they can type anything or delete while it is open but on close can't be save on your attachment
>they can only save the edited file if they the SaveAs for rename a copy on their Disk
>see my attached files
Hi. That's a nice approach. But if the Attachment Control is locked, how did you save the attachment in the first place?
 

Kamolga

New member
Local time
Today, 19:39
Joined
Aug 18, 2020
Messages
11
Hi,

Thanks to all for your help. I opted for the save a copy as where the user wanted, that allows him to set filters for his department in the file without having to go through Access and having to set them back every time.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:39
Joined
Oct 29, 2018
Messages
21,358
Hi,

Thanks to all for your help. I opted for the save a copy as where the user wanted, that allows him to set filters for his department in the file without having to go through Access and having to set them back every time.
Hi. Glad to hear you got it sorted out. Good luck with your project.
 

vhung

Member
Local time
Today, 11:39
Joined
Jul 8, 2020
Messages
235
Hi. That's a nice approach. But if the Attachment Control is locked, how did you save the attachment in the first place?
hello;
>you can add any option as Admin control/pass to edit your file and save
>there is a lot of ways to connect using Timer Interval just to unlocked your file if you have to save for you only
>if for viewer that is absolutely locked but open for viewing... it always works...
 

Users who are viewing this thread

Top Bottom