R
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
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