File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Java & MS Access Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Java & MS Access" Watch "Java & MS Access" New topic
Author

Java & MS Access

Davy Kelly
Ranch Hand

Joined: Jan 12, 2004
Posts: 384
Hey everyone,

I have got a small test class working with MS Access.

I can create a table, insert columns and values to the table & drop the table.

But I am looking for a little bit of info.

I want to be able to create the table if it is not there, but use it if it is there, I was going to use an if statement for this:


How can I do this? how do I find the table is there or not?
I have searched google endlessly.

davy
[ April 22, 2007: Message edited by: Davy Kelly ]

How simple does it have to be???
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30537
    
150

Davy,
There are two main approaches for doing this:
1) The clean way - do a query against the system table that lists all the other tables (I'm not sure if Access has one of these, but you could find out by looking at the tables in some graphical view.)
2) The other way - do a query against your table and look at the error code if the table does not exist.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Davy Kelly
Ranch Hand

Joined: Jan 12, 2004
Posts: 384
I have been looking at the API regarding ResultSet & ResultSetMetaData, trying to get the getTableName() method to work.

but I am not sure I know what you mean by your suggestions.

davy
Davy Kelly
Ranch Hand

Joined: Jan 12, 2004
Posts: 384
Ok been playing around all day, I seem to have got this working.

I just made the database table through Access and I can now add to it, the program looks as if it is working the way I want it just now.

thanks

davy
Davy Kelly
Ranch Hand

Joined: Jan 12, 2004
Posts: 384
Another quick question...

in my MS Access file, I have 18 columns.
column_1 I made as primary Key, which is using a string which is a date: e.g. 21 April 2007

what code would I use to find a certain row, if it existed, so I can update that row or make a new row if that certain row does not exist?

I was looking into the RowID interfaces equals(object)method but I am not sure this is what I need.

davy
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30537
    
150

Originally posted by Davy Kelly:
what code would I use to find a certain row, if it existed, so I can update that row or make a new row if that certain row does not exist?

You just do a query: select count(*) from table where pk = 'date'

If rs.next() returns false, you need to add a row. If it returns true, the row exists.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18570
    
    8

Originally posted by Jeanne Boyarsky:
If rs.next() returns false, you need to add a row. If it returns true, the row exists.
Well, not quite. That query will always return exactly one row. So you need to read it and extract the one column it contains. If the column is zero, there were no rows matching the query.
Parthasarathy balakrishnan
Greenhorn

Joined: Apr 20, 2007
Posts: 14
Originally posted by Davy Kelly:
Hey everyone,

I have got a small test class working with MS Access.

I can create a table, insert columns and values to the table & drop the table.

But I am looking for a little bit of info.

I want to be able to create the table if it is not there, but use it if it is there, I was going to use an if statement for this:


How can I do this? how do I find the table is there or not?
I have searched google endlessly.

davy

[ April 22, 2007: Message edited by: Davy Kelly ]





hi,

i have seen you query...


Interesting one yaar
i have work with it....

here is the example please put the needed code...
-----------------------------------------------------------------
/**
* @(#)TableSearch.java
*
*
* @Parthasarathy.B
* @version 1.00 2007/4/23
*/

import java.sql.*;
public class TableSearch {
public static void main(String args[]){
Connection con; /*The Connection Interface*/
Statement st; /*The Statement Interface*/
ResultSet rs; /*The ResultSet InterFace*/
PreparedStatement ps=null;
boolean exists=false;
boolean Table_Creation=false;
try{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");/*loading of the Driver*/
con=DriverManager.getConnection("Jdbc:Odbc:TestConn","","");/*Establishing The Connection*/
st=con.createStatement();/*Statemnet Creation*/
//System.out.println("the value of before the boolean executed");
try{
//exists=st.execute("select * from +"ARGUMENT_TABLE_NAME"+");/* put your Table argument here And Comment Next Line*/
exists=st.execute("select * from TableCreate");
}catch(Exception e1){}
//System.out.println("the value of boolean is"+exists);
if(exists){
System.out.println("Table Name Already Exists "+exists);

}

if(exists==false){
System.out.println("Table Name Does Not Exists"+exists);
st.execute("create table TableCreate(name varchar(20),place varchar(20))");
System.out.println("Table Created");
}

}catch(Exception e){
e.printStackTrace(System.out);
System.out.println("The Exception is in ==>"+e.getMessage());
}
}
}


