Saving Word docs to HDD from a OLE Datafield

[Roberto]

Hi, I have a problem, I have an Access Database with lots of word doc
stored in a OLE field, and a vba program that work with them. I need
to convert the database to SQL Server and i have to make a new vb.net
software to manage the database.
I've tried to import all Access data to the SQL Server database, but
when i import the word docs it add an own access header, so i cannot
open them.
Ok. I've made a routine with vba that opens one by one all the docs in
the OLE field, so I open a doc, copy it in a new document, then I save
it on the hard drive and import into the SQL Server blob field.
This is my code

oConn.Open sConnSQL 'open the sql server db
i = 0
Set objWrkSpc = DBEngine(0)
Set objDataBase = objWrkSpc.OpenDatabase(strDBase) 'open the access
db
Set objRecSet = objDataBase.OpenRecordset("tblNote", dbOpenDynaset)
'open the access recordset
strTemp = "C:\Data\" 'Where i will save the documents on the hard
drive

With objRecSet
Do Until objRecSet.EOF
i = i + 1
sSQL = "Update tblNote SET CD_Nota = " & i & " WHERE
C_Cliente_Access = " & .Fields("C_Cliente_Access").Value & _
" AND Dt_Nota = #" & .Fields("Dt_Nota").Value & "#" & _
" AND D_Nota = '" & Replace(.Fields("D_Nota").Value, "'",
"''") & "'"
objDataBase.Execute sSQL 'I set a code for each record on the
access and then on the sql server table
DataDoc = CDate(.Fields("Dt_Nota").Value)
sSQL = "INSERT INTO tblNote
(CD_Nota,C_Cliente_Access,Dt_Nota,D_Nota) VALUES (" & i & "," &
..Fields("C_Cliente_Access").Value & _
",CONVERT(DATETIME, '" & Year(DataDoc) & "-" &
Month(DataDoc) & "-" & Day(DataDoc) & "',102),'" &
Replace(.Fields("D_Nota").Value, "'", "''") & "')"
oConn.Execute sSQL
Set OLE1 = CreateObject("Word.Application")

OLE1.Visible = True
DocumentoWord = .Fields("DocumentoWord") 'my word doc on the
access db
If Not IsNull(DocumentoWord.Value) Then
OLE1.Documents.Add 'Create the new word doc
DocumentoWord.Action = acOLECopy 'I copy the doc from the
access db
OLE1.Selection.Paste 'then i paste it on the new doc
OLE1.ActiveDocument.SaveAs FileName:="C:\Data\" & i &
".doc", _
FileFormat:=20 'and I save it on the hard drive
End If
OLE1.Quit
Set OLE1 = Nothing

.MoveNext
Loop
.Close
End With
objRecSet.Close
objDataBase.Close
objWrkSpc.Close
oConn.Close


And then there is a little vb.net program that import all the files on
the sql server database

All work fine but if I have a doc with more than one page, it copy
only the first one on the new document and i lost the other pages.
Have I made any mistake?
Thank you for any help.
Roberto

 

[Van T. Dinh]

I have not used this but try Steven Lebans' Web site:

http://www.lebans.com/oletodisk.htm

 

[Roberto]

Thank you Van but this sw saves on the hdd only bmp or jpg images
stored into an ole field.. It's quite easy to do that because bitmap
and jpg are the only binary file with a declared access header to
remove... If I could know the Word doc Access header I could remove it
to save correctly all the docs on the HDD...
Do you know anything about that?
Thank you
Roberto Cerulli

 

[Van T. Dinh]

Sorry, no. I never use OLE Fields in Access as OLE Fields tend to bloat the
database file.