mikeandbee
asked on
Retrieving jpeg images from Access 97
Hello,
I know this is a very common question, but I need to know how i can a) reference a jpeg image within Access (using the OLE object I guess) and then b) how I can retrieve the image along with other information in the table?
I am making an employee profile db, and i want to be able to click on the name of the person, and then display all the corresponding information with the image of the person.
thanks,
mb
I know this is a very common question, but I need to know how i can a) reference a jpeg image within Access (using the OLE object I guess) and then b) how I can retrieve the image along with other information in the table?
I am making an employee profile db, and i want to be able to click on the name of the person, and then display all the corresponding information with the image of the person.
thanks,
mb
Sorry this is a bit long but its the microsoft method of inserting a BMP image:
HOWTO: Retrieving Bitmap from Access and Displaying In Web Page
Last reviewed: July 31, 1998
Article ID: Q175261
-------------------------- ---------- ---------- ---------- ---------- ---------- ----
The information in this article applies to:
Microsoft Visual InterDev, version 1.0
Microsoft Active Server Pages, version 1.0b
Microsoft Visual Basic Professional and Enterprise Editions for Windows, version 5.0
SUMMARY
This article shows by example how to extract the bitmap photos in the Microsoft Access 97 Northwind.mdb database, and view them from a Web browser using Active Server Pages (ASP). In order to accomplish this task, an ActiveX DLL must be created that strips the Access and OLE headers from the field. This article shows how to create this ActiveX DLL, and how to implement it.
This article demonstrates how to use Visual Basic to retrieve a bitmap stored in an OLE Object field. Because the definition of OLE object storage is not documented, the following code searches the object's OLE header for characters consistent with the start of the graphic. This method may not work in all circumstances.
Be aware that Internet Explorer 3.0 is unable to display true color bitmaps. For this reason, the bitmaps stored in the Access database should be no higher than 256 colors.
Step-by-Step Example to Extract the Photos
Create a new project in Visual Basic and make the project an ActiveX DLL.
Add a reference to ActiveX Data Objects (ADO) by clicking the Project menu and selecting References. Select "Microsoft OLE DB ActiveX Data Objects 1.0 Library" and click OK.
Add a new module to the project by selecting the Project menu and clicking Add Module. Select Module and click Open.
Place the following code in the (general) (declarations) section of MODULE1.BAS:
' Enter the following Declare statement as one single line:
Public Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory"
(lpvDest As Any, lpvSource As Any, ByVal cbCopy As Long)
Type PT
Width As Integer
Height As Integer
End Type
Type OBJECTHEADER
Signature As Integer
HeaderSize As Integer
ObjectType As Long
NameLen As Integer
ClassLen As Integer
NameOffset As Integer
ClassOFfset As Integer
ObjectSize As PT
OleInfo As String * 256
End Type
Place the following code in the (general) (declarations) section of CLASS1.CLS:
Function DisplayBitmap(ByVal OleField As Variant)
Dim Arr() As Byte
Dim ObjHeader As OBJECTHEADER
Dim Buffer As String
Dim ObjectOffset As Long
Dim BitmapOffset As Long
Dim BitmapHeaderOffset As Integer
Dim ArrBmp() As Byte
Dim i As Long
'Resize the array, then fill it with
'the entire contents of the field
ReDim Arr(OleField.ActualSize)
Arr() = OleField.GetChunk(OleField .ActualSiz e)
'Copy the first 19 bytes into a variable
'of the OBJECTHEADER user defined type.
CopyMemory ObjHeader, Arr(0), 19
'Determine where the Access Header ends.
ObjectOffset = ObjHeader.HeaderSize + 1
'Grab enough bytes after the OLE header to get the bitmap header.
Buffer = ""
For i = ObjectOffset To ObjectOffset + 512
Buffer = Buffer & Chr(Arr(i))
Next i
'Make sure the class of the object is a Paint Brush object
If Mid(Buffer, 12, 6) = "PBrush" Then
BitmapHeaderOffset = InStr(Buffer, "BM")
If BitmapHeaderOffset > 0 Then
'Calculate the beginning of the bitmap
BitmapOffset = ObjectOffset + BitmapHeaderOffset - 1
'Move the bitmap into its own array
ReDim ArrBmp(UBound(Arr) - BitmapOffset)
CopyMemory ArrBmp(0), Arr(BitmapOffset), UBound(Arr) -
BitmapOffset + 1
'Return the bitmap
DisplayBitmap = ArrBmp
End If
End If
End Function
Rename the Project by selecting the Project menu, and clicking on "Project1 Properties" and type your new name in the "Project Name" field. This example assumes that you named the project "MyProject" and will refer to that name in future steps.
Make the project Apartment Model Threaded by selecting the "Unattended Execution" check box. Click OK.
Rename the Class in the Property Pane. This example assumes that you named the class "MyClass" and refers to that name in future steps.
Compile the DLL by clicking the File menu and selecting "Make MyProject.dll."
Create an ASP page named "bitmap.asp" that contains the following code:
<%@ LANGUAGE="VBSCRIPT" %>
<%
' You need to set up a System DSN named 'NWind' that points to
' the Northwind.mdb database
Set DataConn = Server.CreateObject("ADODB .Connectio n")
DataConn.Open "DSN=NWind", "admin", ""
Set cmdTemp = Server.CreateObject("ADODB .Command")
Set RS = Server.CreateObject("ADODB .Recordset ")
cmdTemp.CommandText = "SELECT Photo FROM Employees
WHERE EmployeeID = 1"
cmdTemp.CommandType = 1
Set cmdTemp.ActiveConnection = DataConn
RS.Open cmdTemp, , 0, 1
Response.ContentType = "image/bmp"
Set Bitmap = Server.CreateObject("MyPro ject.MyCla ss")
Response.BinaryWrite Bitmap.DisplayBitmap(RS("P hoto"))
RS.Close
%>
Create an HTML page named "BitmapTest.htm" that contains the following code:
<HTML>
<HEAD>
<TITLE>Bitmap Test</TITLE>
</HEAD>
<BODY>
<HR>
<img src="Bitmap.asp">
<HR>
</BODY>
</HTML>
HOWTO: Retrieving Bitmap from Access and Displaying In Web Page
Last reviewed: July 31, 1998
Article ID: Q175261
--------------------------
The information in this article applies to:
Microsoft Visual InterDev, version 1.0
Microsoft Active Server Pages, version 1.0b
Microsoft Visual Basic Professional and Enterprise Editions for Windows, version 5.0
SUMMARY
This article shows by example how to extract the bitmap photos in the Microsoft Access 97 Northwind.mdb database, and view them from a Web browser using Active Server Pages (ASP). In order to accomplish this task, an ActiveX DLL must be created that strips the Access and OLE headers from the field. This article shows how to create this ActiveX DLL, and how to implement it.
This article demonstrates how to use Visual Basic to retrieve a bitmap stored in an OLE Object field. Because the definition of OLE object storage is not documented, the following code searches the object's OLE header for characters consistent with the start of the graphic. This method may not work in all circumstances.
Be aware that Internet Explorer 3.0 is unable to display true color bitmaps. For this reason, the bitmaps stored in the Access database should be no higher than 256 colors.
Step-by-Step Example to Extract the Photos
Create a new project in Visual Basic and make the project an ActiveX DLL.
Add a reference to ActiveX Data Objects (ADO) by clicking the Project menu and selecting References. Select "Microsoft OLE DB ActiveX Data Objects 1.0 Library" and click OK.
Add a new module to the project by selecting the Project menu and clicking Add Module. Select Module and click Open.
Place the following code in the (general) (declarations) section of MODULE1.BAS:
' Enter the following Declare statement as one single line:
Public Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory"
(lpvDest As Any, lpvSource As Any, ByVal cbCopy As Long)
Type PT
Width As Integer
Height As Integer
End Type
Type OBJECTHEADER
Signature As Integer
HeaderSize As Integer
ObjectType As Long
NameLen As Integer
ClassLen As Integer
NameOffset As Integer
ClassOFfset As Integer
ObjectSize As PT
OleInfo As String * 256
End Type
Place the following code in the (general) (declarations) section of CLASS1.CLS:
Function DisplayBitmap(ByVal OleField As Variant)
Dim Arr() As Byte
Dim ObjHeader As OBJECTHEADER
Dim Buffer As String
Dim ObjectOffset As Long
Dim BitmapOffset As Long
Dim BitmapHeaderOffset As Integer
Dim ArrBmp() As Byte
Dim i As Long
'Resize the array, then fill it with
'the entire contents of the field
ReDim Arr(OleField.ActualSize)
Arr() = OleField.GetChunk(OleField
'Copy the first 19 bytes into a variable
'of the OBJECTHEADER user defined type.
CopyMemory ObjHeader, Arr(0), 19
'Determine where the Access Header ends.
ObjectOffset = ObjHeader.HeaderSize + 1
'Grab enough bytes after the OLE header to get the bitmap header.
Buffer = ""
For i = ObjectOffset To ObjectOffset + 512
Buffer = Buffer & Chr(Arr(i))
Next i
'Make sure the class of the object is a Paint Brush object
If Mid(Buffer, 12, 6) = "PBrush" Then
BitmapHeaderOffset = InStr(Buffer, "BM")
If BitmapHeaderOffset > 0 Then
'Calculate the beginning of the bitmap
BitmapOffset = ObjectOffset + BitmapHeaderOffset - 1
'Move the bitmap into its own array
ReDim ArrBmp(UBound(Arr) - BitmapOffset)
CopyMemory ArrBmp(0), Arr(BitmapOffset), UBound(Arr) -
BitmapOffset + 1
'Return the bitmap
DisplayBitmap = ArrBmp
End If
End If
End Function
Rename the Project by selecting the Project menu, and clicking on "Project1 Properties" and type your new name in the "Project Name" field. This example assumes that you named the project "MyProject" and will refer to that name in future steps.
Make the project Apartment Model Threaded by selecting the "Unattended Execution" check box. Click OK.
Rename the Class in the Property Pane. This example assumes that you named the class "MyClass" and refers to that name in future steps.
Compile the DLL by clicking the File menu and selecting "Make MyProject.dll."
Create an ASP page named "bitmap.asp" that contains the following code:
<%@ LANGUAGE="VBSCRIPT" %>
<%
' You need to set up a System DSN named 'NWind' that points to
' the Northwind.mdb database
Set DataConn = Server.CreateObject("ADODB
DataConn.Open "DSN=NWind", "admin", ""
Set cmdTemp = Server.CreateObject("ADODB
Set RS = Server.CreateObject("ADODB
cmdTemp.CommandText = "SELECT Photo FROM Employees
WHERE EmployeeID = 1"
cmdTemp.CommandType = 1
Set cmdTemp.ActiveConnection = DataConn
RS.Open cmdTemp, , 0, 1
Response.ContentType = "image/bmp"
Set Bitmap = Server.CreateObject("MyPro
Response.BinaryWrite Bitmap.DisplayBitmap(RS("P
RS.Close
%>
Create an HTML page named "BitmapTest.htm" that contains the following code:
<HTML>
<HEAD>
<TITLE>Bitmap Test</TITLE>
</HEAD>
<BODY>
<HR>
<img src="Bitmap.asp">
<HR>
</BODY>
</HTML>
Don`t u need SQL Server to insert a BLOB field?
Pepster,
you are right, you do need SQL server to store BLOB field...
mikeandbee,
treat Pepster's comment as an answer...
you are right, you do need SQL server to store BLOB field...
mikeandbee,
treat Pepster's comment as an answer...
To avoid many hassles do not store the images in the database. Only store the path to the image in one field of the database. Then through ASP you call this field and the path to the image is inserted into the HTML. I have also stored the dimensions of the image in separate fields and used those values to change the size of the image on the Web Page. I can send you an example of this if you want. E-mail me at critter017@yahoo.com
I'm not a complete expert on this, but I do know that you can insert or query Binary Large Objects(BLOB's) without SQL Server...Access97/2000 works if you have a component(dll) that handles it like www.aspsmart.com offers...aspSmartupload..
Steve
Steve
oh...but I agree with critter017, because the fact is, when you store a gif or jpg or whatever kind of file, it's filesize may be 5kb in reality, but it quite possibly will hog over 20kb as a BLOB...this gets to be pretty inefficient...pretty fast...storing the ImagePath and calling it up as an ASP object is much simpler, and much more space efficient.
my $.02
Steve
my $.02
Steve
ASKER
it is easier to simply put a reference to the image in Access
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This question was awarded, but never cleared due to the JSP-500 errors of that time. It was "stuck" against userID -1 versus the intended expert whom you awarded. This corrects the problem and the expert will now receive these points; points verified.
Please click on your Member Profile and select "View Question History" to navigate through any open or locked questions you may have to update and finalize them. If you are an EE Pro user, you can also choose Power Search to find all your open questions.
This is the Community Support link, if help is needed. Also a question is posted there specific to these changes that apply to the experts here. Also, I am including the link to our All Topics, since many new ones have recently been added.
https://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
https://www.experts-exchange.com/jsp/zonesAll.jsp
Thank you,
Moondancer
Moderator @ Experts Exchange
Please click on your Member Profile and select "View Question History" to navigate through any open or locked questions you may have to update and finalize them. If you are an EE Pro user, you can also choose Power Search to find all your open questions.
This is the Community Support link, if help is needed. Also a question is posted there specific to these changes that apply to the experts here. Also, I am including the link to our All Topics, since many new ones have recently been added.
https://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
https://www.experts-exchange.com/jsp/zonesAll.jsp
Thank you,
Moondancer
Moderator @ Experts Exchange
HOWTO: Displaying Images Stored in a BLOB Field
http://support.microsoft.com/support/kb/articles/Q173/3/08.ASP