Results 1 to 5 of 5
  1. #1
    datahead is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    May 2015
    Location
    Missouri, USA
    Posts
    19

    Working with images in a SQL back end.


    (SQL Server 2014, Access 2007)

    I have spent a few hours trying to figure this one out. I hope I am missing something simple. To simplify my question let's say that we make a table in a SQL database with three columns: ID-(int), Item-(nvarchar(10) and Picture (varbinary(MAX).

    The table in SQL is linked to Access and then we create a form for the linked table using the form wizard. The bound object frame in the form for the Picture will allow me to right-click and attach a jpeg without any issue. I can verify that the jpeg is making it into the table in SQL.

    In the bound object frame properties I have the display type set to "Content" rather than "Icon". When I work with the form it only displays an icon with the actual file name below it. After the file name it displays "(Command Line)".

    Click image for larger version. 

Name:	sql_question.JPG 
Views:	15 
Size:	15.0 KB 
ID:	20882

    I need the image to actually show on the form. This function is going to be used for displaying inventory items to my coworkers on a split form so they can identify what we have in stock. I will be using the Cellica database app for capturing images with an Android tablet.

    What am I doing wrong here?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    I've never had success with the OLEObject controls (bound or unbound). Try using the Image control and setting its ControlSource property to the Picture field. But I've never worked with images stored in SQL db.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    datahead is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    May 2015
    Location
    Missouri, USA
    Posts
    19
    Well I added an image box to the form and pointed it to the Picture field. It does not display the image or an icon. The curious thing is that if I right-click on the box in the form it offers me the number filter options. If I am in record 1 it offers filters based on 1. If I am in record 2 it offers filters for 2 and so on. So even though I have it pointed at the Picture field it seems to be bound to the ID field. I assume it is binding to the ID field since it happens to be the primary key.

    I don't know a way around this. This basically destroys my initial plans to migrate to SQL.

    I even tried duplicating the above database in Access 2013 and using the migration assistant. The migration assistant will not stay connected to the SQL server for some reason. I figured I would try to migrate the newer version and if it worked maybe reverse engineer it.

    I wonder if Libreoffice can link to an MSSQL database and display the images stored there. I might have to fiddle with it and see.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    It was a long shot. I've never worked with images stored in SQL db. Alternative is to store images external from db with image path or just the image name in field and have the Image control ControlSource property point to the file location. The ControlSource can be a concatenated path, like: "C:\MyImages\" & [fieldname] & ".jpg"

    This might allow for better performance than if queries were having to drag across network entire datasets filled with embedded images.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    datahead is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    May 2015
    Location
    Missouri, USA
    Posts
    19
    I found a post on another forum the other day that I believe explains this issue. Apparently Access stores OLE objects in an OLE container format. So when I select "insert object" from the form and select a Jpeg it is being converted to the container format. If I inject a Jpeg into the SQL database directly Access never sees it because it is looking for this container format. This seems to be the case for a split Access database as well.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 03-28-2014, 05:44 PM
  2. alternate back color not working
    By dbaldwin117 in forum Reports
    Replies: 3
    Last Post: 02-25-2013, 01:33 PM
  3. BACK BUTTON Not Working
    By HENRYM in forum Forms
    Replies: 5
    Last Post: 01-19-2012, 03:16 PM
  4. Alternate Back Color not working properly
    By kolarbr in forum Access
    Replies: 7
    Last Post: 08-30-2011, 10:16 AM
  5. Copy or Back-up of DB Switchboard not working??
    By techexpressinc in forum Import/Export Data
    Replies: 1
    Last Post: 12-30-2008, 11:49 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums