1 2 Previous Next 17 Replies Latest reply: Feb 7, 2008 1:08 AM by 807601 RSS

    how can I open Ms access file in my java program

    807601
      Hi..I am new to this community and new to java....I am in the middle of a project (database is ms access and front end is java).....the table names are displayed in a combo box...when I click on a name that table should be displayed in the in raw form i.e as in the ms access...and I should be able to update it and save changes...please help
        • 1. Re: how can I open Ms access file in my java program
          807601
          first you must connect your program to ms access. Have you done this already?
          • 2. Re: how can I open Ms access file in my java program
            807601
            yes I have connected my program with the database but i am unable to display the table the database table in the JTable which I have used in the form ...I am unable to get the names of the columns and all those stuffs required to display the table when only table name is known
            • 3. Re: how can I open Ms access file in my java program
              807601
              Using DatabaseMetaData you will get table names.
              http://java.sun.com/j2se/1.4.2/docs/api/java/sql/DatabaseMetaData.html
              ResultSet rs = dbmd.getTables(String catalog, String schemaPattern, String tableNamePattern, String[] types);
              
              DatabaseMetaData dbmd = conn.getMetaData(); 
              ResultSet rs = dbmd.getTables(null, null, "%", null); 
              while (rs.next()) {
                    System.out.println(rs.getString(3));// Print
                  }  
              rs.close();
              Using ResultSetmetadata you will get column information
              http://java.sun.com/j2se/1.4.2/docs/api/java/sql/ResultSetMetaData.html
              ResultSet rs = stmt.executeQuery("SELECT * FROM tableName");
              
              ResultSetMetaData rsmd = rs.getMetaData();
              for(int i=0;i<rsmd.getColumnCount();i++) {
                    System.out.println(rsmd.getColumnName(i));// Print
              
              }
              Hope this helps :)
              • 4. Re: how can I open Ms access file in my java program
                807601
                I am not able to use it properly.
                In my project I am displaying table types in the combo box and corresponding to each
                type I have a table name written in my database...I am retreiving the table name and
                storing it in a variable but when try to execute any query using that variable it is not working
                its giving "incomplete query clause"

                try {
                //String tableName= "";
                String tableType=(String) comboTableType.getSelectedItem();
                con=null;
                qryStmt=null;

                con=ClassDB.getConnection();
                qryStmt=con.createStatement();
                String qry="Select table_name from cptms_master_table_mtb where table_type='"+tableType+"' ";
                rs=qryStmt.executeQuery(qry);
                while(rs.next())
                {
                String tableName= rs.getString("table_name");

                String qrycalltable="select * from '"+tableName+"' ";
                stm=con.createStatement();
                rs=stm.executeQuery(qrycalltable);

                while(rs.next())
                {
                ResultSetMetaData rsmd = rs.getMetaData();
                for(int i=0;i<rsmd.getColumnCount();i++) {
                System.out.println(rsmd.getColumnName(i));
                }
                }

                can anybody help me
                • 5. Re: how can I open Ms access file in my java program
                  807601
                  Hi
                  i can simply say u , just use type 1 driver
                  • 6. Re: how can I open Ms access file in my java program
                    807601
                    import javax.swing.JFrame;
                    import javax.swing.JComboBox;
                    
                    import javax.swing.JPanel;
                    import javax.swing.JScrollPane;
                    import javax.swing.JTable;
                    import javax.swing.table.DefaultTableModel;
                    
                    import java.util.Vector;
                    import java.awt.event.ActionEvent;
                    import java.awt.event.ActionListener;
                    import java.awt.BorderLayout;
                    import java.awt.Container;
                    
                    import java.sql.DatabaseMetaData;
                    import java.sql.ResultSetMetaData;
                    import java.sql.ResultSet;
                    import java.sql.Connection;
                    import java.sql.Statement;
                    import java.sql.SQLException;
                    import java.sql.DriverManager;
                    
                    public class DatabaseSurfer extends JFrame implements ActionListener {
                           private JTable table;
                           private DefaultTableModel tableModel;
                           private JComboBox cmbTables;
                           private Vector tableNameList,rows,columns,temp;
                           private JScrollPane scrollPane;
                           private JPanel northPanel;
                           private Container cont;
                           private DatabaseMetaData dbmd;
                           private ResultSetMetaData rsmd;
                           private Connection con=null;
                    
                        public DatabaseSurfer() {
                               super("Database Surfer");
                               setSize(800,600);
                               initComponents();
                               setExtendedState(JFrame.MAXIMIZED_BOTH);
                               setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
                               setVisible(true);
                        }
                        private void initComponents() {
                               northPanel = new JPanel();
                               cont = getContentPane();
                               columns = new Vector();
                               rows = new Vector();
                               temp = new Vector();
                    
                               tableModel = new DefaultTableModel();
                               table = new JTable();
                               scrollPane = new JScrollPane(table);
                               tableNameList = new Vector();
                               cmbTables = new JComboBox(tableNameList);
                               northPanel.add(cmbTables);
                               setDatabaseTable();
                    
                               cont.setLayout(new BorderLayout());
                               cont.add(northPanel,BorderLayout.NORTH);
                               cont.add(scrollPane,BorderLayout.CENTER);
                    
                    
                        }
                        private void setDatabaseTable() {
                                try {
                                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                                String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ="+"BIBLIO.MDB"+";DriverID=22;READONLY=true}";
                                con = DriverManager.getConnection( database ,"","");
                                if(!con.isClosed()) {
                                    System.out.println("Successfully Connected To Access");
                                }
                                dbmd = con.getMetaData();
                                ResultSet rs = dbmd.getTables(null,null,"%",null);
                                while(rs.next()) {
                                    if(rs.getString(4).trim().toUpperCase().equalsIgnoreCase("TABLE")) {
                                        tableNameList.addElement(rs.getString(3));
                                    }
                                }
                                rs.close();
                                cmbTables.addActionListener(this);
                    
                                if(tableNameList.size()>0) {
                                   cmbTables.setSelectedIndex(0);
                                }
                    
                                cmbTables.validate();
                    
                    
                                } catch(Exception e) {
                                   e.printStackTrace();
                                }
                        }
                    
                        public static void main(String[] args) {
                            DatabaseSurfer databasesurfer = new DatabaseSurfer();
                        }
                    
                        public void actionPerformed(ActionEvent e) {
                               String tableName="",sql="";
                               Statement stat=null;
                               ResultSet rs=null;
                               ResultSetMetaData rsmd=null;
                               try {
                                    columns.removeAllElements();
                                    rows.removeAllElements();
                    
                                    tableName = cmbTables.getSelectedItem().toString();
                                    sql = "SELECT * FROM `"+tableName+"`";
                                    stat = con.createStatement();
                                    rs = stat.executeQuery(sql);
                                    rsmd = rs.getMetaData();
                    
                                    for(int i=0;i<rsmd.getColumnCount();i++) {
                                          columns.addElement(rsmd.getColumnName(i+1));
                                    }
                    
                    
                                    while(rs.next()) {
                                        temp = new Vector();
                                    for(int i=0;i<rsmd.getColumnCount();i++) {
                                         temp.addElement(rs.getString(i+1));
                                       }
                                        rows.addElement(temp);
                                    }
                                    rs.close();
                                    stat.close();
                    
                                    tableModel.setDataVector(rows,columns);
                                    table.setModel(tableModel);
                                    table.addNotify();
                                    table.repaint();
                    
                                } catch(Exception ex) {
                                   ex.printStackTrace();
                                }
                        }
                    
                    }
                    Rashi can I expect 1 duke ?
                    • 7. Re: how can I open Ms access file in my java program
                      807601
                      thanks for all ur help.but I am still clueless.....
                      I am giving my code pls chk it.....


                      I am sending the table name from another form where I have the combo box..
                      • 8. Re: how can I open Ms access file in my java program
                        807601
                        package packageCptms;
                        import java.awt.event.ActionEvent;
                        import java.awt.event.ActionListener;
                        import javax.swing.*;
                        import javax.swing.table.*;
                        import java.awt.*;
                        import java.sql.*;
                        import java.util.*;


                        /**
                        *
                        * @author Administrator
                        */
                        public class ClassTableSetter extends AbstractTableModel {

                        private JTable table;
                        private DefaultTableModel tableModel;
                        private JComboBox cmbTables;
                        private Vector tableNameList,rows,columns,temp;
                        private JScrollPane scrollPane;
                        private JPanel northPanel;
                        private Container cont;
                        private DatabaseMetaData dbmd;
                        private ResultSetMetaData rsmd;
                        private Connection con=null;
                        private Statement qryStmt;
                        private Statement stm;
                        String TableName="";

                        private Vector cache;

                        private ResultSet rs;

                        private int colCount;

                        private int noofrows;

                        private String[] headers;
                        /** Creates a new instance of ClassTableSetter */
                        public ClassTableSetter(String tableName) {
                        TableName=tableName;
                        try
                        {
                        con=null;
                        qryStmt=null;
                        String query="Select * from '"+TableName+"' ";
                        con=ClassDB.getConnection();

                        stm=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);

                        //rs=stm.executeQuery(query);
                        setQuery(query);
                        // setDatabaseTable(query);
                        }
                        catch (SQLException ex)
                        {
                        ex.printStackTrace();
                        GlobalData.displayDataBaseError("User Creation");
                        }
                        finally
                        {
                        if(qryStmt!= null )
                        {
                        try
                        {
                        qryStmt.close() ;
                        qryStmt=null;
                        }
                        catch (SQLException ex)
                        {
                        ex.printStackTrace();
                        }
                        }
                        if(con!= null )
                        {
                        try
                        {
                        con.close() ;
                        con=null;
                        }
                        catch (SQLException ex)
                        {
                        ex.printStackTrace();
                        }
                        }
                        }
                        }

                        /*public void setquery(String query)
                        {
                        int noofrows;
                        ResultSet rs;
                        try {           

                        rs=stm.executeQuery(query);
                        rsmd=rs.getMetaData();

                        rs.last();

                        noofrows=rs.getRow();

                        } catch (SQLException ex) {
                        ex.printStackTrace();
                        }



                        fireTableStructureChanged();

                        }*/
                        public void setQuery(String q){
                        cache= new Vector();
                        try{
                        rs=stm.executeQuery(q);
                        ResultSetMetaData meta=rs.getMetaData();
                        colCount=meta.getColumnCount();
                        headers=new String[colCount];
                        for (int h=1;h<=colCount;h++)
                        {
                        headers[h-1]=meta.getColumnName(h);
                        }
                        while(rs.next())
                        {
                        String[] record=new String[colCount];
                        for(int i=0;i<colCount;i++)
                        {record[i]=rs.getString(i+1);}
                        cache.addElement(record);
                        }

                        fireTableChanged(null);
                        }
                        catch(Exception e){
                        cache=new Vector();
                        e.printStackTrace();
                        }
                        }
                        private void setDatabaseTable( String query) {
                        try {
                        con=null;
                        qryStmt=null;

                        // con=ClassDB.getConnection();

                        //stm=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);


                        //Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                        //String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ="+"cptms.MDB"+";DriverID=22;READONLY=true}";
                        //con = DriverManager.getConnection( database ,"","");
                        if(!con.isClosed()) {
                        System.out.println("Successfully Connected To Access");
                        }
                        dbmd = con.getMetaData();
                        ResultSet rs = dbmd.getTables(null,null,"%",null);
                        while(rs.next()) {
                        if(rs.getString(4).trim().toUpperCase().equalsIgnoreCase("TABLE")) {
                        tableNameList.addElement(rs.getString(3));
                        }
                        }
                        rs.close();
                        /*cmbTables.addActionListener(this);

                        if(tableNameList.size()>0) {
                        cmbTables.setSelectedIndex(0);
                        }

                        cmbTables.validate();

                        */
                        } catch(Exception e) {
                        e.printStackTrace();
                        }
                        }
                        public boolean isCellEditable(int row, int col)throws IllegalStateException
                        {
                        try{
                        return true;
                        }
                        catch(Exception ex)
                        {
                        System.out.println("Exception :" +ex.getMessage());
                        }
                        return true;
                        }
                        /*
                        public void actionPerformed(ActionEvent e) {
                        String tablename="",sql="";
                        Statement stat=null;
                        ResultSet rs=null;
                        ResultSetMetaData rsmd=null;
                        try {
                        columns.removeAllElements();
                        rows.removeAllElements();
                        tablename = TableName;
                        //tablename = cmbTables.getSelectedItem().toString();
                        sql = "SELECT * FROM `"+tablename+"`";
                        stat = con.createStatement();
                        rs = stat.executeQuery(sql);
                        rsmd = rs.getMetaData();

                        for(int i=0;i<rsmd.getColumnCount();i++) {
                        columns.addElement(rsmd.getColumnName(i+1));
                        }


                        while(rs.next()) {
                        temp = new Vector();
                        for(int i=0;i<rsmd.getColumnCount();i++) {
                        temp.addElement(rs.getString(i+1));
                        }
                        rows.addElement(temp);
                        }
                        rs.close();
                        stat.close();

                        tableModel.setDataVector(rows,columns);
                        table.setModel(tableModel);
                        table.addNotify();
                        table.repaint();

                        } catch(Exception ex) {
                        ex.printStackTrace();
                        }
                        }*/
                        public int getColumnCount() throws IllegalStateException
                        {
                        try
                        {

                        return rsmd.getColumnCount();

                        }

                        catch(SQLException ex)
                        {
                        System.out.println("SQLException :" +ex.getMessage());
                        }

                        return 0;
                        }



                        public String getColumnName(int column) throws IllegalStateException
                        {


                        try
                        {

                        return rsmd.getColumnName(column +1);
                        }
                        catch(SQLException ex)
                        {
                        System.out.println("SQLException :" +ex.getMessage());
                        }
                        return " ";

                        }

                        public int getRowCount() throws IllegalStateException
                        {
                        return noofrows;

                        }


                        public Object getValueAt(int row,int column)
                        throws IllegalStateException
                        {
                        try
                        {

                        rs.absolute(row + 1);
                        return rs.getObject(column +1);

                        }

                        catch(SQLException ex)
                        {
                        System.out.println("SQLException :" +ex.getMessage());
                        }
                        return " ";
                        }

                        }
                        • 9. Re: how can I open Ms access file in my java program
                          807601
                          Have you gone through my Code ?

                          1) Use DefaultTableModel instead Of AbstractTableModel
                          So you can do
                          tableModel.setDataVector(rows,columns);
                          table.setModel(tableModel);
                          table.addNotify();

                          2) This is what you need only.
                          In main class define tableModel = new DefaultTableModel();
                          public void actionPerformed(ActionEvent e) {
                                     String tableName="",sql="";
                                     Statement stat=null;
                                     ResultSet rs=null;
                                     ResultSetMetaData rsmd=null;
                                     try {
                                          columns.removeAllElements();
                                          rows.removeAllElements();
                           
                                          tableName = cmbTables.getSelectedItem().toString();
                                          sql = "SELECT * FROM `"+tableName+"`";
                                          stat = con.createStatement();
                                          rs = stat.executeQuery(sql);
                                          rsmd = rs.getMetaData();
                           
                                          for(int i=0;i<rsmd.getColumnCount();i++) {
                                                columns.addElement(rsmd.getColumnName(i+1));
                                          }
                           
                           
                                          while(rs.next()) {
                                              temp = new Vector();
                                          for(int i=0;i<rsmd.getColumnCount();i++) {
                                               temp.addElement(rs.getString(i+1));
                                             }
                                              rows.addElement(temp);
                                          }
                                          rs.close();
                                          stat.close();
                           
                                          tableModel.setDataVector(rows,columns);
                                          table.setModel(tableModel);
                                          table.addNotify();
                                          table.repaint();
                           
                                      } catch(Exception ex) {
                                         ex.printStackTrace();
                                      }
                              }
                          • 10. Re: how can I open Ms access file in my java program
                            807601
                            thanks
                            i did as u said
                            right now my program is not showing any error
                            but it is not showing the table either
                            actually I am using netbeans..here the init component is
                            automatically generated and cannot be editted ...
                            so I put the above initcomponent code in the constructor
                            is it possible that due to this my table is not showing..
                            • 11. Re: how can I open Ms access file in my java program
                              807601
                              so I put the above initcomponent code in the constructor
                              is it possible that due to this my table is not showing..

                              I am calling my initComponents method from constructor only.
                              Check if you have added actionlistener on combo.
                              Simple logic
                              1) Use DatabaseMetadata,ResultsetMetaData. Populate Combo with table names using DatabaseMetadata
                              2) Use DefaultTableModel with vectors rows,columns
                              3) Add Actionlistener on combo.
                              4) On change action. Fire query and populate table as in my Code.
                              • 12. Re: how can I open Ms access file in my java program
                                807601
                                thanks a lot

                                my table is showing now
                                • 13. Re: how can I open Ms access file in my java program
                                  807601
                                  two more things
                                  how do add one extra row at the end of the table so that the users can add new data

                                  and
                                  how to I fix the width of the columns(actually one of my table has 7 to 8 columns ,so when it is displayed every column width decreases)
                                  I mean how to make scrollpane scroll horizontally also
                                  thanks a lot for ur help
                                  • 14. Re: how can I open Ms access file in my java program
                                    807601
                                    how do add one extra row at the end of the table so that the users can add new data
                                     int noOfColumns = columns.size();
                                                  temp = new Vector();
                                                  for(int i=0;i<noOfColumns;i++) {
                                                     temp.addElement("");
                                                  }
                                                  rows.addElement(temp);
                                                  table.repaint();
                                                  table.addNotify();
                                                  scrollPane.revalidate();
                                    how to I fix the width of the columns
                                    table.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);
                                    table.getColumnModel().getColumn(0).setWidth(100);
                                    1 2 Previous Next