Visual Basic Programmer: Access database Programmer

Visual Basic Programmer
Microsoft Access Database Programmer

Custom Built Software

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

The Microsoft Access Database Application without Blobs

The Blobs are stored in a Windows folder. A field in a Table contains the Blob address.

  • The Database Application can handle more users
  • Reading from the Database is faster
  • Reading from a remote Database is much faster
  • Handling Blobs in a Windows folder is efficient and readily portable

The use of Blobs in a Database should be limited to exceptional occasions.

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 the address of 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 Application 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.