MS Access/SQL Server-VBA:将本地文件上载到远程SQL Server上的文件流

Question

我需要每周向同一网络中远程服务器上的SQL Server 2016数据库上传一次文件(<10 MB(。到目前为止，这一切都在Access FE/BE中，但我想迁移到SQL Server作为后端。

我在MS Access中的附件现在需要在SQL数据库上处理，因为我不想在文件共享上这样做。

我从SQLShack 中发现了许多关于使用类似内容的线程

DECLARE @File varbinary(MAX);  
SELECT  
@File = CAST(bulkcolumn AS varbinary(max))  
FROM  
OPENROWSET(BULK 'C:sqlshackakshita.png', SINGLE_BLOB) as MyData; 

INSERT INTO DemoFileStreamTable_1  
VALUES  (NEWID(), 'Sample Picture', @File)

当我在SQL Server本身的SSMS中启动查询，并且服务器在其本地驱动器上已经可以访问该文件时，这就起作用了。

但是，当我试图将其放入Access前端计算机上的VBA代码中时：

Sub DaoOdbcExample()
Dim cdb As DAO.Database, qdf As DAO.QueryDef
Set cdb = CurrentDb
Set qdf = cdb.CreateQueryDef("")
qdf.Connect = "ODBC;" & _
"Driver={SQL Server};" & _
"Server=MyServer;" & _
"Database=MyDatabase;" & _
"Trusted_Connection=yes;"
qdf.SQL = "DECLARE @File varbinary(MAX); SELECT @File = CAST(bulkcolumn as varbinary(max))  FROM  OPENROWSET(BULK 'D:SomeFile.pdf', SINGLE_BLOB) as MyData; INSERT INTO DemoFileStreamTable_1  VALUES  (  NEWID(),  'Test PDF',  @File)"
qdf.ReturnsRecords = False
qdf.Execute dbFailOnError
Set qdf = Nothing
Set cdb = Nothing
End Sub

我刚收到一个错误

ODBC--调用失败的

其他简单的"选择"；语句似乎有效，因此连接本身似乎还可以。

所以我的问题是：

1. 如何使用MS access作为前端，将计算机a上的本地文件上载到计算机B上的远程SQL服务器(无法直接访问此文件(？

2. 有没有不同的方法不使用"；BULK"；我需要的陈述"；bulkadmin"；那么所有用户的权限？

Answer 1

我可能使用@AlwaysLearning的链接找到了一个解决方案。第一个子实际上回答了我将文件上载到远程FILESTREAM SQL Server的问题。第二个子系统将所有上传的文件下载到给定的目录中。

Private Sub btn_AddAtachment_Click()
Dim cn, rs  As Object
Dim sql, strCnxn, FileToUpload, FileName As String
'FileSystemObject to do so some file checks
Dim fso As Object
Set fso = VBA.CreateObject("Scripting.FileSystemObject")

'select file to upload, will open a FileOpenDialog
FileToUpload = CustOpenFileDialog
If FileToUpload <> "" Then
FileName = fso.GetFileName(FileToUpload) 'get only filename + extension

'SQL Connection
strCnxn = "Provider=sqloledb;" & _
"Data Source=MYSERVER;" & _
"Initial Catalog=MYDATABASE;" & _
"Integrated Security=SSPI;" 'Windows-Authentication

Set cn = CreateObject("ADODB.Connection")
cn.Open strCnxn

'Recordset
sql = "DemoFileStreamTable_1" 'Table to add file
Set rs = CreateObject("ADODB.Recordset")
rs.Open sql, strCnxn, 1, 3  '1 - adOpenKeyset, 3 - adLockOptimistic"

'Create Stream to upload File as BLOB data
Dim strm As Object
Set strm = CreateObject("ADODB.Stream")
strm.Type = 1 '1 - adTypeBinary
strm.Open
strm.LoadFromFile FileToUpload

'Insert into database
rs.AddNew 'FileId will be automatically handled by SQL
rs!File = strm.Read
rs!FileName = FileName
strm.Close
rs.Update
End If
End Sub
Private Sub btn_DwnldSQL_Click()
Dim cn, rs  As Object
Dim sql As String
Dim oStream As Object
Dim OutputPath, strCnxn, FileName, SaveLocation As String

OutputPath = "D:ExportTest"

'FileSystemObject to do so some file checks
Dim fso As Object
Set fso = VBA.CreateObject("Scripting.FileSystemObject")

'SQL Connection
Set cn = CreateObject("ADODB.Connection")
strCnxn = "Provider=sqloledb;" & _
"Data Source=MYSERVER;" & _
"Initial Catalog=MYDATABASE;" & _
"Integrated Security=SSPI;" 'Windows-Authentication

cn.Open strCnxn 

'your sql statment including varbinary max field here it is File
sql = " SELECT [File],[FileName] from [DemoFileStreamTable_1] "

'Recordset
Set rs = CreateObject("ADODB.Recordset")
rs.Open sql, cn

'Actual Download
Do Until rs.EOF 'Read all rows
Set oStream = CreateObject("ADODB.Stream")
FileName = CStr(rs.Fields("FileName").Value) 'FileName from Database field
SaveLocation = fso.BuildPath(OutputPath, FileName) 'Create outputpath
With oStream
.Type = 1 '1 - adTypeBinary
.Open
.Write rs.Fields("File").Value 'actual BLOB data
.SaveToFile SaveLocation, 2 '2 - adSaveCreateOverWrite
.Close
End With
Set oStream = Nothing
rs.MoveNext
Loop
rs.Close
cn.Close
End Sub
Function CustOpenFileDialog() As String 
Const msoFileDialogFilePicker As Long = 3
Dim objDialog As Object
Set objDialog = Application.FileDialog(msoFileDialogFilePicker)
Dim fso As Object
Set fso = VBA.CreateObject("Scripting.FileSystemObject")
Dim FileName As String
With objDialog
.AllowMultiSelect = False
' Set the title of the dialog box.
.Title = "Please select one file"
' Clear out the current filters, and add our own.
.Filters.Clear
.Filters.Add "supported Types", "*.pdf, *.xml, *.gltf, *.jpg, *.png"

' Show the dialog box. If the .Show method returns True, the
' user picked at least one file. If the .Show method returns
' False, the user clicked Cancel. 
If .Show = True Then
CustOpenFileDialog = .SelectedItems(1)
Else
CustOpenFileDialog = ""
End If
End With
End Function