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.
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:
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
- Home
- A Query for a To Do List in a Grid Format
- Adding Virtual Columns to an RM4 Table
- MS Access Engine
- MS Access Engine – Event Query
- MS Access Engine – EXCEL Pivot Tables
- MS Access Engine – The BLOB Fields
- MS Access Engine – Using EXCEL
- MS Access, SQLite, and Don Quixote
- Questions about RM4 Tables
- Scope of Names in Queries and Sub-queries
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.