MS Access Engine – The BLOB Fields #msaccess #blobfields #conversion

If you are using the Microsoft Access database engine to interrogate the RootsMagic SQLite database (see MS Access Engine and MS Access Engine – Event Query) then you will hit the problem that, on the face of it, Access does not ‘understand’ the BLOB fields within the SQLlite database. This wiki describes the workround that you can use to get this data deciphered.

The problem seems to be that the ODBC driver does not correctly interpret the contents of the BLOB field; perhaps because there are more options in SQLite for what might be held in a BLOB than there are in MS Access.

In some cases, if you develop a Query from within Access and run it it will object to even reading the records IF you have included BLOB fields in your output.

As long as you are not using the BLOB fields you will not have a problem; however these BLOB fields are used to hold a lot of interesting information including some of the narrative sentences used in many of the standard RM reports. So you will probably want to use this data sometimes.

There is a simple workround that runs along the same lines as the problem described in the introductory MS Access wikis concerning sorting and selection; that is to use a simple expression to convert the BLOB into a Text field.

For example, if you were looking at the EVENTTABLE and wanted to examine the Note field you could use the following Access expression to get the text diaplyed in a normal format:

Expr1: StrConv([Note],64)

This converts the Unicode content of the BLOB into normal text. The screen shot below shows how this Query might look.

AMCapture.JPG


And the scrrenshot below shows the output (note I have included a fourth column showing hte Note field in raw format). The field Expr1 is the converted output – you can replace ‘Expr1’ with any other name that you want the field to be known as:

ANCapture.JPG

LIMITATION. The StrConv function has a maximum output length of 255. So if the text contents of your BLOB field are longer than this it will be truncated. I think in most cases, for the purpose of ad hoc reporting, this should not be a problem.

Prev: MS Access Engine | MS Access Engine – Event Query | MS Access Engine – Using Excel | MS Access Engine – EXCEL Pivot Tables


Discussions & comments from Wikispaces site


ve3meo

Use sqlite CAST to convert to text

ve3meo
18 February 2010 04:21:49

Can you use the SQL CAST expression in MS Access to convert BLOB to text?

e.g.:

SELECT CAST(BLOBfield AS TEXT);

This may not be as limiting as MS Access’ StrCvrt().

Another thing I have observed when concatenating fields that, one of which, may contain NULL is the concatenated result may be NULL, even though there fields within that are NOT NULL. I use QUOTE() to put single quotes around and force the NULL field to return the string ‘NULL’. If concatenating a BLOB field, then:

SELECT QUOTE(CAST(BLOBfield AS TEXT));

These should work in MS Access as they are really operators/expressions passed through ODBC to the sqlite engine.


MarkVS

MarkVS
18 February 2010 08:57:51

Unfortunately this does not seem to work: it somes up as a syntax error. I have checked the HELP file too – there is no reference to the CAST parameter. So it looks like the JET engine cannot cope with this one. MVS.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.