OLE Image JPEG from SQL Server to Access 2003 Form

  • Thread starter Thread starter Millard
  • Start date Start date
M

Millard

I need to pull photos from a SQL server into an Access 2003 form. Can
someone tell me how to do this?

I've looked online and find lots of information about handling this in
older versions of Access and when the images are text encoded... I also
find references in Microsoft to how to handle this with .NET, but I
don't see the way to handle this in Access 2003.

I connect to the server via ODBC. Opening the table as a linked table,
I get a display that shows the following columns and data:


Column Name Data
-------------------- ---------------------
ImageNumber: [Number here]
Picture OLE Object
ContentType image/jpeg

I'm not sure how to retreive the images. Can someone point me in the
right direction?

Thanks in advance!
 
Presumably your images are stored as raw binary/blobs (it looks like they are, since you have a content-type,
suitable for serving to web clients) – you can check by opening the table in Access; you should see the
text ‘Long Binary Data’ in the image field. To display the images without using third-party components
requires extracting the image to a temporary file then displaying the file in an Access image control.
There can be quite a performance hit with this approach (especially when scrolling between records), and
you’ll need to implement the registry hack to prevent the image control from crashing if you scroll too
quickly from one record to the next (and to suppress the ‘Loading Image’ dialog that pops up).

You can get some sample code for the blob extraction and display from Larry Linson’s site:
http://members.tripod.com/accdevel/

The registry hack can be found here:
http://www.mvps.org/access/api/api0038.htm

(on Win 2k and later also set the corresponding key in HKEY_CURRENT _USER, and remember that ‘No’ is case-sensitive).
 
Try this...

Create an ASP page on your server that pulls the photo (assuming you
have a Web server that can connect to the SQL server). Then display the
image using the Microsoft Web Browser component.

Here's some code I wrote for something similar.

ASP for your Web server to pull the image. I started from code at
http://www.codeproject.com/asp/Display_Images_from_Data.asp. This is
enhanced to display a "no photo available" image if the image is
missing.

<%
'*************************************************************************************
'Program to output images stored in a SQL database to a Web image
'*************************************************************************************

'Declare Variables..
Dim sql
Dim rs
Dim conn
Dim userID,str

userID = Request("PhotoId")
If userID = "" Then userID = 0

'Instantiate Objects
Set conn = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.Recordset")

'Open connection
Conn.Open "Driver={SQL Server};" & _
"Server=websql;" & _
"Database=Internet;" & _
"Uid=my_user;" & _
"Pwd=my_password"

'Get the specific image based on the ID passed in a querystring
str = "SELECT picture FROM Photo_Table WHERE (((userID)='" & userID
& "'))"
rs.Open str, conn,3,3
if rs.eof then 'No records found

Response.Redirect("nophoto.jpg")
Response.End


else 'Display the contents
Response.ContentType = "image/jpeg"
Response.BinaryWrite(rs("picture"))
end if

'destroy the variables.
rs.Close
conn.Close
set rs = Nothing
set conn = Nothing
%>

I also created an ASP page that improves the display in the Web Browser
component. Using this HTML allows you to control the image size, place
the image right up in the corner of the browser component, and removes
the scroll bars from the browser component.

<%
'*************************************************************************************
'Program to control display of images in the Web Browser Component.
'*************************************************************************************

'Declare Variables..
Dim userID,str

userID = Request("PhotoId")
If userID = "" Then userID = 0

Response.Write("<html><body scroll='no'
style='margin:0px;padding:0px;border:0px;'><img
src='http://www.yoursserver.com/ShowPhoto.asp?PhotoId=" & userID & "'
width='116' height='139'></body></html>")
Response.End

%>

To display the image in the form, use the Web Browser component. For
help on this, take a look at http://support.microsoft.com/?kbid=285820

I need to pull photos from a SQL server into an Access 2003 form. Can
someone tell me how to do this?

I've looked online and find lots of information about handling this in
older versions of Access and when the images are text encoded... I also
find references in Microsoft to how to handle this with .NET, but I
don't see the way to handle this in Access 2003.

I connect to the server via ODBC. Opening the table as a linked table,
I get a display that shows the following columns and data:


Column Name Data
-------------------- ---------------------
ImageNumber: [Number here]
Picture OLE Object
ContentType image/jpeg

I'm not sure how to retreive the images. Can someone point me in the
right direction?

Thanks in advance!
Click to expand...
 
One other thing... a good explaination of why this is a problem for
Access 2003 is at
http://www.ammara.com/articles/accesspictureole.html#S5. They also sell
a component that seems to work well, if that's the way you want to go.
Try this...

Create an ASP page on your server that pulls the photo (assuming you
have a Web server that can connect to the SQL server). Then display the
image using the Microsoft Web Browser component.

Here's some code I wrote for something similar.

ASP for your Web server to pull the image. I started from code at
http://www.codeproject.com/asp/Display_Images_from_Data.asp. This is
enhanced to display a "no photo available" image if the image is
missing.

<%
'*************************************************************************************
'Program to output images stored in a SQL database to a Web image
'*************************************************************************************

'Declare Variables..
Dim sql
Dim rs
Dim conn
Dim userID,str

userID = Request("PhotoId")
If userID = "" Then userID = 0

'Instantiate Objects
Set conn = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.Recordset")

'Open connection
Conn.Open "Driver={SQL Server};" & _
"Server=websql;" & _
"Database=Internet;" & _
"Uid=my_user;" & _
"Pwd=my_password"

'Get the specific image based on the ID passed in a querystring
str = "SELECT picture FROM Photo_Table WHERE (((userID)='" & userID
& "'))"
rs.Open str, conn,3,3
if rs.eof then 'No records found

Response.Redirect("nophoto.jpg")
Response.End


else 'Display the contents
Response.ContentType = "image/jpeg"
Response.BinaryWrite(rs("picture"))
end if

'destroy the variables.
rs.Close
conn.Close
set rs = Nothing
set conn = Nothing
%>

I also created an ASP page that improves the display in the Web Browser
component. Using this HTML allows you to control the image size, place
the image right up in the corner of the browser component, and removes
the scroll bars from the browser component.

<%
'*************************************************************************************
'Program to control display of images in the Web Browser Component.
'*************************************************************************************

'Declare Variables..
Dim userID,str

userID = Request("PhotoId")
If userID = "" Then userID = 0

Response.Write("<html><body scroll='no'
style='margin:0px;padding:0px;border:0px;'><img
src='http://www.yoursserver.com/ShowPhoto.asp?PhotoId=" & userID & "'
width='116' height='139'></body></html>")
Response.End

%>

To display the image in the form, use the Web Browser component. For
help on this, take a look at http://support.microsoft.com/?kbid=285820

I need to pull photos from a SQL server into an Access 2003 form. Can
someone tell me how to do this?

I've looked online and find lots of information about handling this in
older versions of Access and when the images are text encoded... I also
find references in Microsoft to how to handle this with .NET, but I
don't see the way to handle this in Access 2003.

I connect to the server via ODBC. Opening the table as a linked table,
I get a display that shows the following columns and data:


Column Name Data
-------------------- ---------------------
ImageNumber: [Number here]
Picture OLE Object
ContentType image/jpeg

I'm not sure how to retreive the images. Can someone point me in the
right direction?

Thanks in advance!
Click to expand...
Click to expand...
 
Back
Top