VB, OLE and BLOBS

[dirksm]

Hi All.

I need to open and edit a MS Word document in my VB6 application. I have been taking a look at the OLE and Word Document components, but I'm not so sure how to use them. Anyone with some help/info/tips on this?

Secondly, I need to save the above mentioned document as a BLOB in a DB2/SQL-Server database and later I must be able to recall the saved document from the database into my application again.

Thanx in advance for your time and help!!

 

[StewartJ]

CreateObject("Word.Application&quot to get a Word application object that gives you full access to Word's VBA implementation.

Save your document in a temporary file and then use the TextStream object from MicroSoft Scripting Runtime to store and retrieve it in a BLOB. This works well.

I do not know of any way to save the document directly without putting it on disc first. If anyone does, I would be very interested!

 

[dirksm]

Thanx, but I'm still lost!!

Could you maybe give me some more detail or some sample code?
I know I must sound really stupid, but I'm very new (and intimidated) by all of this.

Thanx for your time.

 

[sunaj]

Hi,

Create a new vb standard exe project. Put a command button on the form and paste the follwing code. Remeber to set the references.

------------------------------------------------------------
Option Explicit
'Reference to 'Mircosoft word 9.0 object library'
Dim w As Word.Application
'Reference to 'MS ADO Ext. 2.6 for DDL and Sequrity'
Dim oCat As ADOX.Catalog
'Reference to Microsoft ActiveX Data Objects 2.5 library
Dim Conn As ADODB.Connection, Rst As ADODB.Recordset

Private Sub Command1_Click()
Dim OutPutData() As Byte, OutFile As Byte

Set Rst = New Recordset
Rst.Open "SELECT TOP 1 MyBlob FROM tblTestBlob", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = c:\test.mdb;"
OutPutData = Rst.Fields("MyBlob&quot.GetChunk(Rst.Fields("MyBlob&quot.ActualSize) 'get everything in one chunk
Rst.Close
Set Rst = Nothing

'OutPut data to new file
If Dir(&quot;c:\out.doc&quot <> &quot;&quot; Then Kill &quot;c:\out.doc&quot;
OutFile = FreeFile
Open &quot;c:\out.doc&quot; For Binary Access Write As #OutFile
Put #OutFile, , OutPutData
Close #OutFile
MsgBox &quot;Document retrieved from db written to c:\out.doc&quot;
End Sub

Private Sub Form_Load()
Dim strCon As String, InFile As Byte
Dim InputData() As Byte

'Create database
If Dir(&quot;c:\test.mdb&quot <> &quot;&quot; Then Kill &quot;c:\test.mdb&quot;
Set oCat = New ADOX.Catalog
strCon = oCat.Create(&quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source = c:\test.mdb;&quot
Set oCat = Nothing
'Connection
Set Conn = New ADODB.Connection
Conn.Open strCon

Conn.Execute &quot;CREATE TABLE tblTestBlob(tblTestBlobID COUNTER CONSTRAINT PK_TestBlob PRIMARY KEY, MyBlob LONGBINARY)&quot;

'Make Word document
Set w = New Word.Application
w.Documents.Add
w.ActiveDocument.Content.InsertBefore &quot;My word document&quot;
w.ActiveDocument.SaveAs &quot;c:\test.doc&quot;
w.ActiveDocument.Close
Set w = Nothing


'Open file
InFile = FreeFile
Open &quot;c:\test.doc&quot; For Binary Access Read As #InFile
ReDim InputData(LOF(InFile))
Get #InFile, , InputData 'retrieve data
Close #InFile

'Open recordset
Set Rst = New ADODB.Recordset
Rst.Open &quot;tblTestBlob&quot;, Conn, adOpenKeyset, adLockOptimistic, adCmdTable
Rst.AddNew 'new row
Rst.Fields(&quot;MyBlob&quot.AppendChunk InputData
Rst.Update
Rst.Close
Set Rst = Nothing

Conn.Close
Set Conn = Nothing
End Sub
------------------------------------------------------------ Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[dirksm]

Hi.
I tried the code on my DB2 database which has only on table called BLOB_TABLE with two field, KEY_COL (INTEGER) and BLOB_COL(BLOB), but I ran into some problems. As soon as I call the recordset's UPDATE method I receive an error. I either get a &quot;WRONG NUMBER OF PARAMETERS&quot; or a &quot;ARGUMENTS ARE OF WRONG TYPE,OUT OF RANGE OR IN CONFLICT WITH ONE ANOTHER&quot; error depending on whether I put in the adAffectAll clause with the update. I also tried giving the key field a fixed value everytime the program runs, instead of using an auto-increment field, but this doesn't make any difference to the error I rececive.

Could you please help me out?

Thanx for all your help so far!

Below is a listing of my code:

Option Explicit

Dim w As Word.Application
Dim Rst As ADODB.Recordset
Dim cnnDB2 As Connection 'Connection to my DB2 database

Private Sub Form_Load()

Dim strCon As String, InFile As Byte
Dim InputData() As Byte

'Connection
Set cnnDB2 = New Connection
cnnDB2.CursorLocation = adUseClient
cnnDB2.Open &quot;PROVIDER=IBMDADB2;DSN=TESTDB;&quot;

'Make Word document
Set w = New Word.Application
w.Documents.Add
w.ActiveDocument.Content.InsertBefore &quot;My word document&quot;
w.ActiveDocument.SaveAs &quot;c:\test.doc&quot;
w.ActiveDocument.Close
Set w = Nothing


'Open file
InFile = FreeFile
Open &quot;c:\test.doc&quot; For Binary Access Read As #InFile
ReDim InputData(LOF(InFile))
Get #InFile, , InputData 'retrieve data
Close #InFile

'Open recordset
Set Rst = New ADODB.Recordset
Rst.Open &quot;BLOB_TABLE&quot;, cnnDB2, adOpenKeyset, adLockOptimistic, adCmdTable
Rst.AddNew 'new row
Rst.Fields(&quot;BLOB_COL&quot.AppendChunk InputData

'Rst.Fields(&quot;KEY_COL&quot.Value = 4 'ADDING/REMOVING THIS LINE HAS NO IMPACT WHATSOEVER.

'Rst.Update '--------> WRONG NUMBER OF PARAMETERS

Rst.Update adAffectAll '--------> ARGUMENTS ARE OF WRONG TYPE,OUT OF RANGE OR IN CONFLICT WITH ONE ANOTHER

Rst.Close
Set Rst = Nothing

cnnDB2.Close
Set cnnDB2 = Nothing

End Sub

 

[sunaj]

Hi agian,

I've never worked with db2. The code here works for access & sql Server. Somehow there but be a mismatch between the declared data types (primary key, index?) and the code.

If the column KEY_COL is an autoincrement field you should not attempt to insert values into it. - simply leave out that line.

With this code the BLOB column cannot be a part of the primary key or an unique index.

Make sure that you have only 2 fields, that the 1st field is an autoincrement field and that this field makes up the primary key and make sure that you have no unique indexes. The BLOB field should probably also be allowed to be NULL, for this syntax to work.

Did that help? Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[dirksm]

Still no progress!

I'll get back to this later, but please let me know if anything comes up that might help me.

Well now for the second part. Suppose I managed to read the BLOB from the database and saved the output to file using the code you supplied. How do I open the file for view only and for edit in my VB application? I would like to display/edit the file in the application itself, without opening Word.

Again, thanx for all the help!