CODEWITHSUNDEEP
javams-access
Raju
Raju

Reputation: 53

Syntax error while creating table from another table

My code:

    Statement stmt=null;
    String cmdstr = "create table " + tableName + " as (select * from Master_Sheet);";

        try{            
            stmt = con.createStatement();
            stmt.executeUpdate(cmdstr);

            }
        catch(Exception e)
            {
            e.printStackTrace();
            }
        finally
        {
            try{
                if(stmt != null)
                stmt.close();
            }
            catch(Exception e)
            {
                e.printStackTrace();
            }
        }

Output:

java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error in CREATE TABLE statement. at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source) at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)

Please help, i am very new to java coding.

Upvotes: 3

Views: 406

Answers (2)

Renaud Bompuis
Renaud Bompuis

Reputation: 16806

For Access, the syntax for creating a new table based on the data of a query is:

SELECT INTO newTable
FROM oldTable;

So your code should be rewritten as:

String cmdstr = "insert into table " + tableName + " From Master_Sheet;";

Make sure that your SQL statement follow the MS Access syntax before you use them in your java code.

Upvotes: 3

T.J. Crowder
T.J. Crowder

Reputation: 1075537

You're asking Access to create a table using this SQL statement:

create table yourTableName as (select * from Master_Sheet);

(including the ; at the end). It's telling you that's not valid syntax for a CREATE TABLE statement. This isn't a Java thing, it's an Access thing. See the linked CREATE TABLE documentation. (I linked the latest, be sure to find the one for the version of Access you're using.)

Upvotes: 2

Related Questions