Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Star Lounger
    Join Date
    Jul 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    57
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Recreate links to image files (Access 2000 SR1)

    I have an application that links several photos (jpg files) to a particular insurance policy.
    The photos are stored on a server in folders that are named for the policy # they link to. The photos for each policy are loaded, using a form, by reading the files in the folder and creating a record for each photo in the photos table.

    The Photos table fields are as follows:

    OLEID (AutoNumber)
    OLEPath
    OLEFile
    PolicyNumber

    My client has decided to add another server and needs to move the photos from the existing server to the new server. I thought of changing the path in the OLEPath field but this alone does not do the job. I believe the OLEFile link has to be recreated as well, but don

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Recreate links to image files (Access 2000 SR1)

    It isn't clear to me whether your links are being stored as text fields or OLE objects. The former is fairly easy - you just change the photo path to the new server and all should be fine. If it'st the latter, that's a fair bit more complex, and could well involve writing some code to relink. I would caution you however that the latter will cause your database to bloat very quickly, while the former with just a full path to the phote will be pretty modest in it's growth.
    Wendell

  3. #3
    Star Lounger
    Join Date
    Jul 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    57
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Recreate links to image files (Access 2000 SR1)

    Sorry, I should have been clearer. The links are stored as OLE objects.

    This client wanted the users to be able to display the photos using another application (they are displaying the jpg

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Recreate links to image files (Access 2000 SR1)

    As Wendell noted, the easiest and most efficient way to handle pictures in a table is to store their file names in a text field. You can use code to display the picture on a form, or in a separate application, or whatever you like. For instance, to open a picture in the associated application, you can use code like this:

    Dim lngResult As Long

    lngResult = ShellExecute(hWndAccessApp, "Open", _
    MyFolder & "" & MyFile, 0&, 0&, SW_SHOWMAXIMIZED)
    If lngResult <= 32 Then
    MsgBox "Can't open document.", vbExclamation
    End If

    ShellExecute is a Windows API call that should be declared in a standard module:

    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

    Public Const SW_SHOWNORMAL = 1
    Public Const SW_SHOWMAXIMIZED = 3

    Using ShellExecute with the "Open" argument is the equivalent of opening a file from Windows Explorer.

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Recreate links to image files (Access 2000 SR1)

    Hans has made an excellent suggestion as to how to open the photo in a separate application without storing the photo as a linked OLE object. Doing it the way you are doing will require editing each OLE object when the photos are moved, and I frankly don't have a clue about how that could be done in code. I don't know how many photos you are dealing with, but it can be done manually. The other approach would require a simple update query to change the path, but I don't think that would work with OLE objects.

    The bigger issue with OLE objects is the bloat you get. It appears that Access stores a bunch of image conversion/display with each object, so databases grow about 1MB for each record containing an OLE object (linked or embedded), even though the jpeg may only be a few KB. So if you get up around 1000 to 2000 records, you approach the maximum size of an Access database. Then you run into problems with doing compact and repair and other fun issues. You could always go to a SQL Server back-end (or some other more robust database engine) and resolve the problem that way, but it will likely be an issue for you if you proceed with Jet and OLE objects.
    Wendell

  6. #6
    Star Lounger
    Join Date
    Jul 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    57
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Recreate links to image files (Access 2000 SR1)

    Thank you for help. I was just able to get back to working on this today and incorporated your code into my application. I'm not sure what's wrong but instead of the photo opening when I click on the photo file name in the listbox, a Windows Explorer type screen pops up listing the contents of my c: drive. When I click on a .jpg file in Windows Explorer it opens using Internet Explorer. Did I miss something?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Recreate links to image files (Access 2000 SR1)

    It looks like the path+filename isn't passed correctly to ShellExecute, just the drive or path. If .jpg is associated with Internet Explorer, ShellExecute should use IE to open a .jpg, but if just the path is passed, the application associated to drives/folders is used - Windows Explorer. Try to find out the exact argument, for instance by inserting a MsgBox statement:

    Dim lngResult As Long
    Dim strFile As String

    strFile = MyFolder & "" & MyFile ' use whatever you do do construct the filename instead of this
    MsgBox strFile
    lngResult = ShellExecute(hWndAccessApp, "Open", _
    strFile, 0&, 0&, SW_SHOWMAXIMIZED)
    If lngResult <= 32 Then
    MsgBox "Can't open document.", vbExclamation
    End If

  8. #8
    Star Lounger
    Join Date
    Jul 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    57
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Recreate links to image files (Access 2000 SR1)

    Thank you so much Hans. It's working fine. I hope I'm not pushing my luck but I'd like to ask you one more thing. My users are using two monitors on each PC. They want to display the Access Application on one screen and when the photos open in Internet Explorer, they want them to open on the second screen so they can easily compare the data to the photo. Currently the photos are opening on the first screen over top of the Access app. In the original app where the photo was loaded as a linked object, Internet Explorer would display on the second screen. Is there a way to force the photos/Internet Explorer to open on the second screen?

    Sue

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Recreate links to image files (Access 2000 SR1)

    That's at least somewhat dependent on the video card(s) and the drivers being used. Internet Explorer always seems to want to open on monitor 1 in my configuration, so what I do is either move it to monitor 2, or often I open the app I'm working with and move it to monitor 2 and then let IE do it's own thing. (I'm using a Matrox dual-head video card.)
    Wendell

  10. #10
    Star Lounger
    Join Date
    Jul 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    57
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Recreate links to image files (Access 2000 SR1)

    I tried moving the app to monitor 2 as well but no matter where the app is, IE opens on top of it. (I'm using a Matrox dual-head video card as well.) Oh well, I guess that's something they'll have to live with. Thanks for all your help.

    Best Regards,
    Sue

  11. #11
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Recreate links to image files (Access 2000 SR1)

    Hi Wendell.
    2 monitors <img src=/S/confused.gif border=0 alt=confused width=15 height=20>, do you mean 2 physical screens on a PC? That would be useful for kids and the Internet.
    Am I right in assuming the above?
    What was the cost of the video card?
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  12. #12
    Star Lounger
    Join Date
    Jul 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    57
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Recreate links to image files (Access 2000 SR1)

    Hi again Wendell. I have another slight problem. My users have informed me that they also need to open RTF files listed in the directory. From the code you gave me I believe shell32.dll, in the ShellExecute function , opens Internet Explorer. Can you tell me what to use in this line to use MSWord to open the RTF's or is it not that easy?

  13. #13
    Star Lounger
    Join Date
    Jul 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    57
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Recreate links to image files (Access 2000 SR1)

    Hi again Wendell. I have another slight problem. My users have informed me that they also need to open RTF files listed in the directory. From the code you gave me I believe shell32.dll, in the ShellExecute function , opens Internet Explorer. Can you tell me what to use in this line to use MSWord to open the RTF's or is it not that easy?

    Thanks,
    Sue

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Recreate links to image files (Access 2000 SR1)

    If Word is installed, the default "Open" action for .rtf files is to open them in Word, so ShellExecute should do what you want. As I mentioned before, calling ShellExecute with the "Open" action is equivalent to opening a file from Windows Explorer.

  15. #15
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Recreate links to image files (Access 2000 SR1)

    Yep - 2 monitors means 2 physical screens - I think the video card ran somewhere around US$100 18 months ago. We use it primarily to watch Access objects while you are stepping through code, though sometimes it's nice to be able to see 50 colums in a table or an Excel worksheet.
    Wendell

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •