Adding a Word document to a sql db

I have a form that has a Word object. I want to save that object to the sql db. The original front and backend was an Access db and this all worked using an ole field. The backend data has been moved to a sql 2000 db. The reading of original Word objects works fine but I can't get any new objects stored. I have seen postings that there is no way to by-pass saving the object to a temp file but really I can't get anything to work. The attached code makes no complaints but when I try to access the new object I either get an empty object or an error that the ole server has a problem.

The code below includes simply saving an exisiting doc but that doc does not come back out of the db. I tried just for grins to store the form object in a temporary Access table and then save that field. Actually I got no complaints but then I got the same results. The use of the external file mimics examples I've seen even on this forum. any suggestions are appreciated.

Rick

'Now save word doc that is contained in the form entity
If Not IsNull(Me.oleSubSectionDetail) Then
Dim rst As ADODB.Recordset
Dim mstream As ADODB.Stream

'Tried to make it happen by essentially moving
'a database field to a db field
Dim oleRst As DAO.Recordset
Set oleRst = CurrentDb.OpenRecordset("tmpOLE", dbOpenDynaset)
oleRst.AddNew
'tempole is an ole defined field
oleRst!tempole = Me.oleSubSectionDetail
oleRst.Update
oleRst.MoveFirst

'Select record I want to update
Set rst = New ADODB.Recordset
rst.Open "Select * FROM [tbl-SOW Detail] WHERE RecID = " & RecID, ADOConnection.SQLDB_Connect, adOpenKeyset, adLockOptimistic

Set mstream = New ADODB.Stream
mstream.Type = adTypeBinary
mstream.Open

mstream.LoadFromFile "c:\\Documents and Settings\rburge\My Documents\Standard Contracts\-Cover Page.doc"

'or

'mstream.Write oleRst!tempole

rst.Fields("SubsectionDetail").Value = mstream.Read
rst.Update

rst.Close
Set rst = Nothing
Set mstream = Nothing
End If

I have more information: actually the above is working. It is that once you use that method to save the doc in the image field you must stream it out and to a file to properly read it.

I was hoping someone might share some light on this:

1) if you stream the document in and then stream it out the file is twice the size it was when input. If you open and save the word doc the size is restored. I have read some articles that suggest there is some overhead for every byte stored in the database image field.

2) Originally the backend was an access db which had many documents already stored as ole data type. It was easy enough using DAO to store and retrieve the document image with simple a=b type statements. I upsized the backend to SQL and those same records will allow me to retrieve the image and do a simple form field = db field, even using ADO. Now when I go through the streaming process to save a document I now have to always retrieve it the same way, via the stream. I'm going to have trouble with distiguishing between legacy records and new or I'm going to have to read and write every document record in the db to make sure everybody is on the same page so to speak.

Can anyone explain the storage differences?

Thanks,

Rick

|||

I came up with a solution:

I did create a local temp access table with an ole field. First I stored the document in the ms access field and then I could copy the ms access field to the sql image field. This helped preserve doc that were already in the sql db and preserved the basic document retrievial code. I think that this was a better solution (for me anyways) than reading and writing files.