Neville
Silverman

Visual Basic Programmer, Sydney
Microsoft Access Database Programmer

Custom Built Software
System Optimisation
Office Automation
Phone Australia
(02) 9453-0456

Microsoft Access and Binary Large Objects (Blobs)

Blobs typically contain Microsoft Word DocumentsA Blob is binary data stored field in a Microsoft Access database as an OLE Object data type. Blobs typically contain Images, Microsoft Word Documents or Excel spreadsheets.

Microsoft Access can display Documents, Spreadsheets and Images – such as clip art, logos, and photos, in Forms and Reports. This facility can be very powerful for the small application.

But before venturing into the use of OLE Objects, make sure that you are aware of all the pitfalls. All of this applies equally to Memo fields as well.

The reasons for the use of Blobs:

  • Data consolidation – the database and the file object are all in one place
  • It is an integrated solution
  • Data integrity is assured – eliminates the possibility of orphan files
  • The Blob can be read from the database in one SQL statement

The reasons why not to use Blobs:

  • Blobs can swell up the size of a Table.
  • The Microsoft Access database size limit of 2 gigabytes is soon reached
  • The stored Blob is larger than the original file size
  • There is a large CPU and Memory overhead for every read or write
  • A read of a Table will always do disk I/O operations – there is no chance of finding a record in Memory
  • Older versions of Microsoft Office can cause problems
  • The user has to have the correct version of Microsoft Office installed
  • Server errors are not unusual on a busy system
  • There is an increase in Database fragmentation
  • Text searches take longer
  • The Microsoft Access Form can freeze while the Blob is being decoded
  • Backup times are longer
  • Extracting the original document from a Blob can be a nightmare

Microsoft Access without Blobs

  • A Microsoft Access system can handle more users
  • Reading a Microsoft Access Database is faster
  • Files maintained in a folder are more efficient and easily portable
  • Remote databases have been optimised for small amounts of data

SQL Server avoids Blob problems

SQL Server eliminates all the Blob issues with the "FILESTREAM" column type. The data is stored in a separate file on the database server and only an ID to the file is saved in the table.

The files are automatically included as part of the server backup, and the database and the files are never out of synchronisation.

What applies to SQL Server does not apply to Microsoft Access. SQL Server avoids all the performance issues inherent in Microsoft Access Database Blobs.

Summary of problems using Blobs

Blobs can bloat the Microsoft Access Database size and cause performance issues. Word documents, for example, are not small – even just a few paragraphs can average 20,000 bytes. Normally a 200-300 byte long record would be considered excessive.

For any system of reasonable size, the introduction of Blobs into a Microsoft Access database is a time bomb waiting to happen. Performance may be initially acceptable, but will soon degenerate as record volumes grow.

Due to the 2 GB limit, Blobs can force the splitting up of the Back-End database into multiple separate databases – with all the attendant complexity and inefficiency.