How to configure/run SQL Queries of cache namespaces in MS Access

Question

Question

Virat Sharma · Jan 21, 2023

Hi All,

I am currently using D $SYSTEM.SQL.Shell() command or management portal to retrieve the data from database. I just learned we can also run cache table sql queries in MS access. MS access already installed on my machine.

I need to ask how I can connect my database of USER or sample namespace in MS access to run these SQL queries.

Thanks in advance!!

Product version: Caché 2017.1

Discussion (4)1

Log in or sign up to continue

Robert Cemper · Jan 21, 2023

MS Acess typically uses ODBC data sources.
By default, the ODBC35 driver for Cache/IRIS is installed at Cache/IRIS installation time.
You have to configure it (directly or from MS Access) with Namespace + SuperServer Port

Take care not to mix 32bit mode with 64bit mode depending on your Cache installation

1 0

score 1 · Answer 1 · 2023-01-21T09:22:39-05:00

Kamal Suri · Jan 21, 2023

You can also consider using DBeaver which connects using jdbc.

1 0

score 1 · Answer 2 · 2023-01-21T19:38:17-05:00

From very painful experience linking access to cache can be a very difficult proposition. In theory it's simple, an odbc link from access can read data fine from cache. However if you want to write anything back or perform updates of any kind it gets very finicky.

We ended up writing sql stored procedures in cache and calling them from access for any data modifications. So the linked odbc tables were only used for read only access.

Also be aware of large integer numbers, which need the bigint data type in access to display properly, which was only introduced in access 2019.

score 1 · Answer 3 · 2023-01-26T19:49:04-05:00

Just thought, I had a lot more success easily accessing CACHE ODBC tables via WinSQL.
The Lite version is free, and you'd just need that and the ODBC drivers and you'd have a simple way to access the data.