Files in attachment field not found in Excel when sharing databse to multiple users.

[sabukaru]

Hi,

I have an Access database that records images to an attachment field. There are other fields, but these are all text. An Excel macro interfaces between the user and the database. Images can be added to the record and they are retrieved for viewing.
The Access database is shared to multiple users through SharePoint. All users use the same exact workbook and macros to access the database.

Here's the issue: I am only able to view images if I am doing so from the same computer which added the image to the Access database.

If User A adds an image to a record, User A is able to load the image to the Excel userform. But, if User B, using their ownb machine, tries to view the same image from the same record, they get a "File not found" error in Excel.

The "missing data" problem occurs only with the attachment field. All other fields are referenced correctly at all times. Also, I know Access is updating the image field correctly in Access. If I manually open the database, I will see the image.

I eblieve the problem is at the Access level. For example, if I open the Access table and manually delete the images and then, from User B's computer, update the records with the same images, now User B will be able to see the images, but User A will get the "File not found problem'.

Maybe this has something to do with Access's cache for images?

Here's the code I use to load the attachment data to Excel:

    Dim conn As Connection, rec As Recordset    Dim DBPATH, PRVD, connString, query As String
    
    Set conn = New Connection
    Set rec = New Recordset


    DBPATH = db_path.Value


    PRVD = "Microsoft.ace.OLEDB.12.0;"
    
    connString = "Provider=" & PRVD & "Data Source=" & DBPATH & "; Jet OLEDB:Database Password=" & p & ";"
    
    conn.Open connString
    
    query = "SELECT * FROM womhst WHERE wo_no = " & wo_no & ";"
    
    rec.Open query, conn
    
    iAtt.Picture = LoadPicture(rec.Fields(18))
    
    rec.Close
    conn.Close

I need this ASAP as I am presenting to my bosses tomorrow. I posted to Stack Overflow but have not received any responses, thus, I am also posting here. Here's the SO link: https://stackoverflow.com/questions/...-not-found-for

[Micron]

Not sure how quick you'll get a solution because most of us don't use attachment fields in Access - causes too much file bloat to say the least. We store the file path in a table and open it, usually using Application.FollowHyperlink or Shell. Sounds like you have the file location in the field, not the actual attachment, but I didn't think that was an option for an attachment field, again, not positive as I don't use them. You'd be better off with the other method.

[sabukaru]

I see. Thank you for your reply. I understand that this will cause filse size to increase dramatically. As an aside, my code in Excel limits the amount to one attachment. If user tries to add another, it will delete the previous one.

Either way, I did this method (using the attachment field) because I needed to share images with other users through sharepoint. If I save only the file path, in my limited knowledge, that would be relevant only to one user. That is, the file path would need to change for each user.

COuld you point me to some respurce where I can save only the filepath, as you point out, but being able to share the db over SharePoint? Thank you for your reply.

[NoellaG]

Isn't it easier to create a picture library directly in sharepoint? https://support.microsoft.com/en-gb/...1-4d3847ec21aa

[Micron]

I have virtually no experience with SP so sorry, can't be of much help there. When using only file path, you use a shared network folder and the users of the app (Excel, Access or whatever) need appropriate folder permissions. I can't say whether or not SP imposes any limits that would make that not possible.

[sabukaru]

I figured a work around. Save the image to a temp folder. VBA reads the image from there. Deletes the folder when finished. Not sure how smooth this is but at least it works as intended. Thank you all for your help.

[Welshgasman]

I figured a work around. Save the image to a temp folder. VBA reads the image from there. Deletes the folder when finished. Not sure how smooth this is but at least it works as intended. Thank you all for your help.

Why delete a folder each time, when you are likely going to need it for the next time?
Delete the file by all means, though overwriting should not cause any issues?