Can I link photos stored on Office 365 (OneDrive, etc.) with an Access Database? (1 Viewer)

Plaxerous

New member
Local time
Today, 02:00
Joined
Apr 10, 2018
Messages
4
We have a database that generates reports with pictures that are pulled from a network drive. However, the pictures are not available if the user becomes disconnected from the network drive when working from home or traveling.

We recently integrated with Office 365 so is there anyway we could store the photos on SharePoint or OneDrive, and have the database pull from there? The easiest way from the user perspective would be to store the photos locally on their computer, and create a shareable link which can be copy/pasted into the database, but shareable links do not seem to be .jpg or image files.

Any advice is appreciated! Thanks!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:00
Joined
Oct 29, 2018
Messages
21,357
Hi. Welcome to AWF! Using SharePoint, maybe. Using OneDrive, probably not. Just my 2 cents...
 

sonic8

AWF VIP
Local time
Today, 10:00
Joined
Oct 27, 2015
Messages
998
We recently integrated with Office 365 so is there anyway we could store the photos on SharePoint or OneDrive, and have the database pull from there?
Access cannot display images from an HTTP address directly. You need to download the images to a local (temp-)folder first and then use the local path to the image in the report or form.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:00
Joined
Feb 19, 2002
Messages
42,970
I haven't tried it but if you can "see" OneDrive files via file manager, you should be able to "see" them from Access and therefore open them. But since I haven't tried it, sonicB might be correct. so just try it.
 

AccessBlaster

Registered User.
Local time
Today, 02:00
Joined
May 22, 2010
Messages
5,823
We have a database that generates reports with pictures that are pulled from a network drive. However, the pictures are not available if the user becomes disconnected from the network drive when working from home or traveling.

We recently integrated with Office 365 so is there anyway we could store the photos on SharePoint or OneDrive, and have the database pull from there? The easiest way from the user perspective would be to store the photos locally on their computer, and create a shareable link which can be copy/pasted into the database, but shareable links do not seem to be .jpg or image files.

Any advice is appreciated! Thanks!
If your user is disconnected from the network how would you sign onto sharepoint? I doubt one drive or google drive would be easy to browse from access.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:00
Joined
Feb 28, 2001
Messages
26,996
OK, the question is "What protocol is being used?"

Whole file transfers can be managed by Server Message Block (SMB), File Transfer Protocol (FTP), and a couple of others, plus the secure versions thereof. I know you can also more or less reliably transfer a file as an attachment via SMTP and its variants.

If you can map the remote storage drive to a letter and then browse in a standard "Explorer" window, you are (probably) using SMB. In that case, there is a chance it could work. If you cannot map a drive then you almost certainly cannot do this.

There is a VERY serious (i.e. damned near FATAL) issue in using remotely linked back ends. Trying to get to something over an unsteady link or a non-SMB link will fail miserably and catastrophically for embedded (OLE) files and database .xDB or .xDE files because SMB just cannot handle bad network connectivity. However, I am not sure that the same error condition applies when talking about hyperlinked files. In that case, I don't believe that a database buffer is involved in a way that could lead to corruption if the network link breaks. You still would need robust error handling because you WILL occasionally get errors that could crash your app if you don't handle them correctly.
 

isladogs

MVP / VIP
Local time
Today, 09:00
Joined
Jan 14, 2017
Messages
18,186
If your user is disconnected from the network how would you sign onto sharepoint? I doubt one drive or google drive would be easy to browse from access.

Just for confirmation...Access can definitely browse files that are in OneDrive or Dropbox.
I don't have a Google Drive but I expect that would work as well if it appears in Explorer as both of the others do.

 

Attachments

  • Capture.PNG
    Capture.PNG
    63.8 KB · Views: 832

isladogs

MVP / VIP
Local time
Today, 09:00
Joined
Jan 14, 2017
Messages
18,186
Access cannot display images from an HTTP address directly. You need to download the images to a local (temp-)folder first and then use the local path to the image in the report or form.

