Create Access databases programmatically via JDBC-ODBC?

Thread view:

Jonathan Fisher - 05 Feb 2004 04:16 GMT

I am interacting with Access through a JDBC-ODBC bridge, and my code
needs to create new Access databases frequently. Is there some way to
create a database programmatically from Java, instead of forcing my
users to invoke the Data Source Adminstrator to create a DSN each time?
I'm hoping for something like SQLConfigDataSource(), but without having
to muck about in C.

Also, once I create a database, how do I create new tables in it?
Obvious things like "CREATE TABLE ... " don't seem to work.

Pointers to where either of these questions is answered on the web would
also be much appreciated -- I can't seem to find an explicit description
of what subset of SQL Access supports via ODBC, for example....

TIA for any help,
Jonathan

Reply to this Message

Bjorn Abelli - 05 Feb 2004 13:06 GMT

"Jonathan Fisher" ...

> I am interacting with Access through a JDBC-ODBC
> bridge, and my code needs to create new Access
[quoted text clipped - 4 lines]
> I'm hoping for something like SQLConfigDataSource(),
> but without having to muck about in C.

There are several ways to do this, but I think the easiest would be to
deliver an empty "template" database with your application. When the need to
create a "new" db occurs, you simply make a copy of it to the desired
location and with an approriate name.

> Also, once I create a database, how do I create
> new tables in it? Obvious things like
> "CREATE TABLE ... " don't seem to work.

Yes it does.

You maybe have problems with what column types you're trying to use?

All of the types from SQL-92 can be used (except DECIMAL), though some of
the types might not map to what you expect...

// Bjorn A

Reply to this Message

Jonathan Fisher - 13 Feb 2004 06:16 GMT

> "Jonathan Fisher" ...
>
[quoted text clipped - 11 lines]
> create a "new" db occurs, you simply make a copy of it to the desired
> location and with an approriate name.

Thanks for the reply.

I'm afraid I may not have explained my question clearly enough, though.
It's not creating the new underlying .mdb file that bothers me, but the
ODBC data-source to connect to that .mdb file. As far as I can tell,
JDBC-ODBC will only allow me to interact directly with the "template"
database that is registered with the ODBC Data Source Administrator. It
seems like your suggestion would lead me to do the following every time
I want to interact with my database:

(1) Copy my "real" database, say MyDatabase.mdb, to the one that's
registered with the ODBC Data Source Administrator, say
ODBC_Template.mdb.

(2) Send my SQL code to JDBC-ODBC, referencing the DSN that is
registered to ODBC_Template.mdb.

(3) Copy ODBC_Template.mdb back to MyDatabase.mdb.

That seems like such a roundabout way of catching fish....* (And that's
assuming that the ODBC Data Source Administrator will put up with me
copying files into and out of the files that are registered with it,
which I haven't tried.) Am I missing something?

> > Also, once I create a database, how do I create
> > new tables in it? Obvious things like
[quoted text clipped - 8 lines]
>
> // Bjorn A

You're right, it worked when I tried it again. I must have had a
mismatched quotation-mark or something before.... :-/

Thanks,
Jonathan

* cf "The Animal Family", by Randall Jarrell

Reply to this Message

UGENT - 13 Feb 2004 08:09 GMT

It is not required to use a registered data source. You can provide all the
information in the url.

eg. jdbc:odbc:DRIVER={Microsoft Access Driver (*.mdb)};DBQ=<filename>

This should be enough to create the connection, but you can also provide
other parameters like cache size etc.

Axel Hallez

Reply to this Message

Jonathan Fisher - 14 Feb 2004 18:34 GMT

> It is not required to use a registered data source. You can provide all the
> information in the url.
[quoted text clipped - 5 lines]
>
> Axel Hallez

Cool -- thanks for the info!

Is there documentation available somewhere on the web for the JDBC-ODBC
driver, especially the syntax for the URLs that get passed to it? I've
looked pretty hard and haven't found any really clear-cut information.

For example, Sun's docs for the ODBC sub-protocol of JDBC (1) say that
the syntax for using the ODBC sub-protocol is:

jdbc:odbc:<data-source-name>[;<attribute-name>=<attribute-value>]*

But M$'s docs for ODBC attributes (2) list things like
"SQL_ATTR_LOGIN_TIMEOUT", not things like "DBQ"....

TIA for any help,
Jonathan

(1) URL =
http://java.sun.com/j2se/1.4.2/docs/guide/jdbc/getstart/connection.html#9
99416

(2) URL =
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm
/odch06pr_5.asp

Reply to this Message

Axel Hallez - 16 Feb 2004 09:47 GMT

> Is there documentation available somewhere on the web for the JDBC-ODBC
> driver, especially the syntax for the URLs that get passed to it? I've
[quoted text clipped - 4 lines]
>
> jdbc:odbc:<data-source-name>[;<attribute-name>=<attribute-value>]*

There are two possibilities. You either specify a predefined datasource name
or you provide name value pairs.
The only mandatory attribute seems to be DRIVER, the other attributes depend
on the driver at hand.
One way to see what kind of parameters you can use is to create a file based
datasource with the datasource manager and then open the .dsn file in a
texteditor.

Axel Hallez

Reply to this Message

Create Access databases programmatically via JDBC-ODBC?

Free Magazines