'将任何二进制文件从数据库中下载到本地: Public Function LoadFile(ByVal col As ADODB.Field, ByVal FileName As String) As Boolean '获得binary数据 On Error GoTo myerr: Dim arrBytes() As Byte Dim FreeFileNumber As Integer lngsize = col.ActualSize arrBytes = col.GetChunk(lngsize) FreeFileNumber = FreeFile Open FileName For Binary Access Write As #FreeFileNumber Put #FreeFileNumber, , arrBytes Close #FreeFileNumber LoadFile = True myerr: If Err.Number <> 0 Then LoadFile = False Err.Clear End If End Function'将二进制文件从本地上传到数据库中 Public Function UpLoadFile(ByVal FileName, ByVal col As ADODB.Field) As Boolean On Error GoTo myerr: Dim arrBytes() As Byte Dim FreeFileNumber As Integer FreeFileNumber = FreeFile Open FileName For Binary As #FreeFileNumber n = LOF(FreeFileNumber) ReDim arrBytes(1 To n) As Byte Get #FreeFileNumber, , arrBytes Close #FreeFileNumber col.AppendChunk (arrBytes) UpLoadFile = True myerr: If Err.Number <> 0 Then UpLoadFile = False Err.Clear End If End Function
'VB/VBA中实现数据库中的文件存取'************************************************************************* '** '** 使用 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
'读取文件到内容 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
'将任何文件从数据库中下载到本地: Public Function LoadFile(ByVal col As ADODB.Field, ByVal FileName As String) As Boolean '获得binary数据 On Error GoTo myerr: Dim arrBytes() As Byte Dim FreeFileNumber As Integer lngsize = col.ActualSize arrBytes = col.GetChunk(lngsize) FreeFileNumber = FreeFile Open FileName For Binary Access Write As #FreeFileNumber Put #FreeFileNumber, , arrBytes Close #FreeFileNumber LoadFile = True myerr: If Err.Number <> 0 Then LoadFile = False Err.Clear End If End Function
'将文件从本地上传到数据库中 Public Function UpLoadFile(ByVal FileName, ByVal col As ADODB.Field) As Boolean On Error GoTo myerr: Dim arrBytes() As Byte Dim FreeFileNumber As Integer FreeFileNumber = FreeFile Open FileName For Binary As #FreeFileNumber n = LOF(FreeFileNumber) ReDim arrBytes(1 To n) As Byte Get #FreeFileNumber, , arrBytes Close #FreeFileNumber col.AppendChunk (arrBytes) UpLoadFile = True myerr: If Err.Number <> 0 Then UpLoadFile = False Err.Clear End If End Function ============================
当然也可以用文件流来实现,如: Dim cn As New ADODB.Connection Dim rs As New ADODB.Recordset Dim stm As ADODB.StreamPrivate Sub SavePictureToDB(cn As ADODB.Connection) '将BMP图片存入数据库 On Error GoTo EH Set stm = New ADODB.Stream rs.Open "select ImagePath,ImageValue from tbl_Image", cn, adOpenKeyset, adLockOptimistic CommonDialog1.ShowOpen Text1.Text = CommonDialog1.FileName
With stm .Type = adTypeBinary .Open .LoadFromFile CommonDialog1.FileName End With With rs .AddNew .Fields("ImagePath") = Text1.Text .Fields("ImageValue") = stm.Read .Update End With rs.Close Set rs = Nothing Exit Sub EH: MsgBox Err.Description, vbInformation, "Error" End Sub 上述bmp图片换成doc即可
文档肯定是不可能的了
Public Function LoadFile(ByVal col As ADODB.Field, ByVal FileName As String) As Boolean '获得binary数据
On Error GoTo myerr:
Dim arrBytes() As Byte
Dim FreeFileNumber As Integer
lngsize = col.ActualSize
arrBytes = col.GetChunk(lngsize)
FreeFileNumber = FreeFile
Open FileName For Binary Access Write As #FreeFileNumber
Put #FreeFileNumber, , arrBytes
Close #FreeFileNumber
LoadFile = True
myerr:
If Err.Number <> 0 Then
LoadFile = False
Err.Clear
End If
End Function'将二进制文件从本地上传到数据库中
Public Function UpLoadFile(ByVal FileName, ByVal col As ADODB.Field) As Boolean
On Error GoTo myerr:
Dim arrBytes() As Byte
Dim FreeFileNumber As Integer
FreeFileNumber = FreeFile
Open FileName For Binary As #FreeFileNumber
n = LOF(FreeFileNumber)
ReDim arrBytes(1 To n) As Byte
Get #FreeFileNumber, , arrBytes
Close #FreeFileNumber
col.AppendChunk (arrBytes)
UpLoadFile = True
myerr:
If Err.Number <> 0 Then
UpLoadFile = False
Err.Clear
End If
End Function
'**
'** 使用 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
'数据库连接字符串
iConcStr = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False" & _
";Data Source=F:\My Documents\客户资料1.mdb"
'读取文件到内容
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"
'保存到文件
Set iStm = New ADODB.Stream
With iStm
.Mode = adModeReadWrite
.Type = adTypeBinary
.Open
.Write iRe("img")
.SaveToFile "c:\test.doc"
End With
'关闭对象
iRe.Close
iStm.Close
End Sub
http://expert.csdn.net/Expert/topic/2403/2403509.xml?temp=.9905817
完整的代码如下:如果是用SQL那么保存文件的字段类型应该是Binary。如果是用Access,那么保存文件(包括图片)的字段应该用OLD对象,在表中显示为长二进制数据。
'将任何文件从数据库中下载到本地:
Public Function LoadFile(ByVal col As ADODB.Field, ByVal FileName As String) As Boolean '获得binary数据
On Error GoTo myerr:
Dim arrBytes() As Byte
Dim FreeFileNumber As Integer
lngsize = col.ActualSize
arrBytes = col.GetChunk(lngsize)
FreeFileNumber = FreeFile
Open FileName For Binary Access Write As #FreeFileNumber
Put #FreeFileNumber, , arrBytes
Close #FreeFileNumber
LoadFile = True
myerr:
If Err.Number <> 0 Then
LoadFile = False
Err.Clear
End If
End Function
'将文件从本地上传到数据库中
Public Function UpLoadFile(ByVal FileName, ByVal col As ADODB.Field) As Boolean
On Error GoTo myerr:
Dim arrBytes() As Byte
Dim FreeFileNumber As Integer
FreeFileNumber = FreeFile
Open FileName For Binary As #FreeFileNumber
n = LOF(FreeFileNumber)
ReDim arrBytes(1 To n) As Byte
Get #FreeFileNumber, , arrBytes
Close #FreeFileNumber
col.AppendChunk (arrBytes)
UpLoadFile = True
myerr:
If Err.Number <> 0 Then
UpLoadFile = False
Err.Clear
End If
End Function
============================
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim stm As ADODB.StreamPrivate Sub SavePictureToDB(cn As ADODB.Connection)
'将BMP图片存入数据库
On Error GoTo EH
Set stm = New ADODB.Stream
rs.Open "select ImagePath,ImageValue from tbl_Image", cn, adOpenKeyset, adLockOptimistic
CommonDialog1.ShowOpen
Text1.Text = CommonDialog1.FileName
With stm
.Type = adTypeBinary
.Open
.LoadFromFile CommonDialog1.FileName
End With
With rs
.AddNew
.Fields("ImagePath") = Text1.Text
.Fields("ImageValue") = stm.Read
.Update
End With
rs.Close
Set rs = Nothing
Exit Sub
EH: MsgBox Err.Description, vbInformation, "Error"
End Sub
上述bmp图片换成doc即可