''VBA远程导入、导出二进制文件
''方法一:使用ADO AppendChunk、GetChunk方法
''方法二(推荐):使用ADO Stream对象
''环境:win7、sqlserver2008、access2010
''日期:2018-10-05
--sqlserver表
CREATE TABLE [tmp].[Fi](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Dt] [date] NOT NULL,
[FileNm] [nvarchar](100) NULL,
[FileTp] [nvarchar](100) NULL,
[FileData] [varbinary](max) NULL,
CONSTRAINT [Pk_Fi] PRIMARY KEY CLUSTERED
( [Id] ASC )
)
''====================================== 方法一 ======================================
Public Sub ImportBinData()
''ADO远程导入二进制文件,使用AppendChunk方法
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim Col As ADODB.Field
Dim cnStr As String
Dim sql As String
Dim strData() As Byte ''声明一个动态数组
Dim BlockNums As Long ''读写块数
Dim FileLength As Long ''文件长度
Dim LeftOver As Long ''剩余字节数
Dim SourceFileNum As Long ''文件句柄
Dim i As Long
Const BlockSize As Long = 4096 ''定义常量 块大小
SourceFileNum = FreeFile ''获得剩余的文件句柄号
Path = "d:\"
fileNm = "a.zip"
DiskFile = Path + fileNm
Set cn = New ADODB.Connection
cnStr = "Driver={sql server};server=asus;database=tmp;uid=sa;pwd=sa;"
cn.Open cnStr
Set rs = New ADODB.Recordset
sql = "tmp.tmp.fi"
rs.Open sql, cn, adOpenKeyset, adLockOptimistic, adCmdTable
Set Col = rs.Fields("FileData")
Open DiskFile For Binary Access Read As SourceFileNum ''读取二进制数据
FileLength = LOF(SourceFileNum) ''获得文件长度
MsgBox FileLength
If FileLength = 0 Then
Close SourceFileNum ''关闭文件
Else
BlockNums = FileLength \ BlockSize ''获得块数
LeftOver = FileLength Mod BlockSize ''最后一块的字节数