(moved from Java): java and SQL statement and access 2000

[Iwishi]

I'm not sure if this is the proper forum but sience i am doing programing in java i supose it is.

anyway here's some basic information on what i got here.

using java 1.4 w/ JDBC and using an access 2000 database

picture table
-picture_id
-path
-type

student table
-student_id
-firstName
-lastName
-teacher
-icon

teacher table
-teacher_id
-userName
-icon
-students


anyway i have successfuly created all three tables above in java using these following code


s.execute( "create table PICTURE ( " +
"picture_id counter not null primary key, " +
"path text unique not null, " +
"type text not null )" );

s.execute( "create table STUDENT ( " +
"student_id counter not null primary key, " +
"firstName text, " +
"lastName text )" );

s.execute( "create table TEACHER ( " +
"teacher_id counter not null primary key, " +
"userName text unique, " +
"password text, " +
"icon int, " +
"gradeLevel int, " +
"students int )" );



anyway ive been searching high and low all over the internet searching for any sort of document on SQL statement for access and the closest ive found is transcat-SQL reference and most of it seems to work but large potation refuses to work with access 2000 and for majority of my works ive been gussing and walking around in the dark. anyway i'm very stuck on this last part

what i want to do is have the icon in student and teacher table refer to the picture_id and i want to enforce that it refers only to an vaild picture_id, i think its a many to one relation, in that there can be many teacher/student with the same icon, but that each teacher/students can only have one picture

and also i want to make the students in the teacher table refers to student_id in the student table and also enforced so that it only refers to vaild student_id, this also is many to one relationship i believe, but backward, in that one teacher can have more than 1 student but that students can only have one teacher


thank you for your help

[Iwishi]

Another question is the JDBC driver that comes with the java JDK limited in number of tables that it can create and access in an access 2000 database?


java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine could not find the object 'STUDENT'. Make sure the object exists and that you spell its name and the path name correctly.


what i have is 3 seperate function that creates the tables in the database, first function creates the question table, it works fine, second function creates the picture tables, work fine, third function creates the student and teacher tables work fine, but i cannot access them, however if i disable one of the other two say i disable creation of question or picture table, the create student/teacher table works just fine

this is confusing the **** out of me here.

[Iwishi]

i could always seperate it into two seperate database but imho that sorts of defefeats the point of me converting from an home bult database solution to this new system.

[Davey]

It sounds like you need some foreigh key constraints to ensure your data integrity.

Have a read of the following:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acfundsql.asp

[Iwishi]

thanks :-) so correct me if i'm wrong the correct SQL statement is the jet SQL for using access correct?

anyway thanks i used alter table and that worked fine, i guess there was some thing funny with the construct table and reference but when i moved it to alter table it worked just fine.

but i'm still having this error


[Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot find the input table or query 'STUDENT'. Make sure it exists and that its name is spelled correctly.

[Krzemo]

IMHO not "counter" column but "AutoIncrement" and not just TEXT but TEXT(50) for example....


And this error sugest that U failed with that table creation ... or U connected to wrong database.

Best regards,
Krzemo.

[Iwishi]

Allright thanks, i'll add in those changes later tonight as i update some more codding and so forth :-)