Attachments

[Deleted member 146202]

Hi all,

Im taking over control of a database that has around 200 records and around 130 fields (a lot largely empty due to the main table not being well normalised at all, I'm just scared to change table structures as I don't know what I may lose... the database is used to pull a lot of management data)

I'm being asked by colleagues who don't know about access at all (I'm intermediate at most things but not up on some bits), if I could make an attachment field and display a picture of each of the ~200 products in the database. The pictures are between 300kb and 1500kb, but I could probably get the mall around 300-500 kb in needed.

Would this slow the database down a lot? I would guess it would (possibly making it unusable) but want to make sure.

I've also been asked about ANOTHER attachment field to hold a PDF for each product too.

 

[isladogs]

Attachment fields are possible but a BAD idea as they cause significant database bloat. This will slow down your database & it will rapidly grow to the Access limit of 2GB making your db unusable.

Instead add a test field and store the path to the images. You can then display the images from the file path in forms or reports

 

[mike60smart]

Hi

The attached is one way to handle attachments

View attachment HowToHandleAttachments_Demo.zip

 

[psyc0tic1]

Hi

The attached is one way to handle attachments

View attachment 71440

Is it possible to update this example so it will work on 64-bit systems?

I really want to use method #2 in my db but cannot as this is for 32-bit

 

[mike60smart]

Hi

That is the only example I have which someone from the UtterAccess forum supplied to me.

Sorry

 

[psyc0tic1]

I found something online that fixed it I think

I changed this:

Public Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
                                     (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, _
                                      ByVal lpParameters As String, ByVal lpDirectory As String, _
                                      ByVal nShowCmd As Long) As Long

To this:

Public Declare PtrSafe Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
                                     (ByVal hwnd As LongPtr, ByVal lpOperation As String, ByVal lpFile As String, _
                                      ByVal lpParameters As String, ByVal lpDirectory As String, _
                                      ByVal nShowCmd As LongPtr) As LongPtr

 

[arnelgp]

only the hwnd is LongPtr, the rest stays as Long.

 

[isladogs]

Not tested but I think you only need the first go the three LongPtr (the hwnd one)

Also, I suggest you use conditional compiling so it can be used in both 32-bit and 64-bit

EDIT Arnelgp got there first.

 

[psyc0tic1]

I will work on the conditional piling.

However I did notice that while for the most part this does what it says but using method #2 I manually upload some files to a folder I named "Attachments" in the directory where the back-end is stored... in the sample db I choose the files from that location and the db then creates a folder on my desktop of my local computer and puts copies of the files in there. Great... fine... that is what it says it is going to do... however the links it stores in the table are of the local folder rather than the server location folder.

Is that correct?

The reason I ask is because the instructions state you can write a line of code to delete the local files... but if that happens then the link is pointing to nothing.

 

[psyc0tic1]

I changed the code to this so it will work with both 32 & 64... this should work right?

#If VBA7 Then
Public Declare PtrSafe Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
                                     (ByVal hwnd As LongPtr, ByVal lpOperation As String, ByVal lpFile As String, _
                                      ByVal lpParameters As String, ByVal lpDirectory As String, _
                                      ByVal nShowCmd As Long) As Long
#Else
Public Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
                                     (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, _
                                      ByVal lpParameters As String, ByVal lpDirectory As String, _
                                      ByVal nShowCmd As Long) As Long
#End If

 

[Mark_]

Back to your original question, to restate
"Management wants to add attachementS to the database"

Big S to emphasize more than one.

You will want a child file with a pointer to the parent, Text for what the file is, text for path to file, and file type so you know how YOU want to handle it. Optionally you can add an "Order By" field so you can have them show up in the order your managers like.

For images, you would want a small piece of code to loop through them to display OR a child form in continuous mode that shows them as the user scrolls.

For PDFs, I'd suggest having a subform to show the descriptive name with a button to shell out to a PDF viewer to handle the PDFs themselves.

As soon as someone says "Lets see if we can add this" you often get "Add many of this". With image files/PDFs this can cause issues later when they want to add dozens of images for the same product, as has been pointed out. Unless they are looking inside the tables they should note no difference between you saving the image IN the table and you saving the path to the image.

 

[isladogs]

I changed the code to this so it will work with both 32 & 64... this should work right?

#If VBA7 Then
Public Declare PtrSafe Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
                                     (ByVal hwnd As LongPtr, ByVal lpOperation As String, ByVal lpFile As String, _
                                      ByVal lpParameters As String, ByVal lpDirectory As String, _
                                      ByVal nShowCmd As Long) As Long
#Else
Public Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
                                     (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, _
                                      ByVal lpParameters As String, ByVal lpDirectory As String, _
                                      ByVal nShowCmd As Long) As Long
#End If

Looks ok to me though I tend to use
#If Win64 Then

 

[psyc0tic1]

My apologies for hijacking the thread.