Re: Create Access databases programmatically via JDBC-ODBC?

From: Jonathan Fisher (hq8jk4302_at_sneakemail.com.nospam)
Date: 02/13/04 

Date: Fri, 13 Feb 2004 06:16:39 GMT

In article <bvtf5f$10mong$1@ID-73292.news.uni-berlin.de>,
 "Bjorn Abelli" <DoNotSpam.bjorn_abelli@hotmail.com> wrote:

> "Jonathan Fisher" ...
>
> > 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.
>
> 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.

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
> > "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
>
>

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

Relevant Pages

  • Re: SQL syntax over a network
    ... You can code in one database, ... > database identifier is what goes in the first string literal ... > Jet SQL is what you get by default in most? ...
    (microsoft.public.access.modulesdaovba)
  • using multiple Informix DB Servers w/same name, different Host IPs in ODBC
    ... the same software built around and Informix database. ... Server Name at all these sites is the same. ... address) Window will change the IP address in the previously set up ... ODBC Data Source to that I put in the second ODBC Data Source. ...
    (comp.databases.informix)
  • Re: Accessing an Excel database using Word
    ... Opens a specified database in a Workspace object and returns a reference to the Database object that represents it. ... or the data source name of an ODBC data source. ... A Variant that specifies various connection information, ...
    (microsoft.public.excel.programming)
  • microsoft access driver problems
    ... access a microsoft access database. ... it working using a windows form but not a web form. ... set up the database wrong using the odbc data source administrator? ... database using odbc data source administrator, ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: SQL syntax over a network
    ... You can code in one database, ... > to using multiple IN clauses for the data source. ... > database identifier is what goes in the first string literal ...
    (microsoft.public.access.modulesdaovba)