Using AppendChunk with ADO

[drew_s]

I am using an Access front end attached to a MSQL database and am trying to use "appendchunk" to copy
a photograph into a binary field in the database.

I am using the ADO process to do this and have created a recordset to locate the record (existing) into which
wish to save the photo.

This seems to work fine until I get to the line "T!Pic.appendchunk Datasource", where "T" is the recordset, "Pic"
is a field in the recordset and "datasource" is the binary data.

At this point I get the error message "operation not allowed in this context". I can only think that my reference to the field in
which the data is to be stored "T!Pic" is incorrect, but i am at a loss to know how to fix it.
Can anyone help

Relevent Code

Private Sub Command40_Click()
   Dim Source As String
   Dim T As ADODB.Recordset
   Set T = New ADODB.Recordset
   'Dim SField As String
   Dim SQLT As String
   'Dim F As String
   T.ActiveConnection = CurrentProject.Connection
   T.CursorType = adOpenKeyset
   T.LockType = adLockOptimistic
   
    Source = "C:\Documents and Settings\Owner\My Documents\My Pictures\QuickCam\Album\Pictures and Videos\Pictures\Drew.jpg"
    SQLT = "SELECT PID,Pic FROM tblNames Where PID = '1000'"
    'SField = "Pic"
    T.Open SQLT
  
          Dim NumBlocks As Integer, SourceFile As Integer, i As Integer
          Dim FileLength As Long, LeftOver As Long
          Dim FileData As String
          Dim RetVal As Variant
          
          SourceFile = FreeFile
          Open Source For Binary Access Read As SourceFile

          ' Get the length of the file.
          FileLength = LOF(SourceFile)
          If FileLength = 0 Then
              Exit Sub
          End If

          ' Calculate the number of blocks to read and leftover bytes.
          NumBlocks = FileLength \ Blocksize
          LeftOver = FileLength Mod Blocksize

          ' SysCmd is used to manipulate status bar meter.
          RetVal = SysCmd(acSysCmdInitMeter, "Reading BLOB", _
                   FileLength \ 1000)

          ' Read the leftover data, writing it to the table.
          FileData = String$(LeftOver, 32)
          Get SourceFile, , FileData
          T!Pic.AppendChunk (FileData)

          RetVal = SysCmd(acSysCmdUpdateMeter, LeftOver / 1000)

          ' Read the remaining blocks of data, writing them to the table.
          FileData = String$(Blocksize, 32)
          For i = 1 To NumBlocks
              Get SourceFile, , FileData
              T(SField).AppendChunk (FileData)

              RetVal = SysCmd(acSysCmdUpdateMeter, Blocksize * i / 1000)
          Next i

          ' Update the record and terminate function.
          T.Update
          RetVal = SysCmd(acSysCmdRemoveMeter)
          Close SourceFile
          Exit Sub
          Exit Sub
End Sub

 

[ReAn]

I don't think your RecordSet is Updatable. That might be why you cannot do it like that.

Not really sure, it's not my expertise, but just something I noticed.

 

[ChrisO]

I can’t really say why it won’t append but here are some thoughts.

I don’t normally use ADO but the following code does work and gets the BLOB into the table: -

Private Sub UploadFileToTable()
    Dim bytBuffer() As Byte
    Dim intFileNum  As Integer
    Dim T           As ADODB.Recordset
    
    Set T = New ADODB.Recordset
    
    T.ActiveConnection = CurrentProject.Connection
    T.CursorType = adOpenKeyset
    T.LockType = adLockOptimistic
    T.Open "SELECT PID,Pic FROM tblNames Where PID = '3000'"
    
    intFileNum = FreeFile
    
    Open "C:\RepositoryBE.mdb" For Binary As intFileNum
    
    ReDim bytBuffer(LOF(intFileNum))
    
    Get intFileNum, , bytBuffer
    
    T!Pic.AppendChunk bytBuffer
    
    T.Update
    Close intFileNum
        
End Sub

Note that AppendChunk can now use a Long so there is no need to chop the file into chunks.
It’s been that way since A97 but Micro$oft don’t seem to know it.
The test file I used, RepositoryBE.mdb, is 41Meg and it was loaded in 4 seconds.

At around 10Meg a second I doubt if you will need a progress bar.

Don’t know how you want to use it though.

Regards,
Chris.

 

[drew_s]

To ChrisO

Thanks for the suggestion but your code used in my circumstances comes up with the same error "operation not allowed in this context". I'm wondering if the field attributes for the recordset need to be set differently, but don't know how

 

[ChrisO]

Things to check…

Is PID a text field?
Is there a value of 1000 for PID in the table?
Is there a record returned?
And as ReAn suggested, can any other field be updated?