ODBC from MS-Accesss gives error ((-7701) and (-7702))

[yoasty]

I use odbc connections to sqlite (work flawlessly) and would like to add connections to duckdb.
Has anybody got the connections working flawlessly.

On trying to connect as an external table I get "Reserved error (-7701); there is no message for this error." and if I create a DSN I get "Reserved error (-7702); there is no message for this error."

I have tried installing the latest MS-C-re-distributable and under "language settings " ticking the utf-8 experimental setting. I have also changed system settings for region and language to English-US to minimize the risks.

but with all settings test-odbc gives the same results.

test_odbc
[6/31] (19%): Test SQLConnect and SQLDriverConnect

test_odbc is a Catch v2.13.7 host application.
Run with -? for options

-------------------------------------------------------------------------------
Test SQLConnect and SQLDriverConnect
-------------------------------------------------------------------------------
D:\a\duckdb-odbc\duckdb-odbc\test\tests\connect.cpp(132)
...............................................................................

D:\a\duckdb-odbc\duckdb-odbc\test\tests\connect.cpp(132): FAILED:
  {Unknown expression after the reported line}
due to unexpected exception with message:
  Could not find storage_version.db file.

[29/31] (93%): Test SQLColAttribute for a query that returns an interval        SQLColAttribute: Success with info
[30/31] (96%): Test SQLColAttribute for a query that returns a uuid             SQLColAttribute: Success with info
[31/31] (100%): Test SQLColAttribute for a query that returns a uuid
===============================================================================
test cases:    31 |    30 passed | 1 failed
assertions: 45429 | 45428 passed | 1 failed

-----------------------------------------------------------------------
and test-connections: 
-----------------------------------------------------------------------

test_connection_odbc.exe
[0/1] (0%): Test SQLConnect with Ini File                                       SQLExecDirect (FROM string_values): Error: Error

test_connection_odbc.exe is a Catch v2.13.7 host application.
Run with -? for options

---

Test SQLConnect with Ini File

D:\a\duckdb-odbc\duckdb-odbc\test\tests\connect_with_ini.cpp(9)
...............................................................................

D:\a\duckdb-odbc\duckdb-odbc\test\common.cpp(27): FAILED:
REQUIRE( (((ret)&(~1))==0) )
with expansion:
false

[1/1] (100%): Test SQLConnect with Ini File

test cases: 1 | 1 failed
assertions: 6 | 5 passed | 1 failed

[MorsRene]

Same problem for me

[yoasty]

I can add here that Excel / External Data / ODBC does not stop with error-messages, but fails to present a list of tables to choose from. So there is not much one can do with the DSN nor with making a connection to a file.

[HildebKa]

Same issues:

• ODBC from MS-Accesss gives error ((-7701) and (-7702))
• Excel / External Data / ODBC does always connect to :menory:, database-file is ignored.

[staticlibs]

With the fix proposed in #63 it allows the Access to get from this error:

a little bit further. It still fails when trying to do the schema introspection:

More fixes are needed for Access (Microsoft Jet engine) specific problems, but such fixes can be largely lifted from the Postgres ODBC driver, that includes the detection and specific branching logic for Jet - link.