首页 文章

无法使用java在ms-access中创建自动增量主键字段

提问于
浏览
1

我需要创建一个带有自动增量主键列的表>我尝试使用以下代码,但它会抛出错误 .

My code:

CreateTableAccess.CreateAccessTable("CREATE TABLE D_Centre\n"
            + "(\n"
            + "    SlNo AUTOINCREMENT,\n"
            + "    CentreID VARCHAR,\n"
            + "    CentreName VARCHAR,\n"
            + "    [Createddate] DateTime,  \n"
            + "    CreatedBy VARCHAR, \n"
            + "    [Updateddate] DateTime\n"
           + ")", "D_Centre");


    public static void CreateAccessTable(String sqlscript, String tablename )
{
    String dbFileSpec = DataEntryScreen.homedirectory+"/"+"Dataentry.accdb";
    Connection conn = null ;
    try {
        conn = DriverManager.getConnection("jdbc:ucanaccess://" + dbFileSpec);
    } catch (SQLException ex) {
        Logger.getLogger(CreateTableAccess.class.getName()).log(Level.SEVERE, null, ex);
    }
        DatabaseMetaData dmd = null;
    try {
        dmd = conn.getMetaData();
    } catch (SQLException ex) {
        Logger.getLogger(CreateTableAccess.class.getName()).log(Level.SEVERE, null, ex);
    }
            ResultSet rs = null;
    try {
        rs = dmd.getTables(null, null, tablename , new String[]{"TABLE"});
    } catch (SQLException ex) {
        Logger.getLogger(CreateTableAccess.class.getName()).log(Level.SEVERE, null, ex);
    }
        // }
        {
        try {
            if (rs.next()) {
                System.out.println("Table ["+tablename+"] already exists.");
            } else {
                System.out.println("Table ["+tablename+"] does not exist.");
                Statement s = conn.createStatement();
                  s.executeUpdate(sqlscript);
                System.out.println("Table ["+tablename+"] created.");
            }
        } catch (SQLException ex) {
            Logger.getLogger(CreateTableAccess.class.getName()).log(Level.SEVERE, null, ex);
            System.err.println("Error in table creation  --> "+ex.toString());
        }
        try {
            conn.commit();
        } catch (SQLException ex) {
            Logger.getLogger(CreateTableAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
        try {
            conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(CreateTableAccess.class.getName()).log(Level.SEVERE, null, ex);
        }

}
}

Error: Nov 04,2014 3:39:28 PM org.DataEntryApplication.view.CreateTableAccess CreateAccessTable SEVERE:null net.ucanaccess.jdbc.UcanaccessSQLException:未找到类型或用户缺少权限:net.ucanaccess.jdbc.UcanaccessStatement.executeUpdate中的AUTOINCREMENT( UcanaccessStatement.java:164)org.DataEntryApplication.view.CreateTableAccess.CreateAccessTable(CreateTableAccess.java:53)org.DataEntryApplication.view.AccessProcess.accessProcess(AccessProcess.java:40)org.DataEntryApplication.view.DataEntryScreen.addComponentsToPane (DataEntryScreen.java:188)org.DataEntryApplication.view.DataEntryScreen.createAndShowGUI(DataEntryScreen.java:1092)atg.DataEntryApplication.view.Login $ 7.actionPerformed(Login.java:277)at javax.swing.AbstractButton.fireActionPerformed (AbstractButton.java:2018)javax.swing.AbstractButton $ Handler.actionPerformed(AbstractButton.java:2341)at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:402)at javax.swing.Defaul tButtonModel.setPressed(DefaultButtonModel.java:259)位于javax.swing.JComponent的java.awt.Component.processMouseEvent(Component.java:6505)的javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:252) .processMouseEvent(JComponent.java:3320)位于java.awt.Component的一个java.awt.Component上的java.awt.Component.processEvent(Component.java:6270)中的java.awt.Component . java:4861)at java.awt.Container.dispatchEventImpl(Container.java:2287)at java.awt.Component.dispatchEvent(Component.java:4687)at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4832)at at java.awt.LindweightDispatcher.processMouseEvent(Container.java:4492),java.awt.LightweightDispatcher.dispatchEvent(Container.java:4422),位于java.awt.Window的java.awt.Container.dispatchEventImpl(Container.java:2273) .dispatchEventImpl(Window.java:2719)位于java.awt.EventQueue.dispatchEventImpl(EventQueu)的java.awt.Component.dispatchEvent(Component.java:4687) e.java:735)java.awt.EventQueue.access $ 200(EventQueue.java:103)at java.awt.EventQueue $ 3.run(EventQueue.java:694)at java.awt.EventQueue $ 3.run(EventQueue . java:692)at java.security.ProtectionDomain的java.security.AccessController.doPrivileged(Native Method)java.security.ProtectionDomain $ 1.doIntersectionPrivilege(ProtectionDomain.java:76) .awt.EventQueue $ 4.run(EventQueue.java:708)位于java.security.ProtectionDomain $ 1的java.security.AccessController.doPrivileged(Native Method)的java.awt.EventQueue $ 4.run(EventQueue.java:706) . java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:242)中的java.awt.EventQueue.dispatchEvent(EventQueue.java:705)中的doIntersectionPrivilege(ProtectionDomain.java:76)位于java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java) :161)java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:150)at java.awt.EventDispatchThread.pumpEvent s(EventDispatchThread.java:146)java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:138)at java.awt.EventDispatchThread.run(EventDispatchThread.java:91)引起:java.sql.SQLException:未找到类型或者用户缺少权限:net.ucanaccess.jdbc.AbstractExecute.addDDLCommand(AbstractExecute.java:105)中的net.ucanaccess.jdbc.AbstractExecute.executeBase(AbstractExecute.java:127)的net.ucanaccess.jdbc.ExecuteUpdate.execute中的AUTOINCREMENT (ExecuteUpdate.java:56)在net.ucanaccess.jdbc.UcanaccessStatement.executeUpdate(UcanaccessStatement.java:162)... 41更多

表创建错误 - > net.ucanaccess.jdbc.UcanaccessSQLException:未找到类型或用户缺少权限:AUTOINCREMENT

1 回答

  • 3

    确保您使用的是最新版本的UCanAccess,可用here .

    我只是尝试了以下UCanAccess 2.0.9.1,它对我来说很好 .

    s.executeUpdate(
            "CREATE TABLE D_Centre (" +
                    "slNo AUTOINCREMENT PRIMARY KEY, " +
                    "CentreID VARCHAR(100) " +
                ")");
    

    Edit re: comment

    UCanAccess 2.0.9.1(及更高版本)也支持列的DEFAULT值 . 我刚测试了这个并且它有效:

    s.executeUpdate(
            "CREATE TABLE D_Centre (" +
                    "slNo AUTOINCREMENT PRIMARY KEY, " +
                    "Centre VARCHAR(100) DEFAULT 'foo' " +
                ")");
    

相关问题