ucanaccess : unexpected token with spaces

Question

Today I facing issue with Access DB, Ucanaccess 3.0 and table names with spaces.

To explain : I have a huge query and ucanaccess throw this exception :

SQLException: UCAExc:::3.0.0 unexpected token: AIRCRAFT

So I investigate and try to isolate this problem and I found this :

This query throw the exception :

SELECT [Check Task Aircraft Listing].CTAL_Item_Number
,[Check Task Aircraft Listing].CTAL_Check
,[Check Task Aircraft Listing].CTAL_Delete
,[Check Task Aircraft Listing].CTAL_Effective
,[Check Task Aircraft Listing].CTAL_CheckSelect
,[Check Table].[Aircraft]
FROM [Check Table]
INNER JOIN [Check Task Aircraft Listing] ON [Check Table].CHECK = [Check Task Aircraft Listing].CTAL_Check
WHERE (
        (([Check Task Aircraft Listing].CTAL_Effective) = True)
        AND (([Check Table].[Aircraft]) = 'XXXX')
        )

I try to replace spaces with _ :

SELECT [Check_Task_Aircraft_Listing].CTAL_Item_Number
    ,[Check_Task_Aircraft_Listing].CTAL_Check
    ,[Check_Task_Aircraft_Listing].CTAL_Delete
    ,[Check_Task_Aircraft_Listing].CTAL_Effective
    ,[Check_Task_Aircraft_Listing].CTAL_CheckSelect
    ,[Check_Table].[Aircraft]
FROM [Check_Table]
INNER JOIN [Check_Task_Aircraft_Listing] ON [Check_Table].CHECK = [Check_Task_Aircraft_Listing].CTAL_Check
WHERE (
        (([Check_Task_Aircraft_Listing].CTAL_Effective) = True)
        AND (([Check_Table].[Aircraft]) = 'XXXX')
        )

And a new error is throw by Ucanaccess :

 SQLException: UCAExc:::3.0.0 user lacks privilege or object not found: CHECK_TASK_AIRCRAFT_LISTING

This is normal : this object doesn't exist. The firt exception diseapear so I think this is an issue but according to Ucanaccess website this should work:

Features :

• Square brackets for field names, table names and view names (Access Queries) including spaces (e.g., select [my column] from [my table] ).

Edit : After more investigation it seems like field/table names between bracket can contain one space but not more

Edit 2 :Clarification

I order to connect to access db I use a class named ConnectDB :

String url = "jdbc:ucanaccess://D:/ADEL/ADEL Local/ADEL_DATA.accdb";
            try
            {
                Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");

            }
            catch (java.lang.ClassNotFoundException e)
            {
                System.err.print("ClassNotFoundException: ");
                System.err.println(e.getMessage());
                ErrorFile.writeError(Thread.currentThread().getStackTrace()[2].getLineNumber(), e);
            }

            try
            {
                c = java.sql.DriverManager.getConnection(url);
            }
            catch (SQLException e)
            {
                System.err.println("SQLException: " + e.getMessage());
                ErrorFile.writeError(Thread.currentThread().getStackTrace()[2].getLineNumber(), e);
            }

See the following code to execute the query :

Connection c = ConnectDB.doConnect();
String selectString = "SELECT [Check Task Aircraft Listing].CTAL_Item_Number ,[Check Task Aircraft Listing].CTAL_Check ,[Check Task Aircraft Listing].CTAL_Delete ,[Check Task Aircraft Listing].CTAL_Effective ,[Check Task Aircraft Listing].CTAL_CheckSelect ,[CHECK TABLE].[Aircraft] FROM [CHECK TABLE] INNER JOIN [Check Task Aircraft Listing] ON [CHECK TABLE].CHECK = [Check Task Aircraft Listing].CTAL_Check WHERE ( (([Check Task Aircraft Listing].CTAL_Effective) = True) AND (([CHECK TABLE].[Aircraft]) = 'XXXX') ) ";
try
        {
            stmt = c.createStatement();
            
            ResultSet data = stmt.executeQuery(selectString);
            while (data.next())
            {...use data...}
}

A first query is successfully executed but the second query failed.

Answer 1

In fact your diagnosis is wrong(and the second attempt doesn't make sense because the error due to a wrong table name isn't a proof of something).

Also the number of white spaces is totally irrelevant. Another valid example may be:

select [my column bla bla bla] from [my table bla bla bla].

I've just tried your query on two tables named Check Task Aircraft Listing and Check Table (created with the columns specified in your query) and it works perfectly with the 3.0.0. Please help us to help you with an example that recreates your issue.

EDIT: Try to execute the following code on a new database without tables, and it will execute your query nicely after having created the tables needed:

package xxx;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class UcaT {

    /**
     * @param args
     * @throws SQLException 
     */
    public static void main(String[] args) throws SQLException {
        Connection c= DriverManager.getConnection("jdbc:ucanaccess://c:\\db\\new_database_without_tables.accdb");


        String selectString = "SELECT [Check Task Aircraft Listing].CTAL_Item_Number ,[Check Task Aircraft Listing].CTAL_Check ,[Check Task Aircraft Listing].CTAL_Delete ,[Check Task Aircraft Listing].CTAL_Effective ,[Check Task Aircraft Listing].CTAL_CheckSelect ,[CHECK TABLE].[Aircraft] FROM [CHECK TABLE] INNER JOIN [Check Task Aircraft Listing] ON [CHECK TABLE].CHECK = [Check Task Aircraft Listing].CTAL_Check WHERE ( (([Check Task Aircraft Listing].CTAL_Effective) = True) AND (([CHECK TABLE].[Aircraft]) = 'XXXX') ) ";
        try
                {
                    Statement stmt = c.createStatement();
                    stmt.execute("create table [Check Table] (id counter PRIMARY KEY,[CHECK] text,Aircraft text)");
                    stmt.execute("create table [Check Task Aircraft Listing] (id counter PRIMARY KEY,CTAL_Item_Number text,CTAL_Check text, CTAL_Delete text, CTAL_Effective yesno, CTAL_CheckSelect text)");
                    ResultSet data = stmt.executeQuery(selectString);
                    System.out.println("ok");

                }
        catch(Exception e){
            e.printStackTrace();
        }

    }



}