我有一个Excel文件,一些用户正在使用它作为前端,我希望能够通过VBA上传一个通用文件(如PDF,图像,word文档…)到SQL Server。
在搜索了许多网站后,我把一些代码放在一起,我不知道在VBA SQL变量(INSERT INTO查询)中写什么,以便上传一些一般文件(如PDF)
VBA代码:
Sub SaveAsBinary()
Dim adoStream As Object
Dim adoCmd As Object
Dim strFilePath As String
Dim adoCon As Object
Dim SQL As String
Dim FileNameStr as String
Set adoCon = CreateObject("ADODB.Connection")
Set adoStream = CreateObject("ADODB.Stream")
Set adoCmd = CreateObject("ADODB.Command")
'--Open Connection to SQL server
adoCon.CursorLocation = 3 'adUseClient
adoCon.Open = "Driver={SQL Server Native Client 11.0};" _
& "MyServerName;" _
& "Database=MyDatabaseName;" _
& "Uid=MyUserName;" _
& "Pwd={MyPassword};" _
& "Connection Timeout=30;"
'----
strFilePath = Range("a1").Value 'keep File path to upload (simple local computer path)
FileNameStr = Dir(strFilePath )
SQL = "INSERT INTO Tbl_Test VALUES (FileNameStr, ??)" '‹‹---What value should be here for loading a pdf file (for example)
adoStream.Type = 1 'adTypeBinary
adoStream.Open
adoStream.LoadFromFile strFilePath 'It fails if file is open
With adoCmd
.CommandText = SQL ' Query
.CommandType = 1 ' adCmdText
'---adding parameters
.Parameters.Append .CreateParameter("@FileName", 20, 1, 0, 1)
.Parameters.Append .CreateParameter("@MyFile", 204, 1, adoStream.Size, adoStream.Read)
'---
End With
adoCmd.ActiveConnection = adoCon
adoCmd.Execute
adoCon.Close
End Sub
原始代码摘自:https://usefulgyaan.wordpress.com/2014/09/30/store-and-fetch-files-sql-server-tables/
Sql server中的表:
CREATE TABLE [dbo].[Tbl_Test](
[FileName] [nvarchar](200) NOT NULL,
[MyFile] [varbinary](max) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
一列为文件名,一列为文件本身。
注意:SQL Server在远程服务器上,没有共享文件夹/BLOB/FTP。
解决方案是保持行
SQL = "INSERT INTO Tbl_Test VALUES (?, ?)"
,然后用
将参数填入SQL中.Parameters.Append .CreateParameter("@FileName", 20, 1, 0, 1)
.Parameters.Append .CreateParameter("@MyFile", 204, 1, adoStream.Size, adoStream.Read)
使用CreateParameter方法和数据库字段的合适数据类型。
我会这样做:
SQL = "INSERT INTO Tbl_Test([FileName],[MyFile]) VALUES ('" & FileNameStr & "', CAST(" & adoStream.Read & ") as varbinary(max))"
备注:没有在VBA测试-我是AutoIt编码器,而不是VBA编码器。