ODBC/OLEDB failure accessing MS Access "Attachment" field in query

[Joe]

We are experiencing failures when accessing a datatype="Attachments" field in
a query in an MS Access 2007 database using ACE ODBC or OLEDB drivers.

We are using an MS Access 2007 database
ACE ODBC/OLEDB drivers installed (i.e. Office 2007, which installs these
drivers)
DB contains a table with a field of type "Attachments" (which is new in MS
Access 2007)
DB contains a query that selects the fields of the above table
Using ACE ODBC or ACE OLEDB drivers to display the table works fine. The
"Attachments" field is displayed as the file name of the attachment(s).
However, using either ACE ODBC or ACE OLEDB to display the query (i.e. NOT
the table) results in either incorrect results or unexpected failures of the
ODBC/OLEDB drivers.

Error using ODBC (using "ODBC Test"):
=============================
select * from Query1 does not give an error, but displays a "1" for the
Attachment field.
select Attachments from Query1 gives the following error:
stmt: szSqlState = "HY000", *pfNativeError = -3087, *pcbErrorMsg = 97,
*ColumnNumber = -2, *RowNumber = -2
MessageText = "[Microsoft][ODBC Microsoft Access Driver] Reserved error (|);
there is no message for this error."

Error using OLEDB (using "RowSetViewer"):
================================
select* from Query1gives the followingerror:
Interface: Unknown
Result: 0x0004001 = E_NOTIMPL
FormatMessage: “Not implemented
File:
F:\Depot\SQLVault\mdac28sdk\Samples\oledb\rowsetviewer\SDK\obj\i386\CRowset.cpp
Line: 616

select Attachments from Query1 gives the following error:
Interface: IID_ICommand
Result: 0x0004005 = E_FAIL
IErrorInfo: [0x0000f3f1] “Unspecified error”
File:
F:\Depot\SQLVault\mdac28sdk\Samples\oledb\rowsetviewer\SDK\obj\i386\CCommand.cpp
Line: 439

If it would help to analyze the problem, I have a folder containing all
pertinent files (bare-bone database, tools, instructions) to reproduce this.
I could attach it as a ZIP file, if requested.

Thanks,

Joe

[Pak-Ming Cheung [MSFT]]

I can reproduce this issue and I have contacted the corresponding team. They
will have some in-depth investigation on this problem.

Ming.
MDAC Team, Microsoft.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

[Pak-Ming Cheung [MSFT]]

Here is the summary of the response from the corresponding product team:

- ACE ODBC driver has no support on complex data, such as attachment
- ACE OLEDB driver does not support complex data by default. However,
application can set the “Jet OLEDB:Support Complex Data=True” in connection
string to enable the support
- It is better to use Direct ADO, instead of ODBC / OLEDB, for those complex
data

====================
The second point above can be done by the following to create a connection
string:

Create a new ACCDB, then go to the VBA immediate window (CTRL+G) and type:
?CurrentProject.Connection

You should see something like this:

Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data
Source=C:\Users\JamesR\Documents\Database1.accdb;Mode=Share Deny Read|Share
Deny Write;Extended Properties="";Jet OLEDB:System
database=C:\Users\JamesR\AppData\Roaming\Microsoft\Access\System.mdw;Jet
OLEDB:Registry Path=Software\Microsoft\Office\12.0\Access\Access Connectivity
Engine;Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=6;Jet
OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet
OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet
OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet
OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica
Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=True

Please note the key-value pairs:
- Provider
- Jet OLEDB:Engine Type
- Jet OLEDB:Support Complex Data

USEFUL TIP:
Another trick I like to use for making exactly the connection string you
need is to create a text file on the desktop, then change the extension from
“*.TXT” to “*.UDL”. Then double click it to open it. A connection wizard
will open. Complete the wizard, the open the file in notepad. Viola! You
have your connection string.

Ming.
MDAC Team, Microsoft.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.