MS Office 12.0 Access Database Engine OLE DB Provider driver assigns different data type to text columns, compared with Jet 4 driver

Question

Summary: A Crystal report is connected to an Access database (amongst others), using a SQL query. One of the columns consists of an expression that is exposed in Crystal as a "TEXT [255]" field when connected to the Access database using the Jet4 DAO driver, but as "Memo" when connected using the Microsoft Office 12.0 Access Database Engine OLE DB Provider. This causes downstream problems.

Details: I have a Crystal report which consumes data from two different MS Access databases, using separate SQL queries defined in the report. The results are then combined using a table link (a table link is similar to a table join, but operates across tables from different databases, and is processed by Crystal itself, as opposed to running server-side).

The table link is based (on the one side) on a calculated column ("ColumnToLinkOn") in the SQL query, which looks like this (anonymized):

SELECT
Column1 AS Heading1,
Column2 as Heading2,
Left(Column2,6) AS ColumnToLinkOn -- Table link based on this column
FROM NameOfTable

The data type of Column2 in Access is Short Text (as Access 2013 calls it - previously this was known as Text).

Now for the interesting bit: when the connection from Crystal to the Access database is of type Jet4 (DAO), then the data type of ColumnToLinkOn is String [255]. However, if the connection is changed to use the Microsoft Office 12.0 Access Database Engine OLE DB Provider, then Crystal picks it up as Memo (we change the connection type because we need it to work in 32 and 64 bit mode, but there is no 64 bit Jet4 driver). The problem with this is that table links cannot be based on Memo fields, so that renders the link invalid.

My question: How can I alter the SQL query to cast the data type to a normal text field, so that it can be used in table links? According to the Access documentation, the LEFT function returns Variant (String), which I suspect causes the problem. The source column is not of type memo, so it is clearly the LEFT function that introduces the problem.

I've tried wrapping the result in CStr, like this: CStr(Left(Column2,6)) AS ColumnToLinkOn, but this has made no difference. I also found a hint that said that doing this Replace(Left(Column2,6), "", "") AS ColumnToLinkOn will help, but it didn't. Unfortunately Access also doesn't support the CAST(column AS type) syntax.

I'm using Access 2013 and Crystal Reports 2013.

Any assistance will be greatly appreciated!

I have zero experience with CR. But what happens if you use Nz or Left$? Or concatenate with a string: "" & Left(Column2,6) AS ColumnToLinkOn? — Gustav
Thanks for the questions, Gustav. Trying Nz(Left(Column2,6)) AS ColumnToLinkOn or Left(Nz(Column2),6) AS ColumnToLinkOn fails with "Undefined function 'NZ' in expression." Will try your other suggestions next. — R Hoffmann
Unfortunately, Left$(Column2,6) AS ColumnToLinkOn does not make a difference. Neither does "" & Left(Column2,6) AS ColumnToLinkOn - the result still comes across as Memo. — R Hoffmann
Perhaps CR caches something. What happens if you alter field/table names, like: Left(Column2,6) AS TestColumnToLinkOn FROM NameOfTable As TestTable? — Gustav
A long shot, but the functions Lcase and Ucase return strings. You could try one of these in place of the CStr wrapper. — jhTuppeny

david david · Accepted Answer · 2016-04-12T03:44:23

If the records are distinct, you can do it like this:

SELECT DISTINCT
Column1 AS Heading1,
Column2 as Heading2,
Left(Column2,6) AS ColumnToLinkOn -- Table link based on this column
FROM NameOfTable

Access can't match on memo fields either, so it has to truncate memo fields to shorttext to find distinct elements.

Another way would be use a Crystal subreport, and link to a formula field on the subreport instead of linking to the table.

MS Office 12.0 Access Database Engine OLE DB Provider driver assigns different data type to text columns, compared with Jet 4 driver

1 Answers