Solved

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

Posted on 2001-06-04
9
300 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
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…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…

743 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

12 Experts available now in Live!

Get 1:1 Help Now