Error While Connecting to Access DB

[Sachin Kendre]

I am trying to connect the access DB using UCanAccess but when I tried to connect it through Java program it gives me error as attached. I am creating connection as follows

public static Connection getConnection() throws Exception {

    String driver = "net.ucanaccess.jdbc.UcanaccessDriver";

    String dbName = "jdbc:ucanaccess://"+ "C:\\Study\\Swing\\TRS.accdb";
    String userName = "";
    String password = "";

    Class.forName(driver);
    Connection con = DriverManager
            .getConnection(dbName, userName, password);
    return con;

}

Also. when I click on OK and proceed to the Swing application, it loads the select command values but does not execute a query where I am looking for a count of records matching a perticular condition.

Any help will be greatly appreciated. If you need more info, please let me know .

Thank you.

[Gord Thompson]

Is the DriverManager.getConnection statement causing the error?

[Sachin Kendre]

Thank you for your response. I am really not sure but what surprises me is when I click on OK on the exeption message, it loads the initial data from the database but does not work for other query parts.

[Sachin Kendre]

Also the Exception is not getting caught in my try catch block so there is no trace for it at all.

[Sachin Kendre]

@Gord, I tried entering some sys outs and it looks like it is throwing that exception at DriverManager.getConnection. Is it something wrong that I am doing?

[Gord Thompson]

The folder into which you extracted the UCanAccess distribution (the bin.zip file) contains two batch files: console.bat for Windows, and console.sh for Unix/Linux/Mac. Run whichever one is appropriate, supply the full path to the database file when prompted, then paste the results into a post here. That might help us understand where your problem lies.

[Sachin Kendre]

Hello Gord,

Thanks for your response. Here is the result of the batch file

Please, enter the full path to the access file (.mdb or accdb): C:\Sachin\Study\ Swing\TRS.accdb
Error occured at the first loading attempt of Query3
Converted view was :CREATE VIEW QUERY3 AS SELECT Count(*) AS Expr1
FROM TRS
WHERE ((( PORJECT_NAME )='R&D') AND (( MONTH )='APRIL') AND (( YEAR )=2015))
Error message was :user lacks privilege or object not found: PORJECT_NAME
Loaded Tables:
COG_ID, IT_PROJECT_STATUS, ProjectList, TRS
Loaded Queries:

Loaded Indexes:
Index Unique on COG_ID Columns: (COGID)
, Primary Key on ProjectList Columns: (PROJECT_ID)
, Index on IT_PROJECT_STATUS Columns: (ACTIVITY_ID)
, Index on IT_PROJECT_STATUS Columns: (PROJECT_ID)

Cannot load view Query3 : user lacks privilege or object not found: PORJECT_NAME

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>

[Gord Thompson]

It looks like your database contains one or more queries that are invalid, either because they contain typographical errors or because they refer to columns/tables that no longer exist. In your case the table [TRS] does not contain a column named [PORJECT_NAME].

UCanAccess can continue to use other queries and tables in the database (as you have observed). It is just letting you know that it cannot use that particular query.

[Sachin Kendre]

In addition to that, I am trying to execute the query below where I am passing 2 parameters to the method and adding those values in where clause but it is not returning any result..

String checkStatus = "SELECT COUNT(*) FROM IT_PROJECT_STATUS WHERE PROJECT_ID = '"
+ project
+ "' "
+ " AND ACTIVITY_ID = '"
+ Act
+ "' "
+ "AND PROJECT_STATUS = 'o' AND ACTIVITY_STATUS = 'o'";

I am passing the project and activity code to the method and using them as values in where clause.

Sorry for bothering you much but really appreicate if you can help with it.

Thank you,
Sachin

[Gord Thompson]

Your code works okay for me. Check your data. (And consider using a PreparedStatement instead of relying on dynamic SQL.)

[Sachin Kendre]

Hello Gord,

Thanks for your response..!!

After going throgh the code I realised that one of the table I created for computing values was missing in my accdb. I have corrected that and it started working fine.

Also, as suggested, I used prepared statements and even the count query started working now.

Thank you so much for your help.