UCAExc:::4.0.2 user lacks privilege or object not found: MSYSOBJECTS

[Summer]

Hi Team,

I am getting this error while trying to select objects(Macros, linked tables...etc) using select * from MsysObjects.

However, it throws an error: UCAExc:::4.0.2 user lacks privilege or object not found: MSYSOBJECTS

Is there a walk around to select the systemObjects info?

I have tried select from information_schema, however user_defiened functions etc... are not there.

Thanks,

Summer

[Gord Thompson]

You need to append ;sysSchema=true to your connection URL and then specify the sys. schema in your query, e.g.,

ResultSet rs = st.executeQuery("SELECT * FROM sys.MSysObjects");

[Summer]

Thank you very much Gord!!
It worked perfect!

Regards,
Summer

[Summer]

Hi Gord,

Do you have any idea if I use select * from information_schema, I am I able to list user defined StoredProcdures etc...?

[Mohammad Imran]

Hi @GordThompson: I am using same things but getting same error.

error is : org.hsqldb.HsqlException: user lacks priviledge or object not found: CAT_EVO_ADQUIRIDAS.

Please suggest what to do in this situation.

[Gord Thompson]

You can read from the INFORMATION_SCHEMA tables but be aware that UCanAccess uses HSQLDB as a backing database and those INFORMATION_SCHEMA tables will have a fair bit of HSQLDB-specific information in them.

[Summer]

Thanks Gord :)

If possible, could you please explain more about the HSQLDB as a backing database? Does that mean all data from MSACCESS are moved to HSQLDB? From my understading, MS Access only has Module, which includes both procedures and functions. Is that correct? If so, when I use INFORMATION_SCHEMA TABLE, what I can get will be all HSQLDB information rather than user_defined functions/procedures(i.e. modules) from MS Access.

Not sure if I understood correctly...

[Gord Thompson]

Does that mean all data from MSACCESS are moved to HSQLDB?

 
Yes, all table data (except BLOBs) is copied from the Access database file into an HSQLDB database. However,

From my understading, MS Access only has Module, which includes both procedures and functions. Is that correct?

 
VBA modules, yes. Not SQL stored procedures in the conventional sense.

If so, when I use INFORMATION_SCHEMA TABLE, what I can get will be all HSQLDB information rather than user_defined functions/procedures(i.e. modules) from MS Access.

 
That is correct. UCanAccess creates HSQLDB functions to support built-in Access functions like DateSerial but it does not create any user-defined procedures or functions from the VBA modules.

[Summer]

Thank you sooo much for your quick and very helpful answer!!

Very appreciate your help!

Cheers,
Summer