Solved

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

Posted on 2001-06-04
9
302 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
  • 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

911 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now