Hello,
When working on a linked database, I get a warning: "Cannot resolve db link", and then an exception: "user lacks privilege or object not found".
The same code works fine on the original database.
I'm connecting to the database with default properties. Is there something I am doing wrong ?
Thanks in advance for your answers.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
net.ucanaccess.jdbc.UcanaccessSQLException: user lacks privilege or object not found: T_ANIMAL
at net.ucanaccess.jdbc.UcanaccessStatement.executeQuery(UcanaccessStatement.java:202)
at inra.sicpa.importbc.dao.SqlObjects.executeQuery(SqlObjects.java:89)
at inra.sicpa.importbc.dao.AbstractDao.executeQuery(AbstractDao.java:71)
at inra.sicpa.importbc.dao.DaoAladinOperations.getCorrespondancesAnimaux(DaoAladinOperations.java:79)
at inra.sicpa.importbc.Main.correspondanceAnimal(Main.java:370)
at inra.sicpa.importbc.Main.main(Main.java:125)
Caused by: java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: T_ANIMAL
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
at org.hsqldb.jdbc.JDBCStatement.executeQuery(Unknown Source)
at net.ucanaccess.jdbc.UcanaccessStatement.executeQuery(UcanaccessStatement.java:199)
... 5 more
Caused by: org.hsqldb.HsqlException: user lacks privilege or object not found: T_ANIMAL
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.SchemaManager.getTable(Unknown Source)
at org.hsqldb.ParserDQL.readTableName(Unknown Source)
at org.hsqldb.ParserDQL.readTableOrSubquery(Unknown Source)
at org.hsqldb.ParserDQL.XreadTableReference(Unknown Source)
at org.hsqldb.ParserDQL.XreadFromClause(Unknown Source)
at org.hsqldb.ParserDQL.XreadTableExpression(Unknown Source)
at org.hsqldb.ParserDQL.XreadQuerySpecification(Unknown Source)
at org.hsqldb.ParserDQL.XreadSimpleTable(Unknown Source)
at org.hsqldb.ParserDQL.XreadQueryPrimary(Unknown Source)
at org.hsqldb.ParserDQL.XreadQueryTerm(Unknown Source)
at org.hsqldb.ParserDQL.XreadQueryExpressionBody(Unknown Source)
at org.hsqldb.ParserDQL.XreadQueryExpression(Unknown Source)
at org.hsqldb.ParserDQL.compileCursorSpecification(Unknown Source)
at org.hsqldb.ParserCommand.compilePart(Unknown Source)
at org.hsqldb.ParserCommand.compileStatements(Unknown Source)
at org.hsqldb.Session.executeDirectStatement(Unknown Source)
at org.hsqldb.Session.execute(Unknown Source)
... 8 more
But the problem is at the connection. When creating the tables, in com.healthmarketscience.jackcess.impl.DatabaseImpl.lookupTable, both linkedDbName and linkedTableName are null.
I have also done a quick test : I've created a small mdb database, and then a linked database, and used the console.bat tool on them.
Here is the result on the original database :
avr. 16, 2015 9:15:30 AM com.healthmarketscience.jackcess.impl.IndexData setUnsupportedReason
AVERTISSEMENT: unsupported collating sort order SortOrder[1036(0)] for text index, making read-only
avr. 16, 2015 9:15:30 AM com.healthmarketscience.jackcess.impl.DatabaseImpl readSystemCatalog
INFOS: Could not find expected index on table MSysObjects
Loaded Tables:
TABLE1, TABLE2
Loaded Queries:
Loaded Indexes:
Primary Key on TABLE1 Columns: (ID)
, Primary Key on TABLE2 Columns: (ID)
and the result on the linked database :
avr. 16, 2015 9:16:43 AM com.healthmarketscience.jackcess.impl.IndexData setUnsupportedReason
AVERTISSEMENT: unsupported collating sort order SortOrder[1036(0)] for text index, making read-only
avr. 16, 2015 9:16:43 AM com.healthmarketscience.jackcess.impl.DatabaseImpl readSystemCatalog
INFOS: Could not find expected index on table MSysObjects
WARNING:Cannot resolve db link
WARNING:Cannot resolve db link
Loaded Tables:
The issue appears to be related to the collation sort order of the database. When I run console.bat on the original "Test_link2.accdb" I get
C:\Users\Public\Java\UCanAccess>console.bat
Please, enter the full path to the access file (.mdb or accdb): C:\__tmp\Pierre\Test\Test_link2.accdb
Apr 20, 2015 5:47:22 AM com.healthmarketscience.jackcess.impl.IndexData setUnsupportedReason
WARNING: unsupported collating sort order SortOrder[1036(0)] for text index, making read-only
Apr 20, 2015 5:47:22 AM com.healthmarketscience.jackcess.impl.DatabaseImpl readSystemCatalog
INFO: Could not find expected index on table MSysObjects
WARNING:Cannot resolve db link
WARNING:Cannot resolve db link
Loaded Tables:
...
If I change the "New database sort order" to "General" in Access (File > Options) and do a Compact and Repair Database then the "Cannot resolve db link" warnings go away and I get "file does not exist" warnings instead (because the links point to a file that does not exist on my machine):
C:\Users\Public\Java\UCanAccess>console.bat
Please, enter the full path to the access file (.mdb or accdb): C:\__tmp\Pierre\Test\Test_link2.accdb
WARNING:External file C:\Users\pichalier\Desktop\BioControl_Tests\Aladin\Test.mdb does not exist
WARNING:given file does not exist: C:\Users\pichalier\Desktop\BioControl_Tests\Aladin\Test.mdb
WARNING:External file C:\Users\pichalier\Desktop\BioControl_Tests\Aladin\Test.mdb does not exist
WARNING:given file does not exist: C:\Users\pichalier\Desktop\BioControl_Tests\Aladin\Test.mdb
Loaded Tables:
...
If I change the collation sort order back to "French" then the "Cannot resolve db link" warnings return.
Last edit: Gord Thompson 2015-04-20
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Executing statement - select * from dbo_VW_PTB_DIAG
ERROR UCAExc:::3.0.0 user lacks privilege or object not found: DBO_VW_PTB_DIAG
I have compacted/repaired the database with no success at all.
I have downloaded and installed UcanAccess 3.0.0 that comes with Jackcess 2.1.2, prior to that I had been using ucanaccess-2.0.9.5 with Jackcess 2.1.0. In both cases I have adjusted classpath variable and verified which driver is active. No success so far
Thanks
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
But It may be different from the original issue reported in this thread. Please, open the db with the console.bat and then copy and poste the output here.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Please, enter the full path to the access file (.mdb or accdb): \server_name\share_name\Databases\goanywhere.accdb
WARNING:P:\PHI_Intergy\Flat File Export\CSS (Access is denied)
Loaded Tables:
dbo_VW_PTB_DIAG, Table1
Loaded Queries:
Table1 Query
Loaded Indexes:
Primary Key on Table1 Columns: (ID)
Cannot resolve table CSS_Weekly_Extract
UCanAccess>
Copyright (c) 2012 Marco Amadei
UCanAccess version 3.0.0
You are connected!!
Type quit to exit
Commands end with ;
use:
export <pathToCsv>;
for exporting into a .csv file the result set from the last executed query
UCanAccess>
Comments:
Linked Table CSS_Weekly_Extract is associated with text file in P:\PHI_Intergy\Flat File Export\CSS folder
dbo_VW_PTB_DIAG is also a Linked table
None of those Tables can be queried.
Let me know if I should open a new discussion thread.
Thank you
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
After using network paths we can query linked Access tables but still unable to query linked text files.
C:\Program Files\Linoma Software\GoAnywhere\userdata\lib\ucanaccess-3.0.0.jar" net.ucan
access.console.Main
Please, enter the full path to the access file (.mdb or accdb):\server_name\share_name\Databases\goanywhere.accdb
WARNING:\server_name\share_name\Flat File Export\CSS (Access is denied)
Error occured at the first loading attempt of Query_CSS
Converted view was :CREATE VIEW QUERY_CSS AS SELECT CSS_Extract.*
FROM CSS_Extract
Error message was :user lacks privilege or object not found: CSS_EXTRACT
Loaded Tables:
dbo_VW_PTB_DIAG, Table1
Loaded Queries:
Table1 Query
Loaded Indexes:
Primary Key on Table1 Columns: (ID)
Cannot load view Query_CSS : user lacks privilege or object not found: CSS_EXTRACT
Cannot resolve table CSS_Extract
UCanAccess>
Copyright (c) 2012 Marco Amadei
UCanAccess version 3.0.0
You are connected!!
dbo_VW_PTB_DIAG is Linked Access Table that can be queried.
CSS_Extract is a linked text file and cannot be queried yet
Both were linked using the network path
Using the console what would be the way of calling the remap jdbc parameter?
Thanks
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Ucanaccess doesn't support text files. I think it will be one of the next extension points. You could pass a parameter to the console, let me know if it's still useful at this point.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I appreciate all of the help. I will wait for a version that could connect to tables linked from either text files or MS SQL Server. We have built a lot of logic in MS Access that connects to linked text files or SQL Tables linked via ODBC.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hello,
When working on a linked database, I get a warning: "Cannot resolve db link", and then an exception: "user lacks privilege or object not found".
The same code works fine on the original database.
I'm connecting to the database with default properties. Is there something I am doing wrong ?
Thanks in advance for your answers.
Hi Pierre,
something strange. May you send along the Exception stack?
Hi, sorry for the late answer.
Here is the exception stack :
net.ucanaccess.jdbc.UcanaccessSQLException: user lacks privilege or object not found: T_ANIMAL
at net.ucanaccess.jdbc.UcanaccessStatement.executeQuery(UcanaccessStatement.java:202)
at inra.sicpa.importbc.dao.SqlObjects.executeQuery(SqlObjects.java:89)
at inra.sicpa.importbc.dao.AbstractDao.executeQuery(AbstractDao.java:71)
at inra.sicpa.importbc.dao.DaoAladinOperations.getCorrespondancesAnimaux(DaoAladinOperations.java:79)
at inra.sicpa.importbc.Main.correspondanceAnimal(Main.java:370)
at inra.sicpa.importbc.Main.main(Main.java:125)
Caused by: java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: T_ANIMAL
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
at org.hsqldb.jdbc.JDBCStatement.executeQuery(Unknown Source)
at net.ucanaccess.jdbc.UcanaccessStatement.executeQuery(UcanaccessStatement.java:199)
... 5 more
Caused by: org.hsqldb.HsqlException: user lacks privilege or object not found: T_ANIMAL
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.SchemaManager.getTable(Unknown Source)
at org.hsqldb.ParserDQL.readTableName(Unknown Source)
at org.hsqldb.ParserDQL.readTableOrSubquery(Unknown Source)
at org.hsqldb.ParserDQL.XreadTableReference(Unknown Source)
at org.hsqldb.ParserDQL.XreadFromClause(Unknown Source)
at org.hsqldb.ParserDQL.XreadTableExpression(Unknown Source)
at org.hsqldb.ParserDQL.XreadQuerySpecification(Unknown Source)
at org.hsqldb.ParserDQL.XreadSimpleTable(Unknown Source)
at org.hsqldb.ParserDQL.XreadQueryPrimary(Unknown Source)
at org.hsqldb.ParserDQL.XreadQueryTerm(Unknown Source)
at org.hsqldb.ParserDQL.XreadQueryExpressionBody(Unknown Source)
at org.hsqldb.ParserDQL.XreadQueryExpression(Unknown Source)
at org.hsqldb.ParserDQL.compileCursorSpecification(Unknown Source)
at org.hsqldb.ParserCommand.compilePart(Unknown Source)
at org.hsqldb.ParserCommand.compileStatements(Unknown Source)
at org.hsqldb.Session.executeDirectStatement(Unknown Source)
at org.hsqldb.Session.execute(Unknown Source)
... 8 more
But the problem is at the connection. When creating the tables, in com.healthmarketscience.jackcess.impl.DatabaseImpl.lookupTable, both linkedDbName and linkedTableName are null.
I have also done a quick test : I've created a small mdb database, and then a linked database, and used the console.bat tool on them.
Here is the result on the original database :
avr. 16, 2015 9:15:30 AM com.healthmarketscience.jackcess.impl.IndexData setUnsupportedReason
AVERTISSEMENT: unsupported collating sort order SortOrder[1036(0)] for text index, making read-only
avr. 16, 2015 9:15:30 AM com.healthmarketscience.jackcess.impl.DatabaseImpl readSystemCatalog
INFOS: Could not find expected index on table MSysObjects
Loaded Tables:
TABLE1, TABLE2
Loaded Queries:
Loaded Indexes:
Primary Key on TABLE1 Columns: (ID)
, Primary Key on TABLE2 Columns: (ID)
and the result on the linked database :
avr. 16, 2015 9:16:43 AM com.healthmarketscience.jackcess.impl.IndexData setUnsupportedReason
AVERTISSEMENT: unsupported collating sort order SortOrder[1036(0)] for text index, making read-only
avr. 16, 2015 9:16:43 AM com.healthmarketscience.jackcess.impl.DatabaseImpl readSystemCatalog
INFOS: Could not find expected index on table MSysObjects
WARNING:Cannot resolve db link
WARNING:Cannot resolve db link
Loaded Tables:
Loaded Queries:
Loaded Indexes:
Cannot resolve table Table1
Cannot resolve table Table2
Could you upload the test database files that you created so we might try to recreate your issue?
Here are the files :
- Test.mdb : original database
- Test_link1.mdb : linked database
- Test_link2.accdb : linked database
Thanks Pierre.
The issue appears to be related to the collation sort order of the database. When I run
console.baton the original "Test_link2.accdb" I getIf I change the "New database sort order" to "General" in Access (File > Options) and do a Compact and Repair Database then the "Cannot resolve db link" warnings go away and I get "file does not exist" warnings instead (because the links point to a file that does not exist on my machine):
If I change the collation sort order back to "French" then the "Cannot resolve db link" warnings return.
Last edit: Gord Thompson 2015-04-20
Tried with the Test_link2.accdb.
You have just to use the MS Access Compact and Repair tool on this db: its metadata are corrupted.
Hi Marco. The collation sort order of the database file also seems to be involved. See my reply to Pierre, above.
Thank you both for helping me understand the strange world of Access...
It works better now !
https://sourceforge.net/p/jackcess/bugs/123/
The issue will be fixed in the Jackcess 2.1.1 release.
I have been unable to query any linked table.
The error message is
Executing statement - select * from dbo_VW_PTB_DIAG
ERROR UCAExc:::3.0.0 user lacks privilege or object not found: DBO_VW_PTB_DIAG
I have compacted/repaired the database with no success at all.
I have downloaded and installed UcanAccess 3.0.0 that comes with Jackcess 2.1.2, prior to that I had been using ucanaccess-2.0.9.5 with Jackcess 2.1.0. In both cases I have adjusted classpath variable and verified which driver is active. No success so far
Thanks
But It may be different from the original issue reported in this thread. Please, open the db with the console.bat and then copy and poste the output here.
Please, enter the full path to the access file (.mdb or accdb): \server_name\share_name\Databases\goanywhere.accdb
WARNING:P:\PHI_Intergy\Flat File Export\CSS (Access is denied)
Loaded Tables:
dbo_VW_PTB_DIAG, Table1
Loaded Queries:
Table1 Query
Loaded Indexes:
Primary Key on Table1 Columns: (ID)
Cannot resolve table CSS_Weekly_Extract
UCanAccess>
Copyright (c) 2012 Marco Amadei
UCanAccess version 3.0.0
You are connected!!
Type quit to exit
Commands end with ;
use:
export <pathToCsv>;
for exporting into a .csv file the result set from the last executed query
UCanAccess>
Comments:
Linked Table CSS_Weekly_Extract is associated with text file in P:\PHI_Intergy\Flat File Export\CSS folder
dbo_VW_PTB_DIAG is also a Linked table
None of those Tables can be queried.
Let me know if I should open a new discussion thread.
Thank you
Use remap jdbc parameter to remap to real network path of the db.
After using network paths we can query linked Access tables but still unable to query linked text files.
C:\Program Files\Linoma Software\GoAnywhere\userdata\lib\ucanaccess-3.0.0.jar" net.ucan
access.console.Main
Please, enter the full path to the access file (.mdb or accdb):\server_name\share_name\Databases\goanywhere.accdb
WARNING:\server_name\share_name\Flat File Export\CSS (Access is denied)
Error occured at the first loading attempt of Query_CSS
Converted view was :CREATE VIEW QUERY_CSS AS SELECT CSS_Extract.*
FROM CSS_Extract
Error message was :user lacks privilege or object not found: CSS_EXTRACT
Loaded Tables:
dbo_VW_PTB_DIAG, Table1
Loaded Queries:
Table1 Query
Loaded Indexes:
Primary Key on Table1 Columns: (ID)
Cannot load view Query_CSS : user lacks privilege or object not found: CSS_EXTRACT
Cannot resolve table CSS_Extract
UCanAccess>
Copyright (c) 2012 Marco Amadei
UCanAccess version 3.0.0
You are connected!!
dbo_VW_PTB_DIAG is Linked Access Table that can be queried.
CSS_Extract is a linked text file and cannot be queried yet
Both were linked using the network path
Using the console what would be the way of calling the remap jdbc parameter?
Thanks
Ucanaccess doesn't support text files. I think it will be one of the next extension points. You could pass a parameter to the console, let me know if it's still useful at this point.
I appreciate all of the help. I will wait for a version that could connect to tables linked from either text files or MS SQL Server. We have built a lot of logic in MS Access that connects to linked text files or SQL Tables linked via ODBC.
Just hit the same issue trying to query an Access table linked to a text file. Any idea when this extension might be added?