Access 2007，VBA:将BLOB插入链接表时出现奇怪的错误 [英] Access 2007, VBA: Strange error when inserting BLOB into a linked table

问题描述

尝试将二进制文件插入varbinary列时出现一个奇怪的错误.架构如下:

I have a strange error when trying to insert binary into a varbinary column. Architecture is as follows:

具有此表对象的SQL Server 2008数据库:

SQL Server 2008 database with this table object:

CREATE TABLE [dbo].[BLOBs] (
    [FileName] nvarchar(128) NOT NULL
    , [FileExt] AS  CASE
                        WHEN CHARINDEX(N'.', [FileName]) > 0 THEN REVERSE(SUBSTRING(REVERSE([FileName]), 1, CHARINDEX('.', REVERSE([FileName])) - 1))
                        ELSE NULL
                    END PERSISTED
    , [FileBLOB] [varbinary](max) NOT NULL
    , CONSTRAINT [PK_BLOBs] PRIMARY KEY CLUSTERED ( [FileName] ASC ) ON [DEFAULT]
) ON [DEFAULT];
GO

此表作为表BLOBs链接到MS Access 2007应用程序，表BLOBs包含此快速"脏的书面模块:

This table is linked to a MS Access 2007 Application as table BLOBs containing this "quick'n dirty" written module:

Public Function saveBLOB(strFQFN As String) As Long
10      If HandleErrors() Then On Error GoTo ERR_HANDLING
20      ErrorHandler().CallStack.PushCallStack "saveBLOB('" & strFQFN & "')"

        Dim db  As DAO.Database
        Dim rs  As DAO.Recordset
        Dim fs  As Long
        Dim fn  As String
        Dim hdl As Integer
        Dim blob()  As Byte

30      fn = Right(strFQFN, Len(strFQFN) - InStrRev(strFQFN, "\"))

        ' read data from file
40      hdl = FreeFile()
50      Open strFQFN For Binary Access Read As #hdl
60      fs = LOF(hdl) - 1
70      ReDim blob(fs)
90      Get #hdl, , blob

        ' updateblob data in database
100     Set db = CurrentDb()
110     Set rs = db.OpenRecordset("BLOBs", dbOpenDynaset, dbSeeChanges)
120     rs.FindFirst ("[FileName] = '" & fn & "'")
130     If rs.NoMatch Then
140         rs.AddNew
150         rs!FileName = fn
160     Else
170         rs.Edit
180     End If

190     rs!FileBLOB = blob
200     rs.Update

210     saveBLOB = True

CLEANUP:
8000    Close #hdl
8010    On Error Resume Next
8020    rs.close
8030    Set rs = Nothing
8040    Set db = Nothing
8050    Erase blob

FINALLY:
9000    On Error GoTo 0
9010    ErrorHandler().CallStack.PopCallStack
9020    Exit Function

ERR_HANDLING:
9900    ErrorHandler().handleError ErrSinkScreen, "saveBLOB()"
9910    saveBLOB = False
9920    Resume CLEANUP
End Function

顺便说一句:ODBC驱动程序是SQL Server版本6.01.7601.17514(SQLSRV32.DLL，2010年11月21日).

By the way: ODBC driver is SQL Server Version 6.01.7601.17514 (SQLSRV32.DLL, 21.11.2010).

只要我通过即时窗口调用该函数，一切都可以很好:

Everythings works fine as long as I call the function via the immediate windows:

? saveBLOB("U:\example.txt")
-1

blob数据已成功保存.现在，这个小子的形式是:

The blob data is successfully saved. Now there is this little sub in a form:

Private Sub UploadFile()
10      If HandleErrors() Then On Error GoTo ERR_HANDLING
20      ErrorHandler().CallStack.PushCallStack Me.Name & ".UploadFile()"

30      If (Not saveBLOB(Nz(Me!txtFQFN, vbNullString))) Then _
            MsgBox "Beim Speichern des BLOBs in der Datenbank ist ein Fehler aufgetreten.", vbExclamation

FINALLY:
9000    ErrorHandler().CallStack.PopCallStack
9010    Exit Sub

ERR_HANDLING:
9900    ErrorHandler().handleError ErrSinkDatabase + ErrSinkScreen, Me.Name & ".UploadFile()"
9910    Resume FINALLY
End Sub

如果从此处调用saveBLOB()，则会发生错误(您试图将Null值分配给非Variant数据类型的变量.):

If saveBLOB() is called from here an error occurs (You tried to assign the Null value to a variable that is not a Variant data type.):

更新:这似乎是一个大小问题.插入了一个新创建的8KB的Excel文件.导入681KB的文件失败. Blob列为varbinary(max).根据docs.microsoft.com:

UPDATE: It seems to be a question of size. Insert of a newly created Excel file with 8KB worked. Import of a file with 681KB failed. Blob column is varbinary(max). According to docs.microsoft.com:

max 表示最大存储大小为2 ^ 31-1字节. 因此681KB应该完全适合.

max indicates that the maximum storage size is 2^31-1 bytes. So 681KB should fit perfectly in.

非常感谢您！

推荐答案

有最大数据量，您可以在一个语句中添加/编辑.请参阅

There is a max size of data,you can add/edit in one statement. See Configure the max text repl size Server Configuration Option.

要绕开，请使用对于类似sql服务器上的操作，我更喜欢Adodb，因为OleDb驱动程序在服务器和 Adodb.Stream 简化了二进制读/写.

For operations like that on a sql server, I would preferAdodb, as OleDb driver provides direct sql execution on server and Adodb.Stream simplifies binary read/write.

使用ADODB的saveBLOB函数(后期绑定):

saveBLOB function using ADODB (late-bound):

Public Function saveBLOB(strFQFN As String) As Long

With CreateObject("Scripting.FileSystemObject")  
    Dim fn as String
    fn = .GetFileName(.GetFile(strFQFN))
End With

Dim cnn As Object
Set cnn = CreateObject("ADODB.Connection")
With cnn
    Dim ConString As String
    'uses msoledbsql_18.3 as povider
    ConString = "Provider=MSOLEDBSQL;Data Source=server\instance;Integrated Security=SSPI;Persist Security Info=False;Trusted_Connection=yes;Initial Catalog=db;"
    .Mode = 3 'adModeReadWrite
    .CursorLocation = 3 'adUseClient
    .Open ConString
End With

Dim cmd As Object
Set cmd = CreateObject("ADODB.Command")
With cmd
    .ActiveConnection = cnn
    .CommandText = "SELECT Filename, FileBLOB FROM blob WHERE FiLENAME = ?"
    .Parameters.Append .CreateParameter("FilterFilename", 200, 1, 200, fn)

    With CreateObject("ADODB.Recordset")
        .LockType = 3 'adLockOptimistic
        .Open cmd, , 3 'adOpenDynamic


            Set Stream = CreateObject("ADODB.Stream")
            Stream.Type = 1
            Stream.Open
            Stream.LoadFromFile strFQFN
            If .EOF Then
                .AddNew
                .Fields("Filename") = fn
            End If
            .Fields("FileBLOB").AppendChunk Stream.Read
            .Update
        End If
    End With
End With

saveBLOB = True

Set cmd = Nothing
Set Stream = Nothing
cnn.Close
Set cnn = Nothing
End Function

这篇关于Access 2007，VBA:将BLOB插入链接表时出现奇怪的错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！