Trying to create a database in a MS Access DB via JDBC drivers

[Albretch]

.. I am trying to create a database in a MS Access DB via JDBC drivers.
I have tried both sun.jdbc.odbc.JdbcOdbcDriver and ids.sql.IDSDriver

From some reason both drivers Exceptions tell me 'Syntax error in
CREATE TABLE statement' even though I am not creating a table, but a
Database

// - - - - - - - - - - - - sun.jdbc.odbc.JdbcOdbc
aSQL=CREATE DATABASE dbtest;
java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver]
Syntax error in CREATE TABLE statement.
at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcStatement.execute(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcStatement.executeUpdate(Unkn own
Source)
at JDBCDL00.createCtlg(JDL00.java:200)
at JDL00.main(JDL00.java:505)

// - - - - - - - - - - - - ids.sql
aSQL=CREATE DATABASE dbtest;
java.sql.SQLException: [42000][Microsoft][ODBC Microsoft Access
Driver] Syntax error in CREATE TABLE statement
at ids.sql.IDSSocket.error(IDSSocket.java:374)
at ids.sql.IDSSocket.verify(IDSSocket.java:320)
at ids.sql.IDSStatement.submit(IDSStatement.java:157)
at ids.sql.IDSStatement.execute(IDSStatement.java:479 )
at ids.sql.IDSStatement.executeUpdate(IDSStatement.ja va:270)
at JDBCDL00.createCtlg(JDL00.java:200)
at JDL00.main(JDL00.java:505)

Does it mean MS Access does not have a concept of a BD, since it is
kind of a file base Data Store?

There is also something I don't quite know how to interpret.

When you ask the JdbcOdbc driver for Catalogs it gives you

.. . .\Office\Samples\ADDRBOOK
.. . .\Office\Samples\CONTACT
.. . .\Office\Samples\INVENTRY
.. . .\Office\Samples\Northwind

even if you declare the a USer DSN only to

.. . .\Office\Samples\Northwind

Experimentally I dropped a copy of

.. . .\Office\Samples\Northwind.mdb

in

.. . .\IDS\File\examples

and set up a system DSN to got IDSExamples.mdb

However, while query the DBMS for catalogs, I got

.. . .\IDS\File\examples\IDSExamples
.. . .\IDS\File\examples\Northwind

Does it mean Access considers the folder containing the '.mdb' file
as sort of a 'schema'?

How could u still create a Database using JDBC in a MSAccess DB?

[Liz]

I am pretty sure you can't create a database with jdbc,
at least on the pc.

"Albretch" <lb*****@hotmail.com> wrote in message
news:f8**************************@posting.google.c om...

. I am trying to create a database in a MS Access DB via JDBC drivers.
I have tried both sun.jdbc.odbc.JdbcOdbcDriver and ids.sql.IDSDriver

From some reason both drivers Exceptions tell me 'Syntax error in
CREATE TABLE statement' even though I am not creating a table, but a
Database

// - - - - - - - - - - - - sun.jdbc.odbc.JdbcOdbc
aSQL=CREATE DATABASE dbtest;
java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver]
Syntax error in CREATE TABLE statement.
at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcStatement.execute(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcStatement.executeUpdate(Unkn own
Source)
at JDBCDL00.createCtlg(JDL00.java:200)
at JDL00.main(JDL00.java:505)

// - - - - - - - - - - - - ids.sql
aSQL=CREATE DATABASE dbtest;
java.sql.SQLException: [42000][Microsoft][ODBC Microsoft Access
Driver] Syntax error in CREATE TABLE statement
at ids.sql.IDSSocket.error(IDSSocket.java:374)
at ids.sql.IDSSocket.verify(IDSSocket.java:320)
at ids.sql.IDSStatement.submit(IDSStatement.java:157)
at ids.sql.IDSStatement.execute(IDSStatement.java:479 )
at ids.sql.IDSStatement.executeUpdate(IDSStatement.ja va:270)
at JDBCDL00.createCtlg(JDL00.java:200)
at JDL00.main(JDL00.java:505)

Does it mean MS Access does not have a concept of a BD, since it is
kind of a file base Data Store?

There is also something I don't quite know how to interpret.

When you ask the JdbcOdbc driver for Catalogs it gives you

. . .\Office\Samples\ADDRBOOK
. . .\Office\Samples\CONTACT
. . .\Office\Samples\INVENTRY
. . .\Office\Samples\Northwind

even if you declare the a USer DSN only to

. . .\Office\Samples\Northwind

Experimentally I dropped a copy of

. . .\Office\Samples\Northwind.mdb

in

. . .\IDS\File\examples

and set up a system DSN to got IDSExamples.mdb

However, while query the DBMS for catalogs, I got

. . .\IDS\File\examples\IDSExamples
. . .\IDS\File\examples\Northwind

Does it mean Access considers the folder containing the '.mdb' file
as sort of a 'schema'?

How could u still create a Database using JDBC in a MSAccess DB?

[Luca Paganelli]

I think the problem is that
Access maps one database to
one file. With the odbc
bridge you can access to a db
either via DSN (see ODBC
sources) or by specifing
the actual file in which the
db is contained.

Therefore it's not possible to
CREATE a new db.
You could try creating a new
..mdb file in the file system
and point the odbc bridge to
that file and finally start
creating TABLEs (and not
DATABASE).
--
Luca Paganelli

[Albretch]

You mean a MS Access one right?

I have indeed created all kinds of DBs for the other JDBC
drivers/DBMS combinations.

"Liz" <Li*@nospam.com> wrote in message news:<y9CMc.180319$Oq2.73346@attbi_s52>...

I am pretty sure you can't create a database with jdbc,
at least on the pc.

[Albretch]

"Luca Paganelli" <as*@me.it> wrote in message news:<%W******************@tornado.fastwebnet.it>. ..

I think the problem is that
Access maps one database to
one file. With the odbc
bridge you can access to a db
either via DSN (see ODBC
sources) or by specifing
the actual file in which the
db is contained.

Therefore it's not possible to
CREATE a new db.
You could try creating a new
.mdb file in the file system
and point the odbc bridge to
that file and finally start
creating TABLEs (and not
DATABASE).

Yeah! I figured! I just wanted to have more input from experienced
people.

So, and this is also info 'for the rest of us', in order to CREATE a
new db in Access you will have to:

1._ know which version of Access you are working with

2._ keep a blank (no tables, queries, . . .) copy of an ".mdb" file
for this version

3._ know which directory do you want the 'new' DB in

4._ transfer the blank copy to this dir

5._ rename the '.mdb' file to the name of the 'created' DB

6._ define the DSN "on the fly" by passing all driver conf. settings
(ODBC configuration params in this case) inside the connect string:

con = DriverManager.getConnection("jdbc:odbc:Driver={Mic roSoft Access
Driver (*.mdb)};DBQ=C:/data/Access/<name>.mdb","<UsersId>","<password>");

I think this is not a big deal at all and works on the OS level, so
probably MS Access drivers should do that on their own. Wouldn't you
agree?

Why don't they?

[Albretch]

improving myself on:

4._ transfer the blank copy to this dir

I think there is a way within the OS to produce a blank Access .mdb file

As it is the case when you right click on a folder and select

New > MS Access file

[thufir.hawat]

Why don't I see more about JDO? I *thought* it was s'possed to be the OO
database holy grail..
Thufir Hawat
<http://www.shaw.ca/members/hawat/source/>

[Luke Webber]

th**********@mail.com wrote:

Why don't I see more about JDO? I *thought* it was s'possed to be the
OO database holy grail..
Thufir Hawat
<http://www.shaw.ca/members/hawat/source/>

Sorry Thufir, but you were mistaken. Unusual for a mentat. Perhaps
you've been compromised? <g>

Luke

[thufir.hawat]

On Tue, 27 Jul 2004, Luke Webber wrote:

th**********@mail.com wrote:

Why don't I see more about JDO? I *thought* it was s'possed to be the OO
database holy grail..
Thufir Hawat
<http://www.shaw.ca/members/hawat/source/>

Sorry Thufir, but you were mistaken. Unusual for a mentat. Perhaps you've
been compromised? <g>

Luke

you've let the cat out of the bag!
Thufir Hawat
<http://www.shaw.ca/members/hawat/source/>

[Andrew Thompson]

On Wed, 28 Jul 2004 08:38:58 GMT, th**********@mail.com wrote:

On Tue, 27 Jul 2004, Luke Webber wrote:

th**********@mail.com wrote: .... Sorry Thufir, but you were mistaken. Unusual for a mentat. Perhaps you've
been compromised? <g>

.... you've let the cat out of the bag!

Where's my Gom Jabbar? ;-)

--
Andrew Thompson
http://www.PhySci.org/ Open-source software suite
http://www.PhySci.org/codes/ Web & IT Help
http://www.1point1C.org/ Science & Technology