Solved

How do I insert an OLE object into a database using ADO?

Posted on 2001-06-04
9
320 Views
Last Modified: 2013-11-26
I wish to insert an OLE object/ Microsoft Word Object into an SQL Server Database. I then want to be able to extract it into another OLE object, and open it.  Only problem is that OLE objects don't seem to support ADO.  Any iteas Tigers??

Cheers,

CJ.
 
0
Comment
Question by:CJHarrap
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 1

Expert Comment

by:morgan_peat
ID: 6155467
You can't actually store an object in a database.  The whole idea of object-orientated programming is that the object hides its internal state from you (encapsulation).
The only way you could store something like this (that I know of) is if the object is kind enough to serialise all it's internal data into some form of array / file that you can store.
Luckily, programs like Word and Excel do exactly that - save their state into a File!
Problem is, you will have to deal with each item individually.

There's another question around (something about Word and MTS) in which I recommended the chap to save a Word document into a file, then use VB file access to load it into a byte array.  You can then do whatever you want - pass it around the network, store it in a DB, etc....
0
 
LVL 1

Author Comment

by:CJHarrap
ID: 6158201
Thanks Morgan,
I have done this many time in Access and Access Projects... Storing Pictures or Documents in users tables.

Anyways...
Ok I have managed to insert a Word Document file into the recordset using the appendchunk method.  It is now a long binary type.  How do I now get that binary file back into word format?

If I set the rs field to another ole object control will this do it?

Cheers,

CJ.

   
0
 
LVL 1

Expert Comment

by:morgan_peat
ID: 6159759
Same sort of way as you do pictures etc.
Load the RS, use GetChunk (I think) to access the data.
Save this to a file using normal VB file access, then load the file using Word automation.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:CJHarrap
ID: 6162159
No problem getting the file back out of the recordset using getchunk, but it is now a Binary file.  When Word opens it, the document is corrupt.  Any idea how to stop this from happening??

Cheers,

CJ.
0
 
LVL 1

Accepted Solution

by:
morgan_peat earned 100 total points
ID: 6163002
Here's a sample:

    ' Create & do stuff to a word document
    Set oDoc = New Word.Document
   
   
    ' Get temporary file path
    sTempPath = Space$(MAX_PATH)
    lResult = GetTempPath(MAX_PATH, sTempPath)
    sTempPath = Left$(sTempPath, lResult)
   
    ' Get temporary file name
    sTempFile = Space$(MAX_PATH)
    lResult = GetTempFileName(sTempPath, "mp", 0, sTempFile)
    sTempFile = Left$(sTempFile, InStr(sTempFile, Chr$(0)) - 1)
   
    ' Save word doc to temporary file
    oDoc.SaveAs sTempFile
   
   
    ' Load file into byte array
    iFile = FreeFile
    Open sTempFile For Binary Access Read As #iFile
    lFileLen = LOF(iFile)
    ReDim bData(lFileLen)   ' Keep extra byte just in case...
    Get #iFile, , bData
    Close #iFile
   
   
    ' Save into DB
    Set oCn = New ADODB.Connection
    oCn.Open "FILE NAME=c:\mp\test.udl"
   
    Set oRs = New ADODB.Recordset
    oRs.Open "SELECT * FROM files WHERE file_id = -1", oCn, adOpenKeyset, adLockOptimistic
    oRs.AddNew
    oRs.Fields("file_blob").AppendChunk bData()
    oRs.Update
    lDbId = oRs.Fields("file_id")
    oRs.Close
    Set oRs = Nothing
   
    oDoc.Close
    Set oDoc = Nothing
   
   
   
    ' Load from DB
    Set oRs = New ADODB.Recordset
    oRs.Open "SELECT * FROM files WHERE file_id=" & lDbId, oCn, adOpenKeyset, adLockReadOnly
   
    ' Get temporary file name again.
    ' I'll keep the same name, just delete
    ' the file that was there before
    Kill sTempFile
   
    iFile = FreeFile
    Open sTempFile For Binary As #iFile
    lFileLen = LenB(oRs.Fields("file_blob"))
    bData = oRs.Fields("file_blob").GetChunk(lFileLen)
    Put #iFile, , bData()
    Close #iFile
   
    oRs.Close
    Set oRs = Nothing
   
    oCn.Close
    Set oCn = Nothing
   
   
    ' Load into word
    Set oWord = New Word.Application
    Set oDoc = oWord.Documents.Open(sTempFile)


There's no error handling, and my Word automation is a bit rusty, but it should give you an idea.  Namely:
- Save Word doc to a temporary file.
- Load doc into a byte array using LOF and Get.
- Save into DB using AppendChunk.
- Get BLOB size by using LenB on recordset Field.
- Use GetChunk to retrieve BLOB into a byte array.
- Save into a temporary file using Put.
- Load back into Word using automation.
0
 
LVL 1

Author Comment

by:CJHarrap
ID: 6166293
Beauty!
That works....  I was doing it slightly differently and was only getting half the binary field.

Thanks Morgan, Champ!
0
 
LVL 1

Author Comment

by:CJHarrap
ID: 6166294
Beauty!
That works....  I was doing it slightly differently and was only getting half the binary field.

Thanks Morgan, Champ!
0
 

Expert Comment

by:PremierK
ID: 10618144
CHHarrap are you using this in VB.NET?  If so, can you paste your code
0
 

Expert Comment

by:PremierK
ID: 10618153
I need to do this same thing with VB.NET any sample code?
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
bit defender blocks good applications 2 106
How to debug this code 7 65
Error when passing an argument to a vb6 that contains  '/' 7 46
TT Auto Dashboard 13 101
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question