Hi Phillip
Actually that's not quite true
Whilst I often download images to a local folder before displaying, you can display images direct from their URL if you use the ActiveX Microsoft WebBrowser control.
For example, the screenshot below shows a form with the image I uploaded in my previous post:



I use this approach in various apps including embedding a 'live' route planner on an Access form:

 

Attachments

  • WebImageAccessForm.jpg
    WebImageAccessForm.jpg
    94.3 KB · Views: 779
  • WebImageAccessForm2.PNG
    WebImageAccessForm2.PNG
    67.2 KB · Views: 824

Micron

AWF VIP
Local time
Today, 05:00
Joined
Oct 20, 2018
Messages
3,476
On a home pc, One Drive is a subfolder of the user profile. An image control will display an image with a path like C:\Users\Micron\OneDrive\Pictures...
Not sure where that leaves anyone in a corporate setting if One Drive is shared (which is an option) but if we're talking each person getting at their own images, then maybe possible. Agree though that it's simple enough to try rather than keep wondering if it's possible.
 

AccessBlaster

Registered User.
Local time
Today, 02:00
Joined
May 22, 2010
Messages
5,823
All of these external apps are password protected, its just another road block to overcome for each user. But I guess its easy if you know how.
 

sonic8

AWF VIP
Local time
Today, 10:00
Joined
Oct 27, 2015
Messages
998
Actually that's not quite true
Whilst I often download images to a local folder before displaying, you can display images direct from their URL if you use the ActiveX Microsoft WebBrowser control.
Well, whether it's true or not depends on how we interpret directly in my statement. - Sure, you can use the WebBrowser control to display arbitrary content from an HTTP URL. But...
While the WebBrowser control has been tigthly integrated into Access in recent years, I would still not consider it a fully native Access control.

Be that as it may, the original question was about displaying pictures in a report. - I had no luck so far using a WebBrowser control in a report, despite needing that functionality desperately. - If you show me how to do that, I'll buy you a beer. Actually, as much beer as you can (and want to) drink in a day.


isladogs said:
Just for confirmation...Access can definitely browse files that are in OneDrive or Dropbox.
That is actually a key factor I missed. It's probably the best solution to the question.
You can set up a document library on Sharepoint and sync the documents (pictures) to the local computes using OneDrive (for Business). Then you just link to the local files from the report.
 

isladogs

MVP / VIP
Local time
Today, 09:00
Joined
Jan 14, 2017
Messages
18,186
The Web browser is just about the only ActiveX control I ever use and it does work well on forms.

Thank you for your apparently generous offer but if I could get it to work, it would be worth free beer for a whole year...:D
I actually tried this a couple of years ago and like you I failed. In fact I posted a question about it at Tek-Tips where I found code that looked promising..
https://www.tek-tips.com/viewthread.cfm?qid=1779787
In the end I decided it was impossible and used a different method where I downloaded the Web image as a local file then used a standard image control.
The process is so fast there is no noticeable delay.

As for your other point, I've never had a reason to use SharePoint and somehow doubt I'll be trying it in the future
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:00
Joined
Feb 28, 2001
Messages
26,996
I have no direct experience in this, but an important point can be made regarding Colin's post #12 in which he first copies the image and then displays it.

To me, the underlying issue is what protocols are used to download the image for display. Access really wants to use SMB for things that will become part of its displayed area, whether on forms or reports.

Using an embedded ActiveX control is (apparently) still difficult, which is not surprising given how ActiveX controls have been the cause for so many headaches over the years. I can't recall how long ago that admonitions came out of MS HQ about ActiveX usage and how they were being downplayed.

Colin's suggestion that the process works better in two steps than in one makes sense in that using some COPY command, even within the WebBrowser conntrol, allows a simple file transfer using whatever file transfer protocol is convenient within said control (or using external methods!) Once the file is local, the display problem becomes trivial.

