?
Solved

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

Posted on 2001-06-04
9
Medium Priority
?
347 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 400 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

Independent Software Vendors: 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

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
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…
Suggested Courses
Course of the Month10 days, 15 hours left to enroll

770 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