Cannot access saved query objects that reference linked tables

[Hasan Rammaha]

Greetings!
I tested using ucanaccess in Lucee (Tomcat-based CFML engine). I can successfully access table objects and run queries as long as I join real tables in Access. However when I try to run a saved query named xyz within Access's "queries" tab, I get the error:
UCAExc:::4.0.4 user lacks privilege or object not found: XYZ. This error implies that the driver cannot access query objects directly as in sql "Select * from XYZ". I was able to do so in the past using ODBC. Is this a feature not implemented yet within the driver or am I doing something wrong? I am using (Access 2007 -20016 file format) - accdb.

Any insight would be greatly appreciated.
Sincerely
Hasan

[Gord Thompson]

UCanAccess certainly can work with saved SELECT queries as you've described, with some limitations. Try opening the .accdb file using console.bat (or console.sh) and see if it gives you any indication of why the saved query "xyz" is not being loaded, e.g.,

C:\Users\Public\Downloads\UCanAccess>dir
 Volume in drive C is OS
 Volume Serial Number is F424-D565

 Directory of C:\Users\Public\Downloads\UCanAccess

2018-04-09  17:22    <DIR>          .
2018-04-09  17:22    <DIR>          ..
2018-04-09  15:33               654 console.bat
2018-04-09  15:35               461 console.sh
2015-10-20  20:02               558 copyright.txt
2018-04-09  17:23    <DIR>          lib
2017-12-12  09:06    <DIR>          licenses
2017-12-12  09:06    <DIR>          loader
2018-04-09  16:12           386,403 ucanaccess-4.0.4.jar
2018-04-09  15:37                16 version.txt
               5 File(s)        388,092 bytes
               5 Dir(s)  298,998,185,984 bytes free

C:\Users\Public\Downloads\UCanAccess>console.bat
java version "10.0.2" 2018-07-17
Java(TM) SE Runtime Environment 18.3 (build 10.0.2+13)
Java HotSpot(TM) 64-Bit Server VM 18.3 (build 10.0.2+13, mixed mode)

Please, enter the full path to the access file (.mdb or .accdb): C:\Users\Public\UCanAccessTest.accdb
Error occured at the first loading attempt of xyz
Converted view was :CREATE VIEW XYZ AS SELECT Supplier.*
FROM Supplier
Error message was :user lacks privilege or object not found: SUPPLIER

[Hasan Rammaha]

Thank you Gord for your prompt response!
I did run Console.bat as you prescribed, I got the same error "user lacks privilege or object not found: XYZ", but this time the Console debug messages revealed more helpful info. As I mentioned earlier, XYZ is a saved query, but what I failed to mention is that XYZ joins 2 "Linked" tables, not local tables. Those 2 linked tables are linked to 2 csv files. When I run XYZ from within Access I get results just fine.
The Console showed that it "loaded" a third local table but not the 2 linked tables which XYZ uses. That gave me the impression that may be the driver does not handle linked tables as local tables. So to test this assumption, I converted the 2 linked tables to local tables and ran the XYZ query internally within Access, and it ran successfully. I ran the Console.abt again and it showed that it loaded the 2 converted files, but the saved XYZ was not loaded, and gave me the same error of "User lacks privilege or object not found:XYZ". So I am back where I started.
Below is output of the running Console.bat. Note that XYZ is the same as DistinctFullJoin query. I used XYZ term as a short for DistinctFullJoin for the purpose of reporting this issue

C:\Users\rammahah\Downloads\UCanAccess-4.0.4-bin\UCanAccess-4.0.4-bin>console.bat
java version "1.8.0_121"
Java(TM) SE Runtime Environment (build 1.8.0_121-b13)
Java HotSpot(TM) 64-Bit Server VM (build 25.121-b13, mixed mode)

Please, enter the full path to the access file (.mdb or .accdb): C:\apps\webapps\deltrack\deltrack.accdb
Error occured at the first loading attempt of DeliveriesRequiringInvoice
Converted view was :CREATE VIEW DELIVERIESREQUIRINGINVOICE AS SELECT *
FROM DistinctFullJoin
WHERE invoice is NULL and "POD STATUS" = 'C'
ORDER BY "SALES ORDER", line
Error message was :user lacks privilege or object not found: DISTINCTFULLJOIN
Loaded Tables:
Config, deliveries, sales
Loaded Queries:

Loaded Procedures:

Loaded Indexes:
Primary Key  on Config Columns: (key)

