二进制文件存取示例(VB&VBA)
' 1. 保存文件到数据库
Sub s_SaveFile()
Dim iStm As ADODB.Stream
Dim iRe As ADODB.Recordset
Dim iConcStr As String
'SQL数据库的连接字符串
iConcStr = "Provider=SQLOLEDB.1;Persist Security Info=True;" & _
"User ID=用户名;Password=密码;Initial Catalog=数据库名;Data Source=SQLServer实例名"
'读取文件到内容
Set iStm = New ADODB.Stream
With iStm
.Type = adTypeBinary '二进制模式,如果是用text/ntext字段保存纯文本数据,则改用 adTypeText
.Open
.LoadFromFile "c:\test.doc" ‘读取文件
End With
'打开保存文件的表
Set iRe = New ADODB.Recordset
With iRe
.Open "tablename", iConc, adOpenKeyset, adLockOptimistic
.AddNew '新增一条记录
.Fields("保存文件数据的字段名") = iStm.Read
.Update
End With
'完成后关闭对象
iRe.Close
iStm.Close
End Sub
' 2. 数据库中读取数据,并保存到文件的示例代码。
Sub s_ReadFile()
Dim iStm As ADODB.Stream
Dim iRe As ADODB.Recordset
Dim iConcStr As String
'数据库连接字符串
iConcStr = "Provider=SQLOLEDB.1;Persist Security Info=True;" & _
"User ID=用户名;Password=密码;Initial Catalog=数据库名;Data Source=SQLServer实例名"
'打开保存文件数据的表
Set iRe = New ADODB.Recordset
iRe.Open "tablename", iConcStr, adOpenKeyset, adLockReadOnly
iRe.Filter = "id=1" ‘要读取文件的id
if iRe("保存文件数据的字段名").ActualSize>0 Then
'保存到文件
Set iStm = New ADODB.Stream
With iStm
.Mode = adModeReadWrite
.Type = adTypeBinary '二进制模式,如果是用text/ntext字段保存纯文本数据,则改用 adTypeText
.Open
.Write iRe("保存文件数据的字段名")
.SaveToFile "c:\test.doc"
End With
'关闭对象
iStm.Close
End If
iRe.Close
End Sub