Error opening a database with commas in table names

[logineimer]

Hello jamadei,

we use UCanAccess to deal with Access databases. One of our clients' databases can't be opened anymore caused by the following error which comes from LoadJet.exec():
java.sql.SQLSyntaxErrorException: unexpected token: , required: (

I debugged the error and found out that UCanAccess tries to execute the following statement:
CREATE CACHED TABLE FANCY_TABLE_T,015(FIELD1 VARCHAR(12),FIELD2 VARCHAR(26),FIELD3 DOUBLE)

Obviously, it's impossible to read a table with a comma included in its name. I proved it by renaming one table in order to remove the comma. Unfortunately, renaming all the tables is no option as there are many comma tables. Is there a way to fix this problem? Can I escape all tables names when opening the database?

BTW: I updated to UCanAccess 3.0.4 before testing.

Thanks in advance!

[Derek Visch]

Can you post the SQL query you're using to read the DB? Or atleast an excerpt?

I can verify this query works with a test db

SELECT *
FROM [comm,test];

[logineimer]

I think the query is unimportant as UCanAccess already fails to build the HSQL Mirror, doesn't it? Your solution seems to work as expected. Is there a way to put the table names in brackets when building the mirror like this: CREATE CACHED TABLE [FANCY_TABLE_T,015](FIELD1 VARCHAR(12),FIELD2 VARCHAR(26),FIELD3 DOUBLE)

[Gord Thompson]

This appears to be an issue when the table name contains a comma followed by numeric digits.

I was unable to reproduce the issue with a simple test case of an existing table named foo_bar,baz. However, when I renamed the table to foo_bar,015 then console.bat failed to load the database, reporting

net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::3.0.4 unexpected token: , required: (

[logineimer]

That's exactly the problem I encountered, too. I wonder if this is an UCanAccess issue..

[Marco Amadei]

Ok, it's a bug. To be fixed in the 3.0.5.

[logineimer]

Great! Thank you!

[Andrew Cunningham]

It would seem there is a problem with handling '-' in table names also. I'm using the latest release 3.0.7. Substituting '-' for something else, as suggested above, fixes the problem.

[Gord Thompson]

Unable to reproduce, even with hyphen followed by digits (eg., [table-123]). If possible please post an attachment that demonstrates the issue.

[Marco Amadei]

mmmmm, this works nicely: select [lic-eo] from [scuo-la];
Obviously '-' needs escaping, but I can't reproduce the problem. May you help me with more details?

[Andrew Cunningham]

After some further testing it looks like it is a linked table issue and has nothing to do with '-' in the table names. The database I'm connecting to has some linked tables, I've since learned the naming convention for these tables is to use a '-' in their names, for example 'Tbl-mass_data' would be a linked table but 'Tbl_mass_data' would be an actual table in the database. Connecting to the database that contains the actual table 'Tbl_mass_data' works fine but trying to extract data using the linked table 'Tbl-mass_data' fails in the other database. I think I can remember reading somewhere that ucanaccess won't work with linked tables, is that correct?

Thanks for the help. You guys are doing some great work here!

[Gord Thompson]

I think I can remember reading somewhere that ucanaccess won't work with linked tables, is that correct?

 
Not quite. UCanAccess can work with linked tables that point to native Access tables in another .accdb or .mdb file. What it cannot do is work with ODBC linked tables that point to tables on some other database platform like SQL Server, MySQL, etc..

So your issue is not with linked tables per se. To confirm, I just tested UCanAccess 4.0.0 against a database named "links.accdb" containing a linked table named [Tbl-mass_data] that points to a table named [Tbl_mass_data] in "tables.accdb". It worked fine:

C:\Users\Public\Downloads\UCanAccess>console.bat
Please, enter the full path to the access file (.mdb or accdb): C:\Users\Public\test\ucaLinkTest\links.accdb
Loaded Tables:
Tbl-mass_data
Loaded Queries:

Loaded Procedures:

Loaded Indexes:
Primary Key  on Tbl-mass_data Columns: (id)
, Index on Tbl-mass_data Columns: (id)

UCanAccess>
Copyright (c) 2012 Marco Amadei
UCanAccess version 4.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>select * from [Tbl-mass_data];
----------------------------
| id | description | mass |
----------------------------

| 1 | mass123 | 123 |

UCanAccess>

 
Note that if your issue is with the path to the linked database file then you may need to use the remap connection option.

[Andrew Cunningham]

Thanks very much for the response. I think you might be onto something with the remap suggestion. I'm running from Linux so it makes sense, and when I connect there are a lot of warnings about not being able to find things...seems obvious now!