我需要大約每周一次將檔案 (<10 MB) 上傳到同一網路中遠程服務器上的 SQL Server 2016 資料庫。到目前為止，這一切都在 Access FE/BE 中，但我想遷移到 SQL Server 作為后端。
我在 MS Access 中的附件現在需要在 SQL 資料庫上處理，因為我不想在檔案共享上執行此操作。
我從SQLShack中發現了很多關于使用類似這樣的東西的執行緒
DECLARE @File varbinary(MAX);
SELECT
@File = CAST(bulkcolumn AS varbinary(max))
FROM
OPENROWSET(BULK 'C:\sqlshack\akshita.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--呼叫失敗
其他簡單的“選擇”陳述句似乎作業，所以連接本身似乎沒問題。
所以我的問題是：
How can I perform such an upload from a local file on computer A to the remote SQL server on computer B (which cannot directly access this file) using MS Access as my frontend?
Is there a different way not using the "BULK" statement as I need "bulkadmin" rights for all users then?
uj5u.com熱心網友回復：
我可能使用@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
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/444371.html