Cannot load view DoncoOpenSales : user lacks privilege or object not found: DISTINCTFULLJOIN
Cannot load view DeliveryScheduleForCustomerX : user lacks privilege or object not found: DISTINCTFULLJOIN
Cannot load view DistinctFullJoin : user lacks privilege or object not found: SELECT SALES.[PRODUCT CATEGORY
Cannot load view DeliveriesRequiringInvoice : user lacks privilege or object not found: DISTINCTFULLJOIN
Cannot load view EstimatedSampleCompletionDate : user lacks privilege or object not found: DISTINCTFULLJOIN
Cannot load view InvoiceHistory : user lacks privilege or object not found: DISTINCTFULLJOIN
UCanAccess>
Copyright (c) 2018 Marco Amadei
UCanAccess version 4.0.4
You are connected!!
Type quit to exit

Commands end with ;

Use:
   export [--help] [--bom] [-d <delimiter>] [-t <table>] [--big_query_schema <pathToSchemaFile>] [--newlines] <pathToCsv
>;
for exporting the result set from the last executed query or a specific table into a .csv file

UCanAccess>

The saved query xyz (i.e. DistinctFullJoin) is below...

SELECT DISTINCT x.*
FROM (SELECT sales.[Product Category], sales.[Sold To], sales.[Sold To Customer], sales.[Ship To Customer], sales.[Purchase Order Number], sales.[SO Entered], sales.[PO Date], sales.[Sales Order], sales.Line, sales.[Material Number], sales.[Material Description], sales.[Order Qty], sales.[Shipped Qty], sales.[Remaining Qty], sales.[Confirmed Qty], sales.[Scheduled Ship Date],
deliveries.[Ship Qty], deliveries.[Ship Date], deliveries.Invoice, deliveries.[POD Status], deliveries.[Outbound Delivery], DateAdd("d",14,sales.[Scheduled Ship Date]) AS EstimatedSampleCompletionDate, sales.[Net Value]

FROM sales LEFT JOIN deliveries ON (sales.[Sold To] = deliveries.[Sold To ]) AND (sales.Line = deliveries.Line) AND (sales.[Sales Order] = deliveries.[Sales Order])
WHERE sales.[Sold To] = (SELECT value from Config where key='CustomerCode')

UNION ALL

SELECT sales.[Product Category], deliveries.[Sold To], deliveries.[Sold To Customer], deliveries.[Ship To Customer], deliveries.[Purchase Order Number], deliveries.[SO Entered], deliveries.[PO Date], deliveries.[Sales Order], deliveries.Line, deliveries.[Material Number], deliveries.[Material Description], deliveries.[Order Qty], sales.[Shipped Qty], deliveries.[Remaining Qty], sales.[Confirmed Qty], deliveries.[Scheduled Ship Date], deliveries.[Ship Qty], deliveries.[Ship Date], deliveries.Invoice, deliveries.[POD Status], deliveries.[Outbound Delivery], DateAdd("d",14,deliveries.[Ship Date]) AS EstimatedSampleCompletionDate, deliveries.[Net Value]

FROM sales RIGHT JOIN deliveries ON (sales.[Sold To] = deliveries.[Sold To ]) AND (sales.Line = deliveries.Line) AND (sales.[Sales Order] = deliveries.[Sales Order])
WHERE deliveries.[Sold To] = (SELECT value from Config where key='CustomerCode')

) AS x;

The above query runs just fine within Access.
Much obliged,
Hasan

[Hasan Rammaha]

Hi Gord. I think I narrowed the issue down to Linked tables vs. Local tables. I managed to rewrite my saved query and got it to run from Lucee. However it worked only when the saved query joined Local tables. When the tables were Linked the saved query could not be run via the driver and gave the usual error of "User lacks privilege..etc".
For future releases of Ucanaccess, would it be possible to "Load" Linked tables by the driver and be treated similarly as Local tables?
Best regards
Hasan

[Gord Thompson]

Hi Hasan. UCanAccess can work with persistent linked tables that point to physical Access tables in a different .accdb/.mdb file. What it cannot currently do is work with linked tables that point to

• Excel workbooks,
• .csv/.txt files, or
• ODBC data sources

 
Handling those kinds of linked tables is on our wish list, but we have no ETA on when that might happen.

[Hasan Rammaha]

Thank you Gord! This explains the issue for me. I will be looking forward to the enhancements in future releases.
Best regards,
Hasan

[Aishvarya kapoor]

Hey @gordonthompson, was this feature ever added to UCanAccess to read ODBC Linked tables directly via driver?
I am trying to connect to a legacy system which only support ODBC via Java. Since there is no longer a direct ODBC support for Java, I was doing a work around to link the ODVC source to MS Access via a live link and read using MS Access. I stumbled upon the issue that UCanAccess is limited to reading linked tables.
Also if this is not on roadmap, can you recommend if there is a way to refresh ODBC data daily in MS Access if I create static tables instead of Linking.

[Carlos Coronel]

I got a similar problem. In the website says that ucanaccess supports running I am trying to run a saved query in Access but got the same error "User lacks privilege or object not found". All tables used in query are local to the database file. Can ucanAccess use saved queries or not? if so how? in the sourceforge page it says "You can execute Select queries defined and saved in Access." Can you show me how to do this?