把word文档存入sqlserver中，如何读出来(在线)

Dim Constr As String  'ODBC路径
    Dim FileName As String '图片文件名
    Const BLOCKSIZE = 4096 '每次读写块的大小
    Dim ADOCon As New ADODB.Connection 'ADODB Connection对象
    Dim adorst As New ADODB.Recordset 'ADODB Recordset 对象
    Dim ADOFld As ADODB.Field 'ADODB Field 对象
Private Sub Form_Load()
Constr = "DSN=image" '定义ODBC连接
     ADOCon.Open Constr '创建一个连接
    Set adorst = ADOCon.Execute("select fileno,content from wj where id=6")
     'ADORst.Open "WJ", ADOCon, adOpenDynamic, adLockOptimistic
    '打开一个ADO动态集表名为table
     'Set AdoCtr.Recordset = ADORst '将动态集赋给ADO控件
     If Not adorst.EOF Then
       Text1.Text = adorst("fileno")
       Set ADOFld = adorst("content")
       dbTofile ADOFld, "d:\" & adorst("fileno") & ".doc"
     End If
End Sub
Private Sub dbTofile(ByRef Fld As ADODB.Field, DiskFile As String)
    Dim byteData() As Byte '定义数据块数组
    Dim NumBlocks As Long ''定义数据块个数
    Dim FileLength As Long '标识文件长度
    Dim LeftOver As Long '定义剩余字节长度
    Dim SourceFile As Long '定义自由文件号
    Dim i As Long '定义循环变量
    SourceFile = FreeFile
    Open DiskFile For Binary Access Write As SourceFile

    ReDim byteData(Fld.ActualSize)
        byteData = Fld.GetChunk(Fld.ActualSize)
        Put #SourceFile, , byteData()
    Close #SourceFile
    End    NumBlocks = Fld.ActualSize \ BLOCKSIZE
    LeftOver = Fld.ActualSize Mod BLOCKSIZE
    For i = 1 To NumBlocks
        byteData = Fld.GetChunk(BLOCKSIZE)
        Put #SourceFile, , byteData()
    Next
    ReDim byteData(LeftOver)
    byteData = Fld.GetChunk(LeftOver)
    Put #SourceFile, , byteData()
    Close #SourceFile


    End Sub

所有的文件在sql中都可以用image类型的字段,按二进制编码存储

'VB/VBA中实现数据库中的文件存取'示例数据库为ACCESS数据库,用SQL数据库的话,只需要改连接字符串
'
'*************************************************************************
'**
'** 使用 ADODB.Stream 保存/读取文件到数据库
'** 引用 Microsoft ActiveX Data Objects 2.5 Library 及以上版本
'**
'** ----- 数据库连接字符串模板 ---------------------------------------
'** ACCESS数据库
'** iConcStr = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False" & _
'**     ";Data Source=数据库名"
'**
'** SQL数据库
'** iConcStr = "Provider=SQLOLEDB.1;Persist Security Info=True;" & _
'**     "User ID=用户名;Password=密码;Initial Catalog=数据库名;Data Source=SQL服务器名"
'**
'*************************************************************************
'
'保存文件到数据库中
Sub s_SaveFile()
    Dim iStm As ADODB.Stream
    Dim iRe As ADODB.Recordset
    Dim iConcStr As String

    'ACCESS数据库的连接字符串
    iConcStr = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False" & _
        ";Data Source=F:\My Documents\客户资料1.mdb"

'SQL数据库的连接字符串
    iConcStr = "Provider=SQLOLEDB.1;Persist Security Info=True;" & _
        "User ID=用户名;Password=密码;Initial Catalog=数据库名;Data Source=SQL服务器名"
    '读取文件到内容
    Set iStm = New ADODB.Stream
    With iStm
        .Type = adTypeBinary    '二进制模式
        .Open
        .LoadFromFile "c:\test.doc"
    End With

    '打开保存文件的表
    Set iRe = New ADODB.Recordset
    With iRe
        .Open "表", iConc, adOpenKeyset, adLockOptimistic
        .AddNew         '新增一条记录
        .Fields("保存文件内容的字段") = iStm.Read
        .Update
    End With

    '完成后关闭对象
    iRe.Close
    iStm.Close
End Sub

'从数据库中读取数据,保存成文件
Sub s_ReadFile()
    Dim iStm As ADODB.Stream
    Dim iRe As ADODB.Recordset
    Dim iConc As String

    '数据库连接字符串
    iConc = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False" & _
        ";Data Source=\\xz\c$\Inetpub\zj\zj\zj.mdb"

    '打开表
    Set iRe = New ADODB.Recordset
    iRe.Open "tb_img", iConc, adOpenKeyset, adLockReadOnly
    iRe.Filter = "id=64"    if iRe("img").ActualSize>0 Then    '保存到文件
    Set iStm = New ADODB.Stream
    With iStm
        .Mode = adModeReadWrite
        .Type = adTypeBinary
        .Open
        .Write iRe("img")
        .SaveToFile "c:\test.doc"
    End With

    '关闭对象
    iStm.Close
End If    iRe.Close
End Sub

凑个热闹^_^'** 引用 Microsoft ActiveX Data Objects 2.5 Library 及以上版本
'** 引用 Microsoft Word 9.0 Objects Library
'** 保存Word文档的字段为word（Access数据库为OLE对象，SQL数据库为image）Option Explicit
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim StmWord As ADODB.Stream'调用WORD
Sub OpenWord(FileName As String)
    Dim WordTemps As New Word.Application
    WordTemps.Documents.Add FileName, False
    WordTemps.Visible = True
End Sub'连接数据库
Private Sub Form_Load()
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & App.Path & "\db1.mdb;" & _
        "Persist Security Info=False"

    'cn.Open "Provider = SQLOLEDB.1;Persist Security Info = False;" & _
        '"User ID = sa;Password = abc;Data Source = SERVER;" & _
        '"Initial Catalog = youDB"
End Sub'将Wowd文档保存到数据库
Private Sub cmdSave_Click()
    Set rs = New ADODB.Recordset
    rs.Open "select * from tb_word", _
        cn, adOpenKeyset, adLockOptimistic
    Set StmWord = New ADODB.Stream
    With StmWord
        .Type = adTypeBinary
        .Open
        .LoadFromFile "E:\jsjlt.doc"
    End With
    rs.AddNew
    rs.Fields("word") = StmWord.Read
    rs.Update
    StmWord.Close
    rs.Close
End Sub'读取数据库中的Word文档
Private Sub cmdRead_Click()
    Dim Sql As String
    Sql = "select * from tb_word where word_id=1"
    Set rs = New ADODB.Recordset
    rs.Open Sql, cn, adOpenKeyset, adLockOptimistic
    If Dir(App.Path & "\TempTest.doc") <> "" Then
        Kill App.Path & "\TempTest.doc"
    End If
    Set StmWord = New ADODB.Stream
    With StmWord
        .Mode = adModeReadWrite
        .Type = adTypeBinary
        .Open
        .Write rs!Word
        .SaveToFile App.Path & "\TempTest.doc"
        .Close
    End With
    Call OpenWord(App.Path & "\TempTest.doc")
    rs.Close
End Sub

调试易

把word文档存入sqlserver中，如何读出来(在线)

解决方案 »