SQL OLE Blob Decode and view in Access 2000 URGENT!

[Seth]

Access gurus,

Applications and Platforms involved: Access 2000, SQL server 2000

I have a table on the SQL 2000 Server that is called
PatientProfileAttachments. This table contains long binary data blobs
/ OLE objects.

When I attached this table to my access database, I can view the
table, but I cannot open OLE field. I get the error message

"A problem occurred while Microsoft Access was communicating with the
OLE server or ActiveX control"

This occurs because Microsoft Access adds an OLE Header to data
entered as an OLE Object Datatype. If you enter the data directly into
SQL Server and then open the field through Microsoft Access, the error
occurs, because the Microsoft Access OLE Header is not present.

what im trying to figure out is how to automate the process of
decoding the long binary data stored in the OLE object field?
Ideally, I would like to click on the OLE object field and just have
it open in its default viewer.

Any help is appreciated!

Seth Hicks

[Reid Kell]

This is of no help to you, but I have the same issue. When I store, say, a
Word document in an Image-type column, I cannot extract the file to its
original form. Coworkers of mine using Cold Fusion use a function called
Base64 which encodes and decodes a binary file to ASCII format. Check this
out for more information: http://www.catalyst.com/support/help/library/fed/.
If you ever find a solution, please post to the group. Thanks.

--
Reid Kell
Lockheed-Martin
Reid...@nwdc.net

<Seth> wrote in message news:3c9265b0....@news.microsoft.com...

[Seth Hicks]

Reid,
I found some information from the MS knowledge base.. using ADO. but
this is a bit beyond me. Can you or anyone else shed some light on
this on how to make this automate to just OPEN blob attachments? I
dont even really need it to do anything else but open them.
Seth

MSKB ARTICLE: How to read and write BLOBS using using GetChunk and
AppendChunk

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q194975

[Stephen Lebans]

Seth just for clarification, you have stored a complete file(Word DOc,
Excel file, whatever) within an SQL Server table as plain binary data,
not inserted by Access as an OLE document?

If you knew the full Filename or even just the file extension then you
could simply copy this BLOB to a temp disk file ensuring the proper file
extension to the temp file. You then could use the FollowHyperLink
method(or Shell Execute) to open the file in the default viewer for the
user's machine.

The issue here is determining the file name/extension for the data
stored in the BLOB. Do you have this filename/extension stored in
another field within the table? Otherwise you would have to write custom
code for each possible file type you need to support.
--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.

<Seth> wrote in message news:3c9265b0....@news.microsoft.com...

[Seth Hicks]

Stephen.
The data is inserted through another application to the sql server.
Im just "sharing" to my access application. I cannot change the table
at all, so Im stuck working around the structure...

Given the information I included below below can you suggest some
code to use the "type" field to decode the binary data, all I want to
do is open the file in its default viewer..... I dont want be able to
add any attachments.

Thank you so much for your time.

Seth Hicks
***************************************************************************
Heres the way the "attachment table" table is set up in the SQL
server. the name of the Table is dbo_PatientProfileAttachment

Column name: PatientProfileAttachmentId
Type: Identity
The internal ID of this row. This ID is guaranteed to be unique.

Columm name: PatientProfileId
Type: int
The patient to which this attachment is associated.

Column name: Name
Type: varchar 255
Allow NULL
The name of the attachment.

Column Name: Type
Type: varchar 255
Allow NULL
The type of the attachment.

Column Name: Data
Type: image
Allow NULL
The binary attachment.

Column Name: Created
Type: datetime
The date that this row was created in the system.

Column Name: CreatedBy
Type: varchar 30
The username of the person who created this row in the system.

Column Name: LastModified
Type: datetime
The date that this row was last modifed in the system.

Column NameL LastModifiedBy
Type: varchar 30
The username of the person who last modified this row in the system

*************************************************************************************************

[Seth Hicks]

Stephen, to clarify your question..... no, it was not inserted by
access as an OLE document. Thats why the OLE headers you would get
with Access arent there...

On Fri, 15 Mar 2002 23:44:05 GMT, "Stephen Lebans"
<NoE...@please.com> wrote:

[Stephen Lebans]

Seth here are 2 articles on the subject. First is a newer very simple
method to implement using ADO.
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q258038

This is the older, standard more complex method but with a specific
Access example.
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q103257

There is also code on my site in the LoadSave Jpeg project showing how
to read/write to BLOB fields.
http://www.lebans.com/loadsavejpeg.htm

--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.

"Seth Hicks" <highla...@yahoo.com> wrote in message
news:3c92d0e9.342691023@news-server...

[Seth Hicks]

Stephen,

Using the information in kb article Q258038 , Im working out the
framework of how to view the blob. Pease let me know if my logic is
flawed at all. This seems to be a common problem, so I think If we
work together, this may benefit alot of people. I'll limit my
questions to a few at a time....

*********************************************************************************

First I would need to create a form that holds the records I will be
extracting the blob data from.

Then I open a connection. Do i need to do this if Im aready
authenticated to the SQL server in access via OBDC?

CODE:
Set cn = New ADODB.Connection
cn.Open "Provider=SQLOLEDB;data Source=<name of your SQL Server>;
Initial Catalog=pubs;User Id=<Your Userid>;Password=<Your Password>"

************************************************************************************
Ok here it seems to be selecting the entire recordset, right?
How would you select the current record that was navigated to in the
form?

CODE:
Set rs = New ADODB.Recordset
rs.Open "Select * from pub_info", cn, adOpenKeyset, adLockOptimistic

*************************************************************************************

Pretty self-explanatory here. However, since there is a "type column"
in the sql server table, couldnt I use that "type value" to determine
the file extension? Then it should stream any OLE blob right?

CODE:
Set mstream = New ADODB.Stream
mstream.Type = adTypeBinary
mstream.Open
mstream.Write rs.Fields("logo").Value
mstream.SaveToFile "c:\publogo.gif", adSaveCreateOverWrite

rs.Close
cn.Close

Thanks a million!
Seth Hicks
On Sun, 17 Mar 2002 03:30:56 GMT, "Stephen Lebans"