Note:

1.Please get the argument of the table name and put in "ARGUMENT_TABLE_NAME"
which i have mentioned in the comment area
2.Another familiar error all will make is
varchar2(20) ---> it will work in oracle database not in access

In Access
varchar(20) has to b given...


3.change the 2 line in the code:

a).//exists=st.execute("select * from +"ARGUMENT_TABLE_NAME"+");/* put your Table argument here And Comment Next Line*/

b).//st.execute("create table +"ARGUMENT_TABLE_NAME"+(name varchar(20),place varchar(20))");/*Pass your argument here*/
---------------------------------------------------------------------------

If any doubt regaurding this contact :
In Bangalore:
08066596116.


Thanks and Regards,<br />-------------------<br />Parthasarathy Balakrishnan,<br />Bangalore.
Davy Kelly
Ranch Hand

Joined: Jan 12, 2004
Posts: 384
Cheers guys.

well I was thinking I would look for the row, so I would need to look in column_1 for a specific String.
then update all columns in that row with new data.

So was I wrong to look for the RowID interface?

Thanks Parthasarathy, I have not looked at your code yet, I am just in from work, but I will look after dinner.

davy
Davy Kelly
Ranch Hand

Joined: Jan 12, 2004
Posts: 384
Sorry, just had a though....... careful now

could I do the following statement?
UPDATE tableName
SET column_2 = newData, column_3 = newData, column_4 = newData, etc
WHERE column_1 = 'specific_Search_Criteria'

sorry I think that will work only when I have the row, and its the row I need to find. just ignore, thinking outloud

davy
Davy Kelly
Ranch Hand

Joined: Jan 12, 2004
Posts: 384
I am very sorry everyone,
Parthasarathy you took my earlier problem, sorry I got this sorted.

but what i was meaning was I am thinking of using an if statement:

davy
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30537
    
150

Originally posted by Paul Clapham:
Well, not quite. That query will always return exactly one row. So you need to read it and extract the one column it contains. If the column is zero, there were no rows matching the query.

Oh yeah. I was thinking of select(*). Thanks for catching that Paul.
Davy Kelly
Ranch Hand

Joined: Jan 12, 2004
Posts: 384
I managed it eventually

here was the code that worked for me:



davy
[ April 26, 2007: Message edited by: Davy Kelly ]
Davy Kelly
Ranch Hand

Joined: Jan 12, 2004
Posts: 384
hey people,

I got my database working really well.

I only get an error now if I try to store names like O'Reilly with ' being the problem.

how can I be able to add names like this without getting errors?

my update statement is like this:


davy
[ April 30, 2007: Message edited by: Davy Kelly ]
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 41863
    
  63
Single quotes need to be escaped (by instead using two single quotes in a row), since theey are used as string delimiters by SQL. You can write code that performs this string manipulation, or instead use a PreparedStatement, which does it for you.


Ping & DNS - my free Android networking tools app
Davy Kelly
Ranch Hand

Joined: Jan 12, 2004
Posts: 384
Thanks Ulf,

I have been doing a bit of research on the PreparedStatement.

my update statment right now is big. it deals with 19 columns.
main unique string, 12 Strings, 5 ints one more string.

so I was looking at this PreparedStatement. have I got this right?


davy
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 41863
    
  63
No, this doesn't look right. A PreparedStatement has "?" placeholders where later on the parameters are set. You have none of those, but instead a "*" which has no meaning for a PreparedStatement. Have a look at the javadocs for an example.
Davy Kelly
Ranch Hand

Joined: Jan 12, 2004
Posts: 384
Like this you mean?


and the update:



davy

[ May 01, 2007: Message edited by: Davy Kelly ]
[ May 01, 2007: Message edited by: Davy Kelly ]
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Java & MS Access