Spatialite, MS Access, ODBC: rtree snag

[Alex Z]

Now that ArcGIS Pro supports editing geopackages, we finally have a potential workaround for not being able to use personal geodatabases any longer (ESRI stopped supporting these mdb databases with ArcMap). I have downloaded and installed the 32 bit sqlite ODBC drivers from here. Once I made an ODBC connection to my geopackage, I was able to link my polygon layer in Access and view the data. Super easy! But then I tried to update a row and got the infamous error like "function not found st_(can't remember)" 

Some Googling informed me that I needed to dynamically load the spatialite module, so I added this to the "load extensions" text box in the ODBC driver: C:\spatialite\mod_spatialite.dll. This is the directory in which I unpacked the spatialite binaries. I also created a PATH entry to C:\spatialite.

Now when link a table into Access and try to update a row with geometry, I get the following instead of the error above: "no such module RTREE". So it appears I did load spatialite correctly. When read about RTREE, I learned that it has to be enabled in the sqlite3.exe when it's compiled? Is that correct? Is there no way to dynamically load the rtree functionality when creating the ODBC connection like I did with the spatialite module? 

I think the ODBC driver is using the sqlite3.exe executable that I unpacked into its default directory: C:\Program Files (x86)\SQLite ODBC Driver. And I'm assuming that it does not include the rtree functions (it's only 62kb). So I renamed it to sqlite3_orig.exe and then copied the sqlite3.exe file that I downloaded into the ODBC driver directory. I got the same rtree error (I was pretty surprised that it opened the gpkg table at all!). Then I deleted the sqlite3.exe all together and the table still opened (so maybe the sqlite3.exe isn't needed at all). 

So then I copied all of the spatialite files to the ODBC directory and overwrote whatever files may be there. I know that was risky, but I can reinstall the ODBC drivers if needed.

Then I also renamed sqlite3.exe in C:\Windows\System32 to old_sqlite3.exe. Yeah, I know that was a bit risky too. I can still read the stable in Access. I just can't update a row (same error no such module rtree).

We use Access to link to many different tables and database system through ODBC. Access's graphical query builder is very helpful, as are stored queries, macros, forms and reports! Plus, ArcGIS Pro can be horribly slow with joins and updates when tables get large (even with properly indexed columns). Thats when I typically turn to SQL to get things done faster. Has anybody had any success with editing sqlite/gpkg tables with ODBC and Access? (We just update some of the non-spatial columns, I'd never try to edit the geometry outside of ArcGIS/QGIS).

Thanks for any insight!

Alex

[a.fu...@lqt.it]

On Mon, 19 Oct 2020 13:08:05 -0700 (PDT), 'Alex Z' via SpatiaLite Users
wrote:

> Now when link a table into Access and try to update a row with
> geometry, I get the following instead of the error above: "no such
> module RTREE". So it appears I did load spatialite correctly. When
> read about RTREE, I learned that it has to be enabled in the
> sqlite3.exe when it's compiled? Is that correct? Is there no way to
> dynamically load the rtree functionality when creating the ODBC
> connection like I did with the spatialite module?
>

Hi Alex,

when building libsqlite3 supporting the RTREE is a configurable
option (enabled by default in all recent versions).
once that the library has been built disabling the option there
is absolutely no way to reanable it at run-time, because all
the relevant code has been ignored during the compilation.

> I think the ODBC driver is using the sqlite3.exe executable that I

> unpacked into its default directory: C:Program Files (x86)SQLite ODBC

> Driver. And I'm assuming that it does not include the rtree functions
> (it's only 62kb). So I renamed it to sqlite3_orig.exe and then copied
> the sqlite3.exe file that I downloaded into the ODBC driver
> directory.
> I got the same rtree error (I was pretty surprised that it opened the
> gpkg table at all!). Then I deleted the sqlite3.exe all together and
> the table still opened (so maybe the sqlite3.exe isn't needed at
> all).
>

I strongly doubt that the ODBC driver depends on sqlite3.exe
it looks more realistic assuming that a copy of libsqlite3 is
statically linked to the driver itself. or, may be, it depends
on same specific DLL.
if this is your case, there is no way to circumvent the issue if
not by recompiling the ODBC driver starting from the source code
after enabling the RTREE module.

alternatively, there are several open source sqlite ODBC drivers,
such as https://github.com/softace/sqliteodbc

bye Sandro

[Alex Z]

Thanks for the reply, Sandro. You confirmed what I suspected: it rtree must be compiled into the exe's and dll's. But if it's enabled by default, I wonder why those producing the driver seem to have disabled it. I've never worked with C or C++, so trying to compile it myself would probably be pretty impractical.

Speaking of the driver, I am using the pre-compiled driver that's linked in the GitHub page that you suggested. So I think that's a dead end for me. But you mentioned that there are "are *several* open source sqlite ODBC drivers". I've done some pretty extensive Googling to find them, but the only one I keep finding is http://www.ch-werner.de/sqliteodbc/sqliteodbc.exe.  Do you have direct links to the other OS sqlite ODBC drivers?  They may have all of the functionality that I need. Thanks!

I'm so close to making my future workflows feasible!

Alex

[a.fu...@lqt.it]

On Tue, 20 Oct 2020 05:46:07 -0700 (PDT), 'Alex Z' via SpatiaLite Users
wrote:

> But you mentioned that there are "are *several* open
> source sqlite ODBC drivers". I've done some pretty extensive Googling
> to find them, but the only one I keep finding is

> http://www.ch-werner.de/sqliteodbc/sqliteodbc.exe [2]. Do you have

> direct links to the other OS sqlite ODBC drivers? They may have all
> of
> the functionality that I need.
>

Hi Alex,

you are right; I was confused by the many indirect cross references,
but at the end all them points to just 3 ODBC drivers for SQLite:

1. https://www.devart.com/odbc/sqlite/
Proprietary: but a 30 days free trial version seems to be available

2. https://github.com/softace/sqliteodbc

3. http://www.ch-werner.de/sqliteodbc/

bye Sandro

[Christopher Mumford]

Alex Z-- Did you ever succeed in linking MS Access to a Geopackage?  If so, I would very much appreciate hearing the details for how you did it. Thanks! 

[Christopher Mumford]

Did you succeed in creating an ODBC link that allows gpkg attribute data to be edited from MS Access.