none
"Orphaned" BLOB data when writing BLOBs using OLE DB RRS feed

  • Question

  • OK, let's hope I've picked the right forum - apologies if I haven't!

    I'm trying to work with an Access database using OLE DB under VC++/VS2008/ATL, and I have a particular problem with BLOB data.

    If I have a BLOB data field, and try to delete the row which contains it, the actual data seems to get left in the mdb file (using something like HexEdit condfirms this).

    I've tweaked Microsoft's AOTBLOB OLE DB sample to try to get an insight into what is happening, and I've followed the sequence of operations suggested in ms716943 "Getting and Setting BLOB Data with Storage Objects".

    What I'm seeing is that if I store a BLOB field, then overwite it with another BLOB, or overwrite with a zero length BLOB, or with a null field, or delete the row, everything works as expected, but the mdb file just keeps growing and growing. I never seem to be able to remove the data from the actual mdb data - its as if I'm just deleting a pointer but not the data.

    Can anyone point me in the right direction?

    Many thanks
    Tony

     

    As so often happens, I think I've found the answer just after I posted the question!

    I gather that this isn't me doing anything wrong, it's a feature of the Jet engine that data gets orphaned, not deleted. The solution is to compact the database. There is a (hopefully) relevant MSDN article - Article ID: 230496, in case anyone is looking for an answer to the same problem. I'll go and try it out now ...

    Thanks for youre patience!

    Tony

    • Edited by Tony Hedge Friday, April 16, 2010 12:15 PM Found a possible solution
    • Moved by Barclay Hill Friday, April 16, 2010 5:36 PM Moved to a more appropriate forum (From:Visual Studio Database Development Tools (Formerly "Database Edition Forum"))
    Friday, April 16, 2010 11:11 AM

Answers

  • Yes, that's by design. database allow fragments in their data files. Sophisticated databases can reuse the empty space in new insert, simple databases require a purge.

    This question probably should be posted to one of the Microsoft Access developer forums.



    The following is signature, not part of post
    Please mark the post answered your question as the answer, and mark other helpful posts as helpful.
    Visual C++ MVP
    Friday, April 16, 2010 6:33 PM

All replies

  • Yes, that's by design. database allow fragments in their data files. Sophisticated databases can reuse the empty space in new insert, simple databases require a purge.

    This question probably should be posted to one of the Microsoft Access developer forums.



    The following is signature, not part of post
    Please mark the post answered your question as the answer, and mark other helpful posts as helpful.
    Visual C++ MVP
    Friday, April 16, 2010 6:33 PM
  • Thanks for the heplful reply, and thanks to Barclay for moving the post here!

    Regards

    Tony

    Monday, April 19, 2010 8:47 AM