This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Feb 6, 2008 11:08 PM by 807601 RSS

how can I open Ms access file in my java program

807601 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    thanks a lot

    my table is showing now
  • 13. Re: how can I open Ms access file in my java program
    807601 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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