'提供一个我自己写的函数,我用ADO连接数据库,数据库为SQL SERVER 'ACCESS数据库没测试过,不过应该没有问题'读取数据库中的指定表中的指定字段的内容,并写入到指定的文件中,仅读取一条记录 '参数说明: 'sFileName 指定数据存放的文件名 'sRecordName 指定表名,即从那个表中读取数据 'sFieldName 指定字段名,即从那个字段中读取数据 'sDatabaseConnectionString 指定数据库连接字符串 'sFilter 指定筛选条件 Public Function fReadFileFromDatabase(ByVal sFileName$ _ , ByVal sRecordName$ _ , ByVal sFieldName$ _ , ByVal sDatabaseConnectionString$ _ , Optional ByVal sFilter$ = "" _ ) As Boolean Dim iRecord As New ADODB.Recordset Dim iCh() as Byte,iReturn As Boolean
On Error GoTo lbReadErr
iReturn = False If sFileName = "" Then MsgBox "必须指定文件名", 48 GoTo lbExit End If If Dir(sFileName) <> "" Then If MsgBox("文件:" & vbCrLf & _ sFileName & vbCrLf & _ "已经存在!,是否覆盖" _ , vbQuestion + vbYesNo + vbDefaultButton2) = vbYes Then Kill sFileName Else GoTo lbExit End If End If
If sRecordName = "" Or sFieldName = "" Then GoTo lbExit End If
If sDatabaseConnectionString = "" Then msgbox "请指定ADO数据库连接字符串",48 goto lbExit End If
With iRecord .Open sRecordName, sDatabaseConnectionString, adOpenKeyset, adLockOptimistic, adCmdTable .Filter = sFilter If IsNull(.Field(sFieldName)) Then MsgBox "字段 [" & sFieldName & "] 中无内容,读取不成功!", 48 Else Open sFileName For Binary Access Write As #1 iCh = .Field(sFieldName) Put 1, , iCh Close #1 End If iReturn = True
End With GoTo lbExit lbReadErr: Select Case MsgBox("从数据库中读取字段内容时发生下列错误:" & vbCrLf & _ Error() & vbCrLf & _ "错误代码:" & Err, vbAbortRetryIgnore + vbQuestion) Case vbRetry Resume Case vbAbort iReturn = False Case Else Resume Next End Select lbExit: If iRecord.State <> 0 Then iRecord.Close fReadFileFromDatabase = iReturn End Function
存取大文件,可以下载到本地,保持原格式,修改完之后保存回数据库中 存: Dim Chunk() As Byte If Imageabove.Tag < > " " Then 'Dim Chunk() As Byte Chunk() = Image2Chunk(tmpFile1) .Fields( "tp1 ").AppendChunk Chunk() .UpdateBatch adAffectCurrent End If Private Function Image2Chunk(Filename As String) As Variant On Error GoTo ProcErr Dim Datafile As Integer Dim FileLength As Long Dim Chunk() As Byte Datafile = FreeFile Open Filename For Binary Access Read As Datafile FileLength = LOF(Datafile) If FileLength = 0 Then GoTo ProcErr ReDim Chunk(FileLength) Get Datafile, , Chunk() Close Datafile
ProcExit: Image2Chunk = Chunk() Exit Function ProcErr: Image2Chunk = 0 End Function
取: If (rs.Fields( "tp1 ").ActualSize = 0) Then Image1.Picture = Nothing 'Exit Sub Else Dim Chunk() As Byte Chunk() = rs.Fields( "tp1 ").GetChunk(rs.Fields( "tp1 ").ActualSize) Set Image1.Picture = Chunk2Image(Chunk(), "temp.fil ") Image1.Tag = "hello " End If Public Function Chunk2Image(Chunk() As Byte, Optional Filename As String) As Variant On Error GoTo ProcErr Dim KeepFile As Boolean Dim Datafile As Integer KeepFile = True If Trim(Filename) = " " Then Filename = "c:\tmpxxdb.fil " KeepFile = False End If Datafile = FreeFile Open Filename For Binary Access Write As Datafile Put Datafile, , Chunk() Close Datafile ProcExit: Set Chunk2Image = LoadPicture(Filename) On Error Resume Next ' If Not KeepFile Then Kill filename Exit Function ProcErr: On Error Resume Next Kill Filename Chunk2Image = 0 End Function /*****************************************************************************/ 在internal这个用户下给scott用户授权如下: SQL >grant create any directory to scott; SQL >grant create any library to scott; 在scott这个用户下执行下述语句: SQL >create table bfile_tab (bfile_column BFILE); SQL >create table utl_lob_test (blob_column BLOB); SQL >create or replace directory utllobdir as 'C:\DDS\EXTPROC'; SQL >set serveroutput on 然后执行下面语句就将C:\DDS\EXTPROC目录下的word文件COM.doc存入到utl_lob_test 表中的blob_column字段中了。 declare a_blob BLOB; a_bfile BFILE := BFILENAME('UTLLOBDIR','COM.doc'); --用来指向外部操作系统 文件 begin insert into bfile_tab values (a_bfile) returning bfile_column into a_bfile; insert into utl_lob_test values (empty_blob()) returning blob_column into a_blob; dbms_lob.fileopen(a_bfile); dbms_lob.loadfromfile(a_blob, a_bfile, dbms_lob.getlength(a_bfile)); dbms_lob.fileclose(a_bfile); commit; end;
處於只讀的话可以设OLE的ENABLED属性FALSE
引用excel对象,显示出来
创建一个excel的对象,用这个对象来打开上面生成的临时文件
数据修改完成后,将临时文件写回到数据库中
删除临时文件
'ACCESS数据库没测试过,不过应该没有问题'读取数据库中的指定表中的指定字段的内容,并写入到指定的文件中,仅读取一条记录
'参数说明:
'sFileName 指定数据存放的文件名
'sRecordName 指定表名,即从那个表中读取数据
'sFieldName 指定字段名,即从那个字段中读取数据
'sDatabaseConnectionString 指定数据库连接字符串
'sFilter 指定筛选条件
Public Function fReadFileFromDatabase(ByVal sFileName$ _
, ByVal sRecordName$ _
, ByVal sFieldName$ _
, ByVal sDatabaseConnectionString$ _
, Optional ByVal sFilter$ = "" _
) As Boolean
Dim iRecord As New ADODB.Recordset
Dim iCh() as Byte,iReturn As Boolean
On Error GoTo lbReadErr
iReturn = False
If sFileName = "" Then
MsgBox "必须指定文件名", 48
GoTo lbExit
End If
If Dir(sFileName) <> "" Then
If MsgBox("文件:" & vbCrLf & _
sFileName & vbCrLf & _
"已经存在!,是否覆盖" _
, vbQuestion + vbYesNo + vbDefaultButton2) = vbYes Then
Kill sFileName
Else
GoTo lbExit
End If
End If
If sRecordName = "" Or sFieldName = "" Then
GoTo lbExit
End If
If sDatabaseConnectionString = "" Then
msgbox "请指定ADO数据库连接字符串",48
goto lbExit
End If
With iRecord
.Open sRecordName, sDatabaseConnectionString, adOpenKeyset, adLockOptimistic, adCmdTable
.Filter = sFilter
If IsNull(.Field(sFieldName)) Then
MsgBox "字段 [" & sFieldName & "] 中无内容,读取不成功!", 48
Else
Open sFileName For Binary Access Write As #1
iCh = .Field(sFieldName)
Put 1, , iCh
Close #1
End If
iReturn = True
End With
GoTo lbExit
lbReadErr:
Select Case MsgBox("从数据库中读取字段内容时发生下列错误:" & vbCrLf & _
Error() & vbCrLf & _
"错误代码:" & Err, vbAbortRetryIgnore + vbQuestion)
Case vbRetry
Resume
Case vbAbort
iReturn = False
Case Else
Resume Next
End Select
lbExit:
If iRecord.State <> 0 Then iRecord.Close
fReadFileFromDatabase = iReturn
End Function
存: Dim Chunk() As Byte
If Imageabove.Tag < > " " Then
'Dim Chunk() As Byte
Chunk() = Image2Chunk(tmpFile1)
.Fields( "tp1 ").AppendChunk Chunk()
.UpdateBatch adAffectCurrent
End If Private Function Image2Chunk(Filename As String) As Variant
On Error GoTo ProcErr
Dim Datafile As Integer
Dim FileLength As Long
Dim Chunk() As Byte Datafile = FreeFile
Open Filename For Binary Access Read As Datafile
FileLength = LOF(Datafile)
If FileLength = 0 Then GoTo ProcErr
ReDim Chunk(FileLength)
Get Datafile, , Chunk()
Close Datafile
ProcExit:
Image2Chunk = Chunk()
Exit Function ProcErr:
Image2Chunk = 0
End Function
取:
If (rs.Fields( "tp1 ").ActualSize = 0) Then
Image1.Picture = Nothing
'Exit Sub
Else
Dim Chunk() As Byte
Chunk() = rs.Fields( "tp1 ").GetChunk(rs.Fields( "tp1 ").ActualSize)
Set Image1.Picture = Chunk2Image(Chunk(), "temp.fil ")
Image1.Tag = "hello "
End If
Public Function Chunk2Image(Chunk() As Byte, Optional Filename As String) As Variant
On Error GoTo ProcErr
Dim KeepFile As Boolean
Dim Datafile As Integer KeepFile = True
If Trim(Filename) = " " Then
Filename = "c:\tmpxxdb.fil "
KeepFile = False
End If Datafile = FreeFile
Open Filename For Binary Access Write As Datafile
Put Datafile, , Chunk()
Close Datafile ProcExit:
Set Chunk2Image = LoadPicture(Filename)
On Error Resume Next
' If Not KeepFile Then Kill filename
Exit Function ProcErr:
On Error Resume Next
Kill Filename
Chunk2Image = 0
End Function
/*****************************************************************************/
在internal这个用户下给scott用户授权如下:
SQL >grant create any directory to scott;
SQL >grant create any library to scott;
在scott这个用户下执行下述语句: SQL >create table bfile_tab (bfile_column BFILE);
SQL >create table utl_lob_test (blob_column BLOB);
SQL >create or replace directory utllobdir as 'C:\DDS\EXTPROC';
SQL >set serveroutput on 然后执行下面语句就将C:\DDS\EXTPROC目录下的word文件COM.doc存入到utl_lob_test
表中的blob_column字段中了。 declare
a_blob BLOB;
a_bfile BFILE := BFILENAME('UTLLOBDIR','COM.doc'); --用来指向外部操作系统 文件
begin
insert into bfile_tab values (a_bfile)
returning bfile_column into a_bfile;
insert into utl_lob_test values (empty_blob())
returning blob_column into a_blob;
dbms_lob.fileopen(a_bfile);
dbms_lob.loadfromfile(a_blob, a_bfile, dbms_lob.getlength(a_bfile));
dbms_lob.fileclose(a_bfile);
commit;
end;