Writing an MSAccessdatabase through Java, problems with SQL statements

[ashley.morgan]

USING JCREATOR
I have gotten a connection and created 3 tables and dropped one. I've gotten it to let me insert an integer value into the only column s.execute("insert into STUDENTS values(1)"); for the first column. I created a second table with multiple columns but I cannot figure out how to update one or all of the columns. How do I do that? I've tried many different techniques. Also, after I had created three tables it wouldn't let me create any more, only drop tables.s.execute("create table STUDENTS (ID integer)");
s.execute("drop table STUDENTS"); What is causing this?

[Fou-Lu]

Access definitely isn't limited to only a few tables. Are you certain you are catching the SQL exception and printing it out? The only thing I can think of on a direct creation query is that you are trying to reuse an existing table, using reserved words, using bad datatypes, or have bad SQL structure.

This isn't a java question, this is a database one. The problem isn't with Java.
To do multiple insertions, you simply state in your queries what columns (optional if you are writing to all columns, required if you are not writing to null fields) and the associated data. Access (from what I can tell) does not allow multiple insertions (except from a select).
Updates are simply done with the UPDATE {tablename} SET {columnName} = value WHERE {condition} syntax. Separate multiple columnName = value pairs with commas. Be aware of your datatypes though, if its a string you are expected to wrap it in single quotations.

[Old Pedant]

It's not required, but it's a really really really good idea to list all the columns you are inserting into.

Example:

s.execute("create table STUDENTS (ID integer, name VARCHAR(50), grade double)");
s.execute("insert into STUDENTS (id,name,grade) VALUES(1,'adam',3.5)");
s.execute("insert into STUDENTS (id,name,grade) VALUES(2,'bob',2.723)");
s.execute("insert into STUDENTS (id,name,grade) VALUES(3,'carl',1.92)");

Also, unlike MySQL, if you have an autonumber field you *CAN NOT* mention it in the INSERT, which effectively *forces* you to explicitly call out the other columns:

s.execute("create table STUDENTS (ID counter, name VARCHAR(50), grade double)");
s.execute("insert into STUDENTS (name,grade) VALUES('adam',3.5)");
s.execute("insert into STUDENTS (name,grade) VALUES('bob',2.723)");
s.execute("insert into STUDENTS (name,grade) VALUES('carl',1.92)");

COUNTER is (one of) Access's alias for auto-number.

[ashley.morgan]

When I got my code to work, I tried to integrate it with a GUI and put the connection and sql statements in a method

private void updater(String str1, String str2, String sr3, String str4)
{
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String dataSourceName = "mdbTEST";
String dbURL = "jdbc:odbc:" + dataSourceName;
Connection con = DriverManager.getConnection(dbURL, "Admin","");
Statement s = con.createStatement();

s.execute("insert into s1 (CLASS1, CLASS2, CLASS3, CLASS4) VALUES('" + str1 +"', '"+ str2 +"', '"+ str3 +"','"+ str4+"')");
s.execute("insert into s1 (CLASS1, CLASS2, CLASS3, CLASS4) VALUES('BLANK', 'BLANK', 'BLANK', 'BLANK'");
}
catch (Exception e)
{
System.out.println("Error: " + e);
}

and just called the method with the parameters I needed. I am getting this exception,

java.secutity.AccessControlException: access denied <java.lang.RuntimePermission accessClassInPackage.sun.jdbc.odbc>

Can you tell me how I can fix this please?