support for "ALTER TABLE " & "Select * from oldTable into newTable"

[Julia]

Hi, I have an app that used JDBC ODBC bridge to access MS Access. When I updated to use UCanAccess, anywhere I have "Alter table" to create primary key or "select * from oldTable into newTable" to create a new table failed with error "feature not supported". I downloaded version 2.0.9.3. I was wondering what is the plan to support these two features? Are there any alternative that I could use? Or am I missing something? Thanks!

[Marco Amadei]

Hi Julia,
the underlying library jackcess doesn't currently implement ALTER TABLE.
Should they ever implement it, I will support related DDL in UCanAccess.
About your SQL issue, you should use:
INSERT INTO T1 (CL1,CL2) SELECT C1, C2 FROM T2;
Cheers Marco

[Gord Thompson]

Hi Julia.

You might not (currently) be able to execute ALTER TABLE statements via UCanAccess but if your Java application is running under Windows then you could have your Java code create a little VBScript to add the Primary Key constraints you need.

There is an example here that uses ACE/Jet DAO, and that could certainly get the job done, but the same general approach could be used with an ODBC connection (from the VBScript) and the same ALTER TABLE statements that you have used before.

[Gord Thompson]

Hi Julia. Regarding your requirement to create a new table by SELECTing from an existing table:

While UCanAccess does not (currently) support the Access SQL syntax

SELECT ID, LastName 
INTO newTable 
FROM People

you can use this instead

CREATE TABLE newTable 
AS (SELECT ID, LastName FROM People) 
WITH DATA