This is an example of "divide and conquer" which is an excellent strategy for solving knotty problems. If you can't solve the problem as-is, CHANGE it. Make it a soluble problem piecemeal if you can't do it wholesale.

I know this is an aside, but it was intended for a general audience. Colin, I know YOU understand because you and MajP have had exchanges regarding the intended audience recently.
 

sonic8

AWF VIP
Local time
Today, 10:00
Joined
Oct 27, 2015
Messages
998
Thank you for your apparently generous offer but if I could get it to work, it would be worth free beer for a whole year...:D
Well, obviously I would need to establish your drinking capacity first, but this might be still within budget. ;-)


In the end I decided it was impossible and used a different method where I downloaded the Web image as a local file then used a standard image control.
The process is so fast there is no noticeable delay.
Unfortunately, my requirement is not about displaying images but about displaying HTML that is beyond the capabilities of the standard Access Rich-/HTML-Textbox



As for your other point, I've never had a reason to use SharePoint and somehow doubt I'll be trying it in the future
I wasn't suggesting you personally use Sharepoint, but rather the person asking the question and mentioning Sharepoint as a tool at her disposal.
Even though you can share OneDrive files and folders with other persons, it is rather a personal data store. Sharepoint, in contrast, is intended for teams/groups. So, it is probably the better choice for a whole department or company sharing the images used in the Access application.
 

isladogs

MVP / VIP
Local time
Today, 09:00
Joined
Jan 14, 2017
Messages
18,186
Unfortunately, my requirement is not about displaying images but about displaying HTML that is beyond the capabilities of the standard Access Rich-/HTML-Textbox

Can you supply an example just in case I can think of a possible solution?

I wasn't suggesting you personally use Sharepoint, but rather the person asking the question and mentioning Sharepoint as a tool at her disposal.
Even though you can share OneDrive files and folders with other persons, it is rather a personal data store. Sharepoint, in contrast, is intended for teams/groups. So, it is probably the better choice for a whole department or company sharing the images used in the Access application.

Sorry. My response to that point was unintentionally rather brusque.
 

Dreamweaver

Well-known member
Local time
Today, 09:00
Joined
Nov 28, 2005
Messages
2,466
I do this all the time I have my image so it is stored on one drive and a link on my system which saves space as I have 150k+ images when one is used one drive downloads it which I have has no problems with.


I use an image object to display the image as below


Code:
On Error GoTo HandleErr

If Not IsNull(Me![Picture]) Then
        Me.Imgage1.Picture = Me![FoldersLoc] & Me![Picture]
        Me![CmdRemove1].Enabled = True
    End If
    
HandleExit:
    Exit Sub
    
HandleErr:
    Select Case Err.Number
        Case 2220 'Related Records Found
            MsgBox "Cannot find image", vbCritical, "Sorry Item Required"
            Exit Sub
        Case 2501 'Cancel = True
            Exit Sub
        Case Else
            MsgBox Err.Number & vbCrLf & Err.Description
            Resume HandleExit
        Resume
    End Select


Hope it helps mick
 

Plaxerous

New member
Local time
Today, 02:00
Joined
Apr 10, 2018
Messages
4
Sorry for late reply - this has been my solution:

Our corporate OneDrive stores shareable links as a webpage URL instead of a .jpg/.png URL, so no luck here without automation via CreateObject InternetExplorer :banghead:

However, retrieving images using SharePoint works via URLDownloadToFile Function (https:// github.com/GustavBrock/VBA.PictureUrl/blob/master/Internet.bas). Space added to URL due to my low rep.

There is one caveat for me though, which is that my organization uses an OTP token for SharePoint authentication so I have to open a hidden Internet Explorer window to login first before retrieving image files.

Hope this helps!
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:00
Joined
Feb 28, 2001
Messages
26,996
Sounds to me like a judicious use of "divide and conquer." Download then display as two steps. Don't forget to dispose of the file if you are done with it and need the space.
 

Users who are viewing this thread

Top Bottom