Solved

Access OLE Objects (Word docs, Excel, etc..) in DB

Posted on 1999-01-22
16
420 Views
Last Modified: 2013-12-25
I posted this in the general section, too.

I have a database that contains an OLEObject field.  This field can contain Word documents, Excel Spreadsheets, etc... I need to know:
    1) How to correctly retrieve the Documents name without having to retrieve the entire document itself.
    2) How to retrieve the Document itself (preferably using the name from number 1

I'm creating an application that gives the user an explorer like interface.  The tree might look something like this:
      Root
        |_Word Documents
        |_Excel Spreadsheets
        |_etc...

If they click on Word documents then I would like to retrieve only the names into a listview to the right of the tree.  If they double click on one of the names I would then like to bring the document up into either an instance of Word or a Word-compatible OCX.

Anyone have any ideas?  The points are VERY negotiable (up to 400 for a complete answer)
 
 
 
0
Comment
Question by:tleforge
  • 7
  • 6
  • 3
16 Comments
 
LVL 1

Expert Comment

by:mukvij
Comment Utility
There are two ways around:
First Way:

Have one more field in database 'Object Type'. Which stores whether it is excel or word doc and another field stores the file name associated. So when you click at a node on TreeView control you can just show values from file_name field and when you somebody double-click at a value in ListView control you actually load the object.

Sample code for loading

if rs!ObjectTYpe="DOC" then
     set objword=new word.application
     objword.open rs!filename
else


0
 
LVL 1

Expert Comment

by:mukvij
Comment Utility
I am sorry I click by mistake. Answer is continued:
else
   set objexcel=new excel.applicatio
   objexcel.open rs!file_name
   objexcel.visible=true
endif

Secondway:

Just store the file name which I think you are doing now. Parse the file name string to know whether the extension is .doc or .xls. Rest is I beleive the same.


0
 

Author Comment

by:tleforge
Comment Utility
It looks promising, I'm going to try that out.  I'll get back to you ASAP.  It may take me the weekend though.  Sorry about the delay.

Thank you for your quick reply.

P.S.  I don't mind storing the TYPE of this object, but I was just wondering if you knew of a way that I could get that from the object itself or if I could even get at the Document properties that are a part of all Office9x documents, without actually opening it.  Again, thank you.
0
 
LVL 1

Expert Comment

by:mukvij
Comment Utility
So far as I know There is no generic thing like 'Office Document'. hence it almost always becomes necessary to bind to a particular object.
0
 

Author Comment

by:tleforge
Comment Utility
mukvij,

I apologize for taking so long to get back...

I tried your suggestion and couldn't get it to work.  Admittedly, I've been away from VB for a while, working with Delphi so I may be forgetting something.

I tried the code like you said and, of course, found that I needed to declare ObjWord.  At first I declared it as an Object and when I ran it an error came up saying that the "Object did not support this method" (I'm assuming the open method).  I then declared it as a Word.application (after adding the reference) and now it comes back saying "Method or data member not found" (again, on the open method).

Am I missing something?

0
 
LVL 1

Expert Comment

by:mukvij
Comment Utility
Add the reference to Word Object library and following is the sample code. For Excel Replece Documents by Workbooks.:

Dim objWord As Word.Application
Set objWord = New Word.Application

objWord.Documents.Open "d:\abc\xyz.doc"
objWord.Visible = True
0
 

Author Comment

by:tleforge
Comment Utility
mukvij,

Remember though, I'm not coming at this from a "document stored in a directory" standpoint, but instead from a "document stored in a database".

The "objWord.Documents.Open rs!WordDocument" statement gives me a Type Mismatch error.

0
 
LVL 1

Expert Comment

by:mukvij
Comment Utility
What does WordDocument field store. Does it store path of word file?
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:tleforge
Comment Utility
It's an OLE Object field.  It holds the actual document itself.  I didn't want to get into the mess of storing the documents in directory structures.  I've been there and with a potential of thousands of documents it gets pretty ugly.

I need to be able to store the Word documents in a database.  The documents themselves, not paths to the documents.
0
 
LVL 1

Expert Comment

by:mukvij
Comment Utility
I am sorry I did not understand you correctly. hence I wasted your time. All Information I passed was dependant on paths.

I really do not think it is possible. Only way I can think of is having a OLE Container control on your form. From there you can do whatever you want to do. Because if you want to open document contained in access field in seperate word window. It will not be linked. Heve a OLE containter control and set its SourceDoc property.

I think that would help.

0
 
LVL 4

Accepted Solution

by:
agarwal_rahul earned 400 total points
Comment Utility
Here is an e.g, where I store & load an rtf file from a text field. Maybe this will help you.

Private Sub cmdLoad_Click()
    Dim strSQL      As String
    Dim strChunk    As String
    Dim lngPos      As Long
    Dim lngColSize  As Long
    Dim intFileHnd  As Integer
   
    mrsetText.Requery
    mrsetText.MoveFirst
    txtTextDesc.Text = mrsetText("TEXT_DESC").Value
    lngColSize = mrsetText("TEXT").ColumnSize
       
    intFileHnd = FreeFile()
   
    Open App.Path & "\Try.Rtf" For Append As #intFileHnd
    For lngPos = 1 To lngColSize
        Print #intFileHnd, mrsetText("TEXT").GetChunk(CHUNK_SIZE)
        lngPos = lngPos + (CHUNK_SIZE - 1)
    Next lngPos
    Close intFileHnd
   
    rtbText.LoadFile App.Path & "\Try.Rtf", rtfRTF
    rtbText.Refresh
   
    Kill App.Path & "\Try.Rtf"
End Sub

Private Sub cmdSave_Click()
    On Error GoTo ErrorHandler
   
    Dim strSQL      As String
    Dim strChunk    As String
    Dim strNewItmID As String
   
    Dim lngPos      As Long
    Dim rsetAddNew  As rdoResultset
       
    gDB.QueryTimeOut = 0
       
    strNewItmID = GetNewKey("ITEM_ID")
   
    strSQL = "SELECT * FROM TEXT WHERE 1 = 2"
    gDB.OpenRecordset rsetAddNew, strSQL, rdOpenKeyset, rdConcurValues

    rsetAddNew.ActiveConnection.BeginTrans
        rsetAddNew.AddNew
        rsetAddNew("ITEM_ID").Value = strNewItmID
        rsetAddNew("TEXT_DESC").Value = txtTextDesc.Text
        rsetAddNew("TEXT").AppendChunk Null
       
        For lngPos = 1 To Len(rtbText.TextRTF)
            rsetAddNew("TEXT_DESC").Value = "UPD"
            strChunk = ""
            strChunk = Mid$(rtbText.TextRTF, lngPos, CHUNK_SIZE)
            rsetAddNew("TEXT").AppendChunk strChunk
               
            lngPos = lngPos + (CHUNK_SIZE - 1)
            DoEvents
        Next lngPos
       
        rsetAddNew.Update
    rsetAddNew.ActiveConnection.CommitTrans
   
    rsetAddNew.Close
    Set rsetAddNew = Nothing
   
    Exit Sub
   
ErrorHandler:
    rsetAddNew.ActiveConnection.RollbackTrans
    Call DBError_Log("DB Error", "cmdSave_Click()")
End Sub

0
 

Author Comment

by:tleforge
Comment Utility
It might take me a while to test your code, but from what I've read it looks REAL promising.  Let me ask you one more question... The RTF control that comes with VB is not quite as feature rich as MSWord is and thus does not except drawings, tables, etc... that Word can place in the documents.  I'm wondering if I were to find an RTF that was feature complete enough do you think this could still work?  I could probably go the route of bring Word INTO my app and using it as my RTF control, but I was wanting to stay away from that slow, clunky option if possible.

Also, do you know of a way to get this to work with other file types (see original question up top)?  Basically, I need to be able to store just about anything in a BLOB or some binary field and then retrieve it.

I'm not trying to get out of awarding you the points (I probably will anyhow depending on how my tests go), but I was just wondering if you could also help with the rest of the question and maybe supply a more generic solution.

Thanks for all your hard work.
0
 
LVL 4

Expert Comment

by:agarwal_rahul
Comment Utility
Here is another set of functions to read and write any binary file. This e.g is with Access db but it should also work with SQL db with some modifications. Probably you will need to add another field in db saying what type of object is stored in the field and then just read the data in similar file type. e.g if the object is a word document then read the data in a .doc file and open with word, if the object is an bmp file read in a .bmp file and read with Paintbrush or image control.

The e.g is very big.

Reading, Storing, & Writing Binary Large Objects (BLOBs)

You can store large data objects (such as sound, video, or graphic data) in a field with the OLE Object data type in a Microsoft Access table. Some large binary data objects cannot be represented, however, if they do not have an OLE server that understands the data being stored. You can also store copies of executable program files or other non-OLE data in a Microsoft Access table. This type of data is referred to as a binary large object bitmap (BLOB).

MORE INFORMATION
The following example contains two sample user-defined functions that you can use to manage large amounts of binary data in a field with the OLE Object data type. The user-defined functions are ReadBLOB() and WriteBLOB().

The ReadBLOB() function reads a binary disk file and stores it in an OLE Object field.
The WriteBLOB() function writes binary data stored in an OLE Object field to a disk file.

This example demonstrates how to copy a binary file into an OLE Object field and then write it back out to a new disk file:

Create a new module called BLOB and enter the following lines in the module's Declarations section:


      Option Explicit
      Const BlockSize = 32768
NOTE: if you are working with Microsoft Access 2.0 you will need to include the following definitions after the two previous ones:



      Const dbOpenTable = DB_OPEN_TABLE
      Const acSysCmdInitMeter = SYSCMD_INITMETER
      Const acSysCmdUpdateMeter = SYSCMD_UPDATEMETER
      Const acSysCmdRemoveMeter = SYSCMD_REMOVEMETER

Enter the following code in the module.

NOTE: This following technique will not work for Microsoft Access 1.x. For Microsoft Access 1.x you will need to modify the code so you will use Table variables instead of Recordset variables and the OpenTable function instead of OpenRecordset.

      '**************************************************************
      ' FUNCTION: ReadBLOB()
      '
      ' PURPOSE:
      '   Reads a BLOB from a disk file and stores the contents in the
      '   specified table and field.
      '
      ' PREREQUISITES:
      '   The specified table with the OLE object field to contain the
      '   binary data must be opened in Visual Basic code (Access Basic
      '   code in Microsoft Access 2.0 and earlier) and the correct record
      '   navigated to prior to calling the ReadBLOB() function.
      '
      ' ARGUMENTS:
      '   Source - The path and filename of the binary information
      '            to be read and stored.
      '   T      - The table object to store the data in.
      '   Field  - The OLE object field in table T to store the data in.
      '
      ' RETURN:
      '   The number of bytes read from the Source file.
      '**************************************************************
      Function ReadBLOB(Source As String, T As Recordset, _
      sField As String)
          Dim NumBlocks As Integer, SourceFile As Integer, i As Integer
          Dim FileLength As Long, LeftOver As Long
          Dim FileData As String
          Dim RetVal As Variant

          On Error GoTo Err_ReadBLOB

          ' Open the source file.
          SourceFile = FreeFile
          Open Source For Binary Access Read As SourceFile

          ' Get the length of the file.
          FileLength = LOF(SourceFile)
          If FileLength = 0 Then
              ReadBLOB = 0
              Exit Function
          End If

          ' Calculate the number of blocks to read and leftover bytes.
          NumBlocks = FileLength \ BlockSize
          LeftOver = FileLength Mod BlockSize

          ' SysCmd is used to manipulate status bar meter.
          RetVal = SysCmd(acSysCmdInitMeter, "Reading BLOB", _
                   FileLength \ 1000)

          ' Put first record in edit mode.
          T.MoveFirst
          T.Edit

          ' Read the leftover data, writing it to the table.
          FileData = String$(LeftOver, 32)
          Get SourceFile, , FileData
          T(sField).AppendChunk (FileData)

          RetVal = SysCmd(acSysCmdUpdateMeter, LeftOver / 1000)

          ' Read the remaining blocks of data, writing them to the table.
          FileData = String$(BlockSize, 32)
          For i = 1 To NumBlocks
              Get SourceFile, , FileData
              T(sField).AppendChunk (FileData)

              RetVal = SysCmd(acSysCmdUpdateMeter, BlockSize * i / 1000)
          Next i

          ' Update the record and terminates function.
          T.Update
          RetVal = SysCmd(acSysCmdRemoveMeter)
          Close SourceFile
          ReadBLOB = FileLength
          Exit Function

      Err_ReadBLOB:
          ReadBLOB = -Err
          Exit Function

      End Function

      '**************************************************************
      ' FUNCTION: WriteBLOB()
      '
      ' PURPOSE:
      '   Writes BLOB information stored in the specified table and field
      '   to the specified disk file.
      '
      ' PREREQUISITES:
      '   The specified table with the OLE object field containing the
      '   binary data must be opened in Visual Basic code (Access Basic
      '   code in Microsoft Access 2.0 or earlier) and the correct
      '   record navigated to prior to calling the WriteBLOB() function.
      '
      ' ARGUMENTS:
      '   T           - The table object containing the binary information.
      '   sField      - The OLE object field in table T containing the
      '                 binary information to write.
      '   Destination - The path and filename to write the binary
      '                 information to.
      '
      ' RETURN:
      '   The number of bytes written to the destination file.
      '**************************************************************
      Function WriteBLOB(T As Recordset, sField As String, _
      Destination As String)
          Dim NumBlocks As Integer, DestFile As Integer, i As Integer
          Dim FileLength As Long, LeftOver As Long
          Dim FileData As String
          Dim RetVal As Variant

          On Error GoTo Err_WriteBLOB

          ' Get the size of the field.
          FileLength = T(sField).FieldSize()
          If FileLength = 0 Then
              WriteBLOB = 0
              Exit Function
          End If

          ' Calculate number of blocks to write and leftover bytes.
          NumBlocks = FileLength \ BlockSize
          LeftOver = FileLength Mod BlockSize

          ' Remove any existing destination file.
          DestFile = FreeFile
          Open Destination For Output As DestFile
          Close DestFile

          ' Open the destination file.
          Open Destination For Binary As DestFile

          ' SysCmd is used to manipulate the status bar meter.
          RetVal = SysCmd(acSysCmdInitMeter, _
          "Writing BLOB", FileLength / 1000)

          ' Write the leftover data to the output file.
          FileData = T(sField).GetChunk(0, LeftOver)
          Put DestFile, , FileData

          ' Update the status bar meter.
          RetVal = SysCmd(acSysCmdUpdateMeter, LeftOver / 1000)

          ' Write the remaining blocks of data to the output file.
          For i = 1 To NumBlocks
              ' Reads a chunk and writes it to output file.
              FileData = T(sField).GetChunk((i - 1) * BlockSize _
                 + LeftOver, BlockSize)
              Put DestFile, , FileData

              RetVal = SysCmd(acSysCmdUpdateMeter, _
              ((i - 1) * BlockSize + LeftOver) / 1000)
          Next i

          ' Terminates function
          RetVal = SysCmd(acSysCmdRemoveMeter)
          Close DestFile
          WriteBLOB = FileLength
          Exit Function

      Err_WriteBLOB:
          WriteBLOB = -Err
          Exit Function

      End Function

      '**************************************************************
      ' SUB: CopyFile
      '
      ' PURPOSE:
      '   Demonstrates how to use ReadBLOB() and WriteBLOB().
      '
      ' PREREQUISITES:
      '   A table called BLOB that contains an OLE Object field called
      '   Blob.
      '
      ' ARGUMENTS:
      '   Source - The path and filename of the information to copy.
      '   Destination - The path and filename of the file to write
      '                 the binary information to.
      '
      ' EXAMPLE:
      '   CopyFile "c:\windows\winfile.hlp", "c:\windows\winfil_1.hlp"
      '**************************************************************
      Sub CopyFile(Source As String, Destination As String)
          Dim BytesRead As Variant, BytesWritten As Variant
          Dim Msg As String
          Dim db As Database
          Dim T As Recordset

          ' Open the BLOB table.
          Set db = CurrentDb()
          Set T = db.OpenRecordset("BLOB", dbOpenTable)

          ' Create a new record and move to it.
          T.AddNew
          T.Update
          T.MoveLast

          BytesRead = ReadBLOB(Source, T, "Blob")

          Msg = "Finished reading """ & Source & """"
          Msg = Msg & Chr$(13) & ".. " & BytesRead & " bytes read."
          MsgBox Msg, 64, "Copy File"

          BytesWritten = WriteBLOB(T, "Blob", Destination)

          Msg = "Finished writing """ & Destination & """"
          Msg = Msg & Chr$(13) & ".. " & BytesWritten & " bytes written."
          MsgBox Msg, 64, "Copy File"
      End Sub


Create the following new table and then save it as BLOB:


      Table: BLOB
      ------------------------
      Field Name: Blob
         Data Type: OLE Object


With the BLOB module open in Design view, click Debug Window (or Immediate Window in Microsoft Access 2.0 or earlier) on the View menu.

Type the following line in the Debug window, and then press ENTER:


      CopyFile "c:\windows\winfile.hlp", _
               "c:\windows\winfil_1.hlp"

The ReadBLOB() and WriteBLOB() functions copy the Microsoft Windows Help file to the Blob field in the BLOB table and then from there to a disk file called Winfil_1.hlp  

0
 

Author Comment

by:tleforge
Comment Utility
WOW!!!  Sorry it took so long... I've been on a business trip to Mauai.  Thanks a ton!!! and here's the 400 points I promised for a complete answer.

Again, Wow!!!
0
 

Author Comment

by:tleforge
Comment Utility
Hmmmm... agarwal_rahul, did you get 400 points.  I increased it and then sent it along, but I don't think MY points decreased by 400.  Let me know and I'll try to rectify if needed.
0
 
LVL 4

Expert Comment

by:agarwal_rahul
Comment Utility
I think I got the 400 points. Thanks a lot.

0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
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 utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

763 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

11 Experts available now in Live!

Get 1:1 Help Now