使用DAO代码将文件从Access上传到SQL Server-必需对象错误 [英] Uploading file from Access to SQL Server with DAO code - Object Required error
问题描述
我有一个Access 2016数据库用作SQL Server 2008 Express实例的前端. WATER_FILES表存在于SQL Server数据库中，并且包括类型为varbinary(MAX)的列Binary_File.
I have an Access 2016 database being used as the frontend for a SQL Server 2008 Express instance. The WATER_FILES table exists in the SQL Server database and includes a column Binary_File of type varbinary(MAX).
我正在尝试编写一个VBA子例程，该例程会将文件上传到该列中.以下代码会导致错误
I'm attempting to write a VBA sub routine that will upload a file into that column. The following code causes an error
必需对象
at runtime. This code is based on this incomplete page: https://msdn.microsoft.com/en-us/vba/access-vba/articles/work-with-attachments-in-dao#
我想念什么?
Dim dbsGMEC As DAO.Database
Dim rstWater_Files As DAO.Recordset
Set dbsGMEC = CurrentDb
Set rstWater_Files = dbsGMEC.OpenRecordset("dbo_WATER_FILES", dbOpenDynaset, dbSeeChanges)
' Unclear if a single record should be opened
' Dim strSQL As String
' strSQL = "SELECT * FROM dbo_WATER_FILES WHERE OBJECTID = '2'"
' Set rstWater_Files = dbsGMEC.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
' Activate edit mode.
rstWater_Files.Edit
' Instantiate the child recordset.
Dim rstFiles As DAO.Recordset
' THIS LINE GIVES THE ERROR "Runtime Error '424': Object Required"
Set rstFiles = rstWater_Files.Fields("Binary_File").Value
' Add a new attachment.
rstFiles.AddNew
rstFiles.Fields("FileData").LoadFromFile "C:\test.jpg"
rstFiles.Update
' Update the parent record
rstWater_Files.Update
推荐答案
Set关键字用于将变量设置为对象引用.您的.Value不是对象，它为Null.因此，需要对象错误.
The Set keyword is used for setting a variable to an object reference. Your .Value isn't an object, it's Null. Hence the Object Required error.
找到的代码用于Access附件字段.但是，Varbinary(Max)不是附件字段，而是映射到Access/DAO中的OLE对象.这意味着您需要将值设置为包含文件数据的字节数组，而不是使用嵌套的记录集来管理附件.
The code you've found is used for Access attachment fields. Varbinary(Max), however, is not an attachment field, but maps to an OLE object in Access/DAO. That means you need to set the value to a bytearray containing the file data, instead of using a nested recordset to manage attachments.
有很多方法可以将文件加载到字节数组中.我更喜欢以下使用ADODB.Stream对象的代码.
There are many ways to load a file into a bytearray. I prefer the following code, that uses an ADODB.Stream object.
Dim dbsGMEC As DAO.Database
Dim rstWater_Files As DAO.Recordset
Set dbsGMEC = CurrentDb
Set rstWater_Files = dbsGMEC.OpenRecordset("dbo_WATER_FILES", dbOpenDynaset, dbSeeChanges)
rstWater_Files.Edit
Dim strm As Object
Set strm = CreateObject("ADODB.Stream")
strm.Type = 1 'adTypeBinary
strm.Open
strm.LoadFromFile "C:\test.jpg"
rstWater_Files.Fields("Binary_File").Value = strm.Read
strm.Close
rstWater_Files.Update
要将其存储回文件中:
With CreateObject("ADODB.Stream")
.Type = 1 'adTypeBinary
.Open
.Write rstWater_Files.Fields("Binary_File").Value
.SaveToFile "C:\testcopy.jpg", 2 'adSaveCreateOverWrite
.Close
End With
如果您真的不喜欢ADODB，甚至想到了ADODB.Stream，您也可以使用VBA本身将文件读入字节数组:
If you really don't like ADODB, and even the thought of an ADODB.Stream disgusts you, you can also use VBA itself to read a file into a bytearray:
Dim dbsGMEC As DAO.Database
Dim rstWater_Files As DAO.Recordset
Set dbsGMEC = CurrentDb
Set rstWater_Files = dbsGMEC.OpenRecordset("dbo_WATER_FILES", dbOpenDynaset, dbSeeChanges)
rstWater_Files.Edit
Dim byteArr() As Byte
Dim fileInt As Integer: fileInt = FreeFile
Open "C:\test.jpg" For Binary Access Read As #fileInt
ReDim arr(0 To LOF(fileInt) - 1)
Get #fileInt, , byteArr
Close #fileInt
rstWater_Files.Fields("Binary_File").Value = byteArr
rstWater_Files.Update
最后一个代码将限制最大文件大小为2147483647647字节(Long的最大大小).但是，这也是Access数据库的最大大小，因此在此之前您可能会遇到麻烦.该代码也没有使用分块，因此它可能会使用比所需更多的内存.
This last code will cap the max file size of 2,147,483,647 bytes (the max size of a Long). However, this is also the max size of an Access database, so you'll likely run into trouble before that. This code also doesn't use chunking, so it might use more memory than required.
这篇关于使用DAO代码将文件从Access上传到SQL Server-必需对象错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
