f



Accessing Access database files

Is there a JDBC driver or non-JDBC class library that would give a Java 
program read/write access to the tables and schema in a MS Access 
database? 

I don't need to update the database, just examine the schema and table 
contents. I don't have a copy od the Access software and nowhere to run 
it because all my computers run Linux.


-- 
martin@   | Martin Gregorie
gregorie. | Essex, UK
org       |
0
Martin
7/1/2014 4:28:25 PM
comp.lang.java.programmer 52711 articles. 1 followers. Post Follow

39 Replies
2983 Views

Similar Articles

[PageSpeed] 39

On 7/1/2014 9:28 AM, Martin Gregorie wrote:
> Is there a JDBC driver or non-JDBC class library that would give a Java
> program read/write access to the tables and schema in a MS Access
> database?
>
> I don't need to update the database, just examine the schema and table
> contents. I don't have a copy od the Access software and nowhere to run
> it because all my computers run Linux.


I haven't tried this myself, but Stack Overflow says:

"You can use JDBC-ODBC bridge by calling:

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver")

Then you can use JDBC database URL in form:

jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=c:\\Nwind.mdb

or if you configure your database in ODBC manager you can use its alias:

jdbc:odbc:northwind

"

You might need an older copy of the JDK because the driver was removed 
in JDK 8.  You can find drivers in a couple of places:

http://jackcess.sourceforge.net/
http://ucanaccess.sourceforge.net/site.html



0
markspace
7/1/2014 4:55:30 PM
On Tue, 01 Jul 2014 09:55:30 -0700, markspace wrote:

> On 7/1/2014 9:28 AM, Martin Gregorie wrote:
>> Is there a JDBC driver or non-JDBC class library that would give a Java
>> program read/write access to the tables and schema in a MS Access
>> database?
>>
>> I don't need to update the database, just examine the schema and table
>> contents. I don't have a copy od the Access software and nowhere to run
>> it because all my computers run Linux.
> 
> 
> I haven't tried this myself, but Stack Overflow says:
> 
> "You can use JDBC-ODBC bridge by calling:
> 
> Class.forName("sun.jdbc.odbc.JdbcOdbcDriver")
> 
> Then you can use JDBC database URL in form:
> 
> jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=c:\\Nwind.mdb
> 
> or if you configure your database in ODBC manager you can use its alias:
> 
> jdbc:odbc:northwind
> 
> "
> 
> You might need an older copy of the JDK because the driver was removed
> in JDK 8.  You can find drivers in a couple of places:
> 
> http://jackcess.sourceforge.net/
> http://ucanaccess.sourceforge.net/site.html

Does this stuff work without a copy of the Access software?
I'm puzzled because EasySoft makes a big fuss about leaving the .mdb file 
on the PC its always lived on, but I have the .mdb files I'd like to look 
at already on my Linux box. 

What this is about: I'm in the process of taking over the Roster Manager 
role for my gliding club. The task is primarily concerned with looking 
after the rosters database. There is currently no ERD for it, so it would 
be nice if I could use the jsql tool to look at the schema by accessing 
the .mdb file.


-- 
martin@   | Martin Gregorie
gregorie. | Essex, UK
org       |
0
Martin
7/1/2014 5:29:14 PM
On 7/1/2014 12:55 PM, markspace wrote:
> On 7/1/2014 9:28 AM, Martin Gregorie wrote:
>> Is there a JDBC driver or non-JDBC class library that would give a Java
>> program read/write access to the tables and schema in a MS Access
>> database?
>>
>> I don't need to update the database, just examine the schema and table
>> contents. I don't have a copy od the Access software and nowhere to run
>> it because all my computers run Linux.
>
>
> I haven't tried this myself, but Stack Overflow says:
>
> "You can use JDBC-ODBC bridge by calling:
>
> Class.forName("sun.jdbc.odbc.JdbcOdbcDriver")
>
> Then you can use JDBC database URL in form:
>
> jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=c:\\Nwind.mdb
>
> or if you configure your database in ODBC manager you can use its alias:
>
> jdbc:odbc:northwind
>
> "

If you are on Windows then it works somewhat OK.

The JDBC ODB bridge has some problems (at least early version did not
support multi-threaded usage) and Access has some limitations as
database.

But many people including me has used it. One just need to
know what not to do with the config.

Arne


0
UTF
7/2/2014 1:01:01 AM
On 7/1/2014 12:28 PM, Martin Gregorie wrote:
> Is there a JDBC driver or non-JDBC class library that would give a Java
> program read/write access to the tables and schema in a MS Access
> database?
>
> I don't need to update the database, just examine the schema and table
> contents. I don't have a copy od the Access software and nowhere to run
> it because all my computers run Linux.

I don't think you can get it to work.

The semi-official way to do it is to use the JDBC ODBC bridge.

But that uses the Access ODBC driver which only exist on Windows.

You need a Windows computer.

Arne


0
UTF
7/2/2014 2:47:54 AM
On 7/1/2014 7:47 PM, Arne Vajhøj wrote:

> The semi-official way to do it is to use the JDBC ODBC bridge.
>
> But that uses the Access ODBC driver which only exist on Windows.
>
> You need a Windows computer.

Two of the links I posted used Java only, and therefore will run on any 
system that runs a JRE/JDK.


0
markspace
7/2/2014 3:11:39 AM
Martin Gregorie <martin@address-in-sig.invalid> wrote:

>Is there a JDBC driver or non-JDBC class library that would give a Java 
>program read/write access to the tables and schema in a MS Access 
>database? 
>
>I don't need to update the database, just examine the schema and table 
>contents. I don't have a copy od the Access software and nowhere to run 
>it because all my computers run Linux.

You need a JDBC driver for the Access database. Google turned up this
one <http://ucanaccess.sourceforge.net/site.html>. The site includes
some details to help connect the database to Open Office so you can
examine the tables without writing any Java code.

I haven't tried it because I use the JDBC-ODBC bridge on a Windows
machine but that would require an ODBC driver on your Linux computer.
 

0
Gordon
7/2/2014 6:57:27 AM
On Tue, 01 Jul 2014 09:55:30 -0700, markspace wrote:

> You might need an older copy of the JDK because the driver was removed
> in JDK 8.  You can find drivers in a couple of places:
> 
> http://jackcess.sourceforge.net/
> http://ucanaccess.sourceforge.net/site.html
>
Thanks for those references. They look like exactly what I was hoping to 
find. So does the SQLeo application which is referenced from the 
UCanAcccess website.

I kind of skipped over them  at first: my bad. My excuse is that I'd been 
searching for ODBC/JDBC drivers earlier and had only found expensive paid-
for offerings (the same price or more than Access itself) that all 
targeted Access the database server rather than reading the file directly 
as I now see that Jackcess does.


-- 
martin@   | Martin Gregorie
gregorie. | Essex, UK
org       |
0
Martin
7/2/2014 10:03:23 AM
On Wed, 02 Jul 2014 16:57:27 +1000, Gordon Levi wrote:

> Martin Gregorie <martin@address-in-sig.invalid> wrote:
> 
>>Is there a JDBC driver or non-JDBC class library that would give a Java
>>program read/write access to the tables and schema in a MS Access
>>database?
>>
>>I don't need to update the database, just examine the schema and table
>>contents. I don't have a copy od the Access software and nowhere to run
>>it because all my computers run Linux.
> 
> You need a JDBC driver for the Access database. Google turned up this
> one <http://ucanaccess.sourceforge.net/site.html>. The site includes
> some details to help connect the database to Open Office so you can
> examine the tables without writing any Java code.
> 
> I haven't tried it because I use the JDBC-ODBC bridge on a Windows
> machine but that would require an ODBC driver on your Linux computer.

Actually, UCanAccess fits the bill almost perfectly and using SQLeo 
rather than Office Libre as the client application gets closer to what I 
need for looking at the DB schema. Either oocalc or a very simple bit of 
Java that can write query results into a text file can handle my data 
extraction needs.


-- 
martin@   | Martin Gregorie
gregorie. | Essex, UK
org       |
0
Martin
7/2/2014 10:20:40 AM
On Tue, 1 Jul 2014 16:28:25 +0000 (UTC), Martin Gregorie
<martin@address-in-sig.invalid> wrote, quoted or indirectly quoted
someone who said :

>Is there a JDBC driver or non-JDBC class library that would give a Java 
>program read/write access to the tables and schema in a MS Access 
>database? 

have a look at http://mindprod.com/jgloss/jdbc.html

under the ODBC bridge.  I that is how you can get at them, not wildly
efficient, but I doubt you need it.

-- 
Roedy Green Canadian Mind Products http://mindprod.com
A program is only as good as its worst piece of code.
~ Joshua Cramer
0
Roedy
7/2/2014 4:40:30 PM
On 02.07.2014 12:20, Martin Gregorie wrote:

> Actually, UCanAccess fits the bill almost perfectly and using SQLeo
> rather than Office Libre as the client application gets closer to what I
> need for looking at the DB schema. Either oocalc or a very simple bit of
> Java that can write query results into a text file can handle my data
> extraction needs.

Thanks for the pointer!  I always used Open/Libre Office when in needed 
to access an Access database - even for modifications.

Kind regards

	robert


0
Robert
7/2/2014 6:22:26 PM
On 07/01/2014 12:28 PM, Martin Gregorie wrote:
> Is there a JDBC driver or non-JDBC class library that would give a Java
> program read/write access to the tables and schema in a MS Access
> database?
>
> I don't need to update the database, just examine the schema and table
> contents. I don't have a copy od the Access software and nowhere to run
> it because all my computers run Linux.
>
>
I'm pretty sure Open Office will open Access databases?

0
Jeff
7/2/2014 9:08:56 PM
On Wed, 02 Jul 2014 09:40:30 -0700, Roedy Green wrote:

> On Tue, 1 Jul 2014 16:28:25 +0000 (UTC), Martin Gregorie
> <martin@address-in-sig.invalid> wrote, quoted or indirectly quoted
> someone who said :
> 
>>Is there a JDBC driver or non-JDBC class library that would give a Java
>>program read/write access to the tables and schema in a MS Access
>>database?
> 
> have a look at http://mindprod.com/jgloss/jdbc.html
> 
> under the ODBC bridge.  I that is how you can get at them, not wildly
> efficient, but I doubt you need it.
>
There appear to be better solutions than the ODBC bridge.

I haven't tried using it yet, but UCanAccess + Jackcess + HSQLDB together 
make up a JDBC driver, it says, that can read and write Access databases 
without a byte of Windows code being involved. It appears that the whole 
thing fits together like this:
 - Jackcess 2.0 understands the Access file structure at the 
   schema/table/locking(I think) level
 - HSQLDB 2.25 contributes the SQL engine 
 - UCanAccess 2.x.x binds it all together and turns it into a functional 
   JDBC driver.

I like JDBC: I've been using it for a while now to access a PostgreSQL 
database. Performnance is better than I initially expected and it pretty 
much 'just works' and keeps on doing so despite software, hardware and OS 
upgrades.

 
-- 
martin@   | Martin Gregorie
gregorie. | Essex, UK
org       |
0
Martin
7/2/2014 9:28:01 PM
On Wed, 02 Jul 2014 17:08:56 -0400, Jeff Higgins wrote:

> On 07/01/2014 12:28 PM, Martin Gregorie wrote:
>> Is there a JDBC driver or non-JDBC class library that would give a Java
>> program read/write access to the tables and schema in a MS Access
>> database?
>>
>> I don't need to update the database, just examine the schema and table
>> contents. I don't have a copy od the Access software and nowhere to run
>> it because all my computers run Linux.
>>
>>
> I'm pretty sure Open Office will open Access databases?
>
Yes, its known to do that, but the usual run of ODBC/JDBC drivers almost 
certainly expect the JET database server to be involved. I don't have JET 
or an OS it would run on, hence my interest in using UCanAccess.

UCanAccess is a JDBC driver written in Java that incorporates Java class 
libraries to give it direct access to the Access database files and an 
SQL engine. Because if this it has been shown to let Libre Office and 
Open Office, running under Linux, read and write Access database files 
installed locally on the same Linux box. This is exactly what I need.


-- 
martin@   | Martin Gregorie
gregorie. | Essex, UK
org       |
0
Martin
7/2/2014 10:39:49 PM
On 7/1/2014 11:11 PM, markspace wrote:
> On 7/1/2014 7:47 PM, Arne Vajhøj wrote:
>> The semi-official way to do it is to use the JDBC ODBC bridge.
>>
>> But that uses the Access ODBC driver which only exist on Windows.
>>
>> You need a Windows computer.
>
> Two of the links I posted used Java only, and therefore will run on any
> system that runs a JRE/JDK.

Really just one. The second software use the first software
for file access.

Such software do exist.

But it is based on reverse engineering. If it works then fine, but
if it don't then little to do about it.

Arne





0
UTF
7/3/2014 1:05:19 AM
On 7/2/2014 6:03 AM, Martin Gregorie wrote:
> On Tue, 01 Jul 2014 09:55:30 -0700, markspace wrote:
>> You might need an older copy of the JDK because the driver was removed
>> in JDK 8.  You can find drivers in a couple of places:
>>
>> http://jackcess.sourceforge.net/
>> http://ucanaccess.sourceforge.net/site.html
>>
> Thanks for those references. They look like exactly what I was hoping to
> find. So does the SQLeo application which is referenced from the
> UCanAcccess website.

If they suit your needs then fine.

> I kind of skipped over them  at first: my bad. My excuse is that I'd been
> searching for ODBC/JDBC drivers earlier and had only found expensive paid-
> for offerings (the same price or more than Access itself) that all
> targeted Access the database server rather than reading the file directly
> as I now see that Jackcess does.

There are no such thing as a MS Access database server.

MS Access is always embedded.

Arne


0
UTF
7/3/2014 1:06:37 AM
On 7/2/2014 5:28 PM, Martin Gregorie wrote:
> On Wed, 02 Jul 2014 09:40:30 -0700, Roedy Green wrote:
>
>> On Tue, 1 Jul 2014 16:28:25 +0000 (UTC), Martin Gregorie
>> <martin@address-in-sig.invalid> wrote, quoted or indirectly quoted
>> someone who said :
>>
>>> Is there a JDBC driver or non-JDBC class library that would give a Java
>>> program read/write access to the tables and schema in a MS Access
>>> database?
>>
>> have a look at http://mindprod.com/jgloss/jdbc.html
>>
>> under the ODBC bridge.  I that is how you can get at them, not wildly
>> efficient, but I doubt you need it.
>>
> There appear to be better solutions than the ODBC bridge.
>
> I haven't tried using it yet, but UCanAccess + Jackcess + HSQLDB together
> make up a JDBC driver, it says, that can read and write Access databases
> without a byte of Windows code being involved. It appears that the whole
> thing fits together like this:
>   - Jackcess 2.0 understands the Access file structure at the
>     schema/table/locking(I think) level
>   - HSQLDB 2.25 contributes the SQL engine
>   - UCanAccess 2.x.x binds it all together and turns it into a functional
>     JDBC driver.

I would be reluctant to consider some reverse engineered code
better in general than what Oracle and MS support.

But you have a requirement to run on Linux and the Oracle & MS
solution only runs on Windows so in that context it is definitely better.

Arne


0
UTF
7/3/2014 1:09:38 AM
On Wed, 02 Jul 2014 21:06:37 -0400, Arne Vajhøj wrote:

> On 7/2/2014 6:03 AM, Martin Gregorie wrote:
>> On Tue, 01 Jul 2014 09:55:30 -0700, markspace wrote:
>>> You might need an older copy of the JDK because the driver was removed
>>> in JDK 8.  You can find drivers in a couple of places:
>>>
>>> http://jackcess.sourceforge.net/
>>> http://ucanaccess.sourceforge.net/site.html
>>>
>> Thanks for those references. They look like exactly what I was hoping
>> to find. So does the SQLeo application which is referenced from the
>> UCanAcccess website.
> 
> If they suit your needs then fine.
> 
>> I kind of skipped over them  at first: my bad. My excuse is that I'd
>> been searching for ODBC/JDBC drivers earlier and had only found
>> expensive paid-
>> for offerings (the same price or more than Access itself) that all
>> targeted Access the database server rather than reading the file
>> directly as I now see that Jackcess does.
> 
> There are no such thing as a MS Access database server.
>
What about the JET server?
 

-- 
martin@   | Martin Gregorie
gregorie. | Essex, UK
org       |
0
Martin
7/3/2014 9:48:52 AM
On 7/3/2014 5:48 AM, Martin Gregorie wrote:
> On Wed, 02 Jul 2014 21:06:37 -0400, Arne Vajhøj wrote:
>
>> On 7/2/2014 6:03 AM, Martin Gregorie wrote:
>>> On Tue, 01 Jul 2014 09:55:30 -0700, markspace wrote:
>>>> You might need an older copy of the JDK because the driver was removed
>>>> in JDK 8.  You can find drivers in a couple of places:
>>>>
>>>> http://jackcess.sourceforge.net/
>>>> http://ucanaccess.sourceforge.net/site.html
>>>>
>>> Thanks for those references. They look like exactly what I was hoping
>>> to find. So does the SQLeo application which is referenced from the
>>> UCanAcccess website.
>>
>> If they suit your needs then fine.
>>
>>> I kind of skipped over them  at first: my bad. My excuse is that I'd
>>> been searching for ODBC/JDBC drivers earlier and had only found
>>> expensive paid-
>>> for offerings (the same price or more than Access itself) that all
>>> targeted Access the database server rather than reading the file
>>> directly as I now see that Jackcess does.
>>
>> There are no such thing as a MS Access database server.
>>
> What about the JET server?

Never heard about it.

Google seems to indicate that it is a Solaris admin tool of
some kind.

What is it?

Arne


0
UTF
7/3/2014 8:31:33 PM
On Thu, 03 Jul 2014 16:31:33 -0400, Arne Vajhøj wrote:

> On 7/3/2014 5:48 AM, Martin Gregorie wrote:
>
>> What about the JET server?
> 
> Never heard about it.
> 
> Google seems to indicate that it is a Solaris admin tool of some kind.
> 
> What is it?
>
I think a number of products from different sources share the JET moniker 
but AFAIK only MS use it for a database engine.
 
Its the database server part of MS Access. Add a graphical package to it 
and there's your desktop Access app. It can use ODBC to connect to other 
databases or to accept ODBC connections from clients. And, of course, 
wherever ODBC goes, JDBC can go too. 

More than yo ever wanted to know can be found here:
https://en.wikipedia.org/wiki/Microsoft_Access

I've known the name for years (it stands for Joint Engine Technology) and 
that it was the guts of Access but never more than  that. Now you know 
why I was surprised and gratified to find that there is a JDBC driver 
that can access .MDB files without talking to the MS Jet database engine.

There is also an ODBC equivalent, written in C++, to the JDBC UCanAccess 
driver but it can only read .mdb files.


-- 
martin@   | Martin Gregorie
gregorie. | Essex, UK
org       |
0
Martin
7/3/2014 10:03:27 PM
On Tue, 1 Jul 2014 16:28:25 +0000 (UTC), Martin Gregorie
<martin@address-in-sig.invalid> wrote, quoted or indirectly quoted
someone who said :

>Is there a JDBC driver or non-JDBC class library that would give a Java 
>program read/write access to the tables and schema in a MS Access 
>database? 

Would a solution of this form be feasible?

1. Persuade Access to export its data in some simple form, e.g. CSV
2. import into some more tractable database like PostGre.
-- 
Roedy Green Canadian Mind Products http://mindprod.com
A program is only as good as its worst piece of code.
~ Joshua Cramer
0
Roedy
7/5/2014 11:28:02 PM
On Sat, 05 Jul 2014 16:28:02 -0700, Roedy Green wrote:

> On Tue, 1 Jul 2014 16:28:25 +0000 (UTC), Martin Gregorie
> <martin@address-in-sig.invalid> wrote, quoted or indirectly quoted
> someone who said :
> 
>>Is there a JDBC driver or non-JDBC class library that would give a Java
>>program read/write access to the tables and schema in a MS Access
>>database?
> 
> Would a solution of this form be feasible?
> 
> 1. Persuade Access to export its data in some simple form, e.g. CSV 2.
> import into some more tractable database like PostGre.

Thats not needed: just install the UCanAccess JDBC driver.

There can be major problems with moving data from one RDBMS to another. 
While its relatively simple to do if the source schema was fully 
normalised, i.e. all tables are in 3NF, there are still potential 
problems that arise from differences between SQL dialects since very few 
RDBMSs implement fully standards-compliant SQL: PostgreSQL is one of the 
better ones. Common problem areas include migrating schemas between RDBMSs 
that implement auto-incrementing data types and those that use sequences 
and there can be problems with BLOBs and CLOBs.

A big issue with Access databases is that they have often been cobbled 
together by a non-specialist who have never heard of normalisation, 
referential integrity or prime keys and who think that tables containing 
arrays and lots of derived values are the bee's knees. Don't ask me how I 
know.....
   

-- 
martin@   | Martin Gregorie
gregorie. | Essex, UK
org       |
0
Martin
7/6/2014 8:51:04 AM
Martin Gregorie <martin@address-in-sig.invalid> wrote:

>On Sat, 05 Jul 2014 16:28:02 -0700, Roedy Green wrote:
>
>> On Tue, 1 Jul 2014 16:28:25 +0000 (UTC), Martin Gregorie
>> <martin@address-in-sig.invalid> wrote, quoted or indirectly quoted
>> someone who said :
>> 
>>>Is there a JDBC driver or non-JDBC class library that would give a Java
>>>program read/write access to the tables and schema in a MS Access
>>>database?
>> 
>> Would a solution of this form be feasible?
>> 
>> 1. Persuade Access to export its data in some simple form, e.g. CSV 2.
>> import into some more tractable database like PostGre.
>
>Thats not needed: just install the UCanAccess JDBC driver.

Did it all work as expected? My application needs a major upgrade and
it would be a good time to throw out the JDBC-ODBC bridge.

[snip]
0
Gordon
7/6/2014 9:01:18 AM
On 7/5/2014 7:28 PM, Roedy Green wrote:
> On Tue, 1 Jul 2014 16:28:25 +0000 (UTC), Martin Gregorie
> <martin@address-in-sig.invalid> wrote, quoted or indirectly quoted
> someone who said :
>
>> Is there a JDBC driver or non-JDBC class library that would give a Java
>> program read/write access to the tables and schema in a MS Access
>> database?
>
> Would a solution of this form be feasible?
>
> 1. Persuade Access to export its data in some simple form, e.g. CSV
> 2. import into some more tractable database like PostGre.

If he had had a Windows system: yes. But apparently he do not.

Anyway he has found a library that can read the MDB file, so
he should be good.

Arne


0
ISO
7/6/2014 3:55:48 PM
On 7/3/2014 6:03 PM, Martin Gregorie wrote:
> On Thu, 03 Jul 2014 16:31:33 -0400, Arne Vajhøj wrote:
>
>> On 7/3/2014 5:48 AM, Martin Gregorie wrote:
>>
>>> What about the JET server?
>>
>> Never heard about it.
>>
>> Google seems to indicate that it is a Solaris admin tool of some kind.
>>
>> What is it?
>>
> I think a number of products from different sources share the JET moniker
> but AFAIK only MS use it for a database engine.
>
> Its the database server part of MS Access.

There are no database server in MS Access. It is embedded database only.

> More than yo ever wanted to know can be found here:
> https://en.wikipedia.org/wiki/Microsoft_Access

That articles not not mention any "JET server" at all. In fact it
explicit states:

"Microsoft Access is a file server-based database"

> I've known the name for years (it stands for Joint Engine Technology) and
> that it was the guts of Access but never more than  that. Now you know
> why I was surprised and gratified to find that there is a JDBC driver
> that can access .MDB files without talking to the MS Jet database engine.

The data exist in a disk file. Of course you can write Java code that
can read that file.

But MS only supports going through the Jet Engine.

Arne


0
UTF
7/6/2014 4:01:32 PM
On Sun, 06 Jul 2014 12:01:32 -0400, Arne Vajhøj wrote:

> On 7/3/2014 6:03 PM, Martin Gregorie wrote:
>> On Thu, 03 Jul 2014 16:31:33 -0400, Arne Vajhøj wrote:
>>
>>> On 7/3/2014 5:48 AM, Martin Gregorie wrote:
>>>
>>>> What about the JET server?
>>>
>>> Never heard about it.
>>>
>>> Google seems to indicate that it is a Solaris admin tool of some kind.
>>>
>>> What is it?
>>>
>> I think a number of products from different sources share the JET
>> moniker but AFAIK only MS use it for a database engine.
>>
>> Its the database server part of MS Access.
> 
> There are no database server in MS Access. It is embedded database only.
> 
>> More than yo ever wanted to know can be found here:
>> https://en.wikipedia.org/wiki/Microsoft_Access
> 
> That articles not not mention any "JET server" at all. In fact it
> explicit states:
> 
> "Microsoft Access is a file server-based database"
>
This is "no mention at all"?

"Microsoft Access, also known as Microsoft Office Access, is a database 
management system from Microsoft that combines the relational Microsoft 
Jet Database Engine with a graphical user interface and software-
development tools." 
- the first sentence on the page I posted the URL for.

> The data exist in a disk file. Of course you can write Java code that
> can read that file.
>
Of course, and the Java code exists. Jackcess is a Java class library 
that implements low level access to it and UCanAccess is a Java class 
library is a JDBC driver which delegates file access to Jackcess and SQL 
interpretation to the HSQLDB class library.
 
> But MS only supports going through the Jet Engine.
>
Indeed, and very many of the ODBC Access drivers connect to it, though it 
can be quite difficult to understand exactly what any particular driver 
does because descriptions provided by the authors and vendors of third 
party drivers are often anything but clear. I think MS-provided ODBC 
drivers use the JET Database Engine. 

And, since at least some JDBC Access drivers are wrappers for ODBC 
drivers, its safe to say that the same applies to them too. My original 
search, which was probably for "JDBC Access", only found a few commercial 
drivers with eye-watering prices and which failed to say whether or not 
they used the JET Database Engine to handle Access database files.
 

-- 
martin@   | Martin Gregorie
gregorie. | Essex, UK
org       |
0
Martin
7/6/2014 4:42:16 PM
On 7/6/2014 12:42 PM, Martin Gregorie wrote:
> On Sun, 06 Jul 2014 12:01:32 -0400, Arne Vajhøj wrote:
>
>> On 7/3/2014 6:03 PM, Martin Gregorie wrote:
>>> On Thu, 03 Jul 2014 16:31:33 -0400, Arne Vajhøj wrote:
>>>
>>>> On 7/3/2014 5:48 AM, Martin Gregorie wrote:
>>>>
>>>>> What about the JET server?
>>>>
>>>> Never heard about it.
>>>>
>>>> Google seems to indicate that it is a Solaris admin tool of some kind.
>>>>
>>>> What is it?
>>>>
>>> I think a number of products from different sources share the JET
>>> moniker but AFAIK only MS use it for a database engine.
>>>
>>> Its the database server part of MS Access.
>>
>> There are no database server in MS Access. It is embedded database only.
>>
>>> More than yo ever wanted to know can be found here:
>>> https://en.wikipedia.org/wiki/Microsoft_Access
>>
>> That articles not not mention any "JET server" at all. In fact it
>> explicit states:
>>
>> "Microsoft Access is a file server-based database"
>>
> This is "no mention at all"?

Correct.

> "Microsoft Access, also known as Microsoft Office Access, is a database
> management system from Microsoft that combines the relational Microsoft
> Jet Database Engine with a graphical user interface and software-
> development tools."
> - the first sentence on the page I posted the URL for.

I can really not see the term "JET server" anywhere in that paragraph.

It talks about DBMS and engine, which does not imply a server. They
apply to both database servers and embedded databases.

Arne




0
UTF
7/6/2014 4:45:01 PM
On 7/6/2014 12:42 PM, Martin Gregorie wrote:
> On Sun, 06 Jul 2014 12:01:32 -0400, Arne Vajhøj wrote:
>> The data exist in a disk file. Of course you can write Java code that
>> can read that file.
>>
> Of course, and the Java code exists. Jackcess is a Java class library
> that implements low level access to it and UCanAccess is a Java class
> library is a JDBC driver which delegates file access to Jackcess and SQL
> interpretation to the HSQLDB class library.
>
>> But MS only supports going through the Jet Engine.
>>
> Indeed, and very many of the ODBC Access drivers connect to it, though it
> can be quite difficult to understand exactly what any particular driver
> does because descriptions provided by the authors and vendors of third
> party drivers are often anything but clear. I think MS-provided ODBC
> drivers use the JET Database Engine.

I am sure it does.

> And, since at least some JDBC Access drivers are wrappers for ODBC
> drivers, its safe to say that the same applies to them too. My original
> search, which was probably for "JDBC Access", only found a few commercial
> drivers with eye-watering prices and which failed to say whether or not
> they used the JET Database Engine to handle Access database files.

Very likely they do. Selling a commercial product based on reverse
engineering of MDB format would be rather risky.

Arne


0
UTF
7/6/2014 4:47:29 PM
On Sun, 06 Jul 2014 19:01:18 +1000, Gordon Levi wrote:

> Martin Gregorie <martin@address-in-sig.invalid> wrote:
> 
>>On Sat, 05 Jul 2014 16:28:02 -0700, Roedy Green wrote:
>>
>>> On Tue, 1 Jul 2014 16:28:25 +0000 (UTC), Martin Gregorie
>>> <martin@address-in-sig.invalid> wrote, quoted or indirectly quoted
>>> someone who said :
>>> 
>>>>Is there a JDBC driver or non-JDBC class library that would give a
>>>>Java program read/write access to the tables and schema in a MS Access
>>>>database?
>>> 
>>> Would a solution of this form be feasible?
>>> 
>>> 1. Persuade Access to export its data in some simple form, e.g. CSV 2.
>>> import into some more tractable database like PostGre.
>>
>>Thats not needed: just install the UCanAccess JDBC driver.
> 
> Did it all work as expected? My application needs a major upgrade and it
> would be a good time to throw out the JDBC-ODBC bridge.
>
I haven't got around to trying it yet: sorry. I'm still in the process of 
getting my head round the system I'm taking over as superuser/webmaster. 
Its written in ASP.net running under IIS and using an Access database 
(obviously) which is 'soon' to be replaced by an equivalent level SQL 
Server[1]. As I wouldn't know ASP.net from S/360 BAL and anyway there 
seems to be no schema definition or ERD. Since I don't have anything that 
can run Access code but do have copies of the .mdf files, I'm hoping to 
use UCanAccess plus a client such as SQLeo to extract a schema diagram I 
can make sense of and, as a bonus, use that or write a simple Java app 
that can extract one or two lists that I'd find useful as webmaster.

Hopefully I'll find time over the next week or two to install UCanAccess 
and try some of this stuff out. 

When I do, I'll post my findings here.

However, I believe MS is in the process of dumping Access in favour of 
supporting just SQL Server, which is why we're due to migrate our system 
to SQL Server Compact which seems to be a free version of the DBMS. You 
might want to consider that too. And before you ask, no, I haven't even 
begun to look for stand-alone JDBC drivers for it. 

[1] SQL Server Compact. Literally all I know about it at this point is 
that it stores data in .sdf files just as Access stores data in .mdb 
files, so its no use at all to ask me anything more about it.


-- 
martin@   | Martin Gregorie
gregorie. | Essex, UK
org       |
0
Martin
7/6/2014 5:11:08 PM
On 7/6/2014 1:11 PM, Martin Gregorie wrote:
> On Sun, 06 Jul 2014 19:01:18 +1000, Gordon Levi wrote:
>
>> Martin Gregorie <martin@address-in-sig.invalid> wrote:
>>
>>> On Sat, 05 Jul 2014 16:28:02 -0700, Roedy Green wrote:
>>>
>>>> On Tue, 1 Jul 2014 16:28:25 +0000 (UTC), Martin Gregorie
>>>> <martin@address-in-sig.invalid> wrote, quoted or indirectly quoted
>>>> someone who said :
>>>>
>>>>> Is there a JDBC driver or non-JDBC class library that would give a
>>>>> Java program read/write access to the tables and schema in a MS Access
>>>>> database?
>>>>
>>>> Would a solution of this form be feasible?
>>>>
>>>> 1. Persuade Access to export its data in some simple form, e.g. CSV 2.
>>>> import into some more tractable database like PostGre.
>>>
>>> Thats not needed: just install the UCanAccess JDBC driver.
>>
>> Did it all work as expected? My application needs a major upgrade and it
>> would be a good time to throw out the JDBC-ODBC bridge.
>>
> I haven't got around to trying it yet: sorry. I'm still in the process of
> getting my head round the system I'm taking over as superuser/webmaster.
> Its written in ASP.net running under IIS and using an Access database
> (obviously) which is 'soon' to be replaced by an equivalent level SQL
> Server[1]. As I wouldn't know ASP.net from S/360 BAL and anyway there
> seems to be no schema definition or ERD. Since I don't have anything that
> can run Access code but do have copies of the .mdf files, I'm hoping to
                                                 ^^^^
> use UCanAccess plus a client such as SQLeo to extract a schema diagram I
> can make sense of and, as a bonus, use that or write a simple Java app
> that can extract one or two lists that I'd find useful as webmaster.

Pull the break!

Is is a MDB file or a MDF file?

MDB => Access

MDF => SQLServer

Arne

0
UTF
7/6/2014 7:17:10 PM
On 7/6/2014 1:11 PM, Martin Gregorie wrote:
> However, I believe MS is in the process of dumping Access in favour of
> supporting just SQL Server, which is why we're due to migrate our system

That process has been ongoing for the past 15+ years and I suspect that
it will be ongoing for the next 15 years as well.

With the widely usage of MS Office, the large number of MS Access
databases out there and the support for MS Access databases by
third party tools then it is like getting rid of COBOL.

> to SQL Server Compact which seems to be a free version of the DBMS. You
> might want to consider that too. And before you ask, no, I haven't even
> begun to look for stand-alone JDBC drivers for it.
>
> [1] SQL Server Compact. Literally all I know about it at this point is
> that it stores data in .sdf files just as Access stores data in .mdb
> files, so its no use at all to ask me anything more about it.

Contrary to the name SQLServer CE is not an edition of SQLServer.

SQLServer CE is an embedded database that are not very widely known,
but it is actually quite nice. If I had to use an embedded database
in .NET environment, then it would be my pick too.

Unfortunately no JBDC driver.

MS do supply a JDBC driver for the real SQLServer.

Arne


0
UTF
7/6/2014 7:27:47 PM
On Sun, 06 Jul 2014 15:17:10 -0400, Arne Vajhøj wrote:

> 
> Is is a MDB file or a MDF file?
>
MDB (unspotted finger trouble). 

I haven't had anything to do with Sybase since version 11. That was just 
before MS bought a license for version 12, applied their own tweaks and 
started to sell it as SQL Server. I imagine its diverged quite a bit 
since then. For starters, Sybase 11 used raw disk partitions as its data 
store, rather than files.  

I haven't knowingly used SQL Server at all.


-- 
martin@   | Martin Gregorie
gregorie. | Essex, UK
org       |
0
Martin
7/6/2014 7:30:33 PM
On Sun, 06 Jul 2014 15:27:47 -0400, Arne Vajhøj wrote:

> Contrary to the name SQLServer CE is not an edition of SQLServer.
>
Yes, I know that: I've read the summary. Presumably SQL Server CE is now 
as dead as WinCE.

I don't know what exactly  SQL Server Compact is, but it isn't SQL Server 
CE. Maybe MS has (slightly) reused a name, but SQL Server Compact seems 
to be a free-to-download replacement for Access - it certainly uses the 
same paradigm of having mapping both metadata (schema) and data into a 
single file with an .sdf extension.

> SQLServer CE is an embedded database that are not very widely known, but
> it is actually quite nice. If I had to use an embedded database in .NET
> environment, then it would be my pick too.
> 
Maybe SQL Server Compact is SQL Server CE recompiled to run on Windows 
Server boxes?
 
> MS do supply a JDBC driver for the real SQLServer.
>
Good, and if SQL Server is a true server family, as opposed to IBM-style 
product naming kludge[*], it will work with all members of the family? 
That would be a really good thing to have.
  
[*] I'm thinking of SNA, where any commonality between SNA/LU2 (aka 3270 
Bisync) and SNA/LU6 is a figment of a salesdroid#s imagination. 


-- 
martin@   | Martin Gregorie
gregorie. | Essex, UK
org       |
0
Martin
7/6/2014 10:37:49 PM
On 7/6/2014 6:37 PM, Martin Gregorie wrote:
> On Sun, 06 Jul 2014 15:27:47 -0400, Arne Vajhøj wrote:
>
>> Contrary to the name SQLServer CE is not an edition of SQLServer.
>>
> Yes, I know that: I've read the summary. Presumably SQL Server CE is now
> as dead as WinCE.
>
> I don't know what exactly  SQL Server Compact is, but it isn't SQL Server
> CE. Maybe MS has (slightly) reused a name, but SQL Server Compact seems
> to be a free-to-download replacement for Access - it certainly uses the
> same paradigm of having mapping both metadata (schema) and data into a
> single file with an .sdf extension.
>
>> SQLServer CE is an embedded database that are not very widely known, but
>> it is actually quite nice. If I had to use an embedded database in .NET
>> environment, then it would be my pick too.
>>
> Maybe SQL Server Compact is SQL Server CE recompiled to run on Windows
> Server boxes?

SQLServer Compact is often abbreviated SQLServer CE.

I think:
   CE = Compact Edition.

http://en.wikipedia.org/wiki/SQL_Server_Compact

Arne

0
UTF
7/6/2014 11:06:30 PM
On 7/6/2014 3:30 PM, Martin Gregorie wrote:
> On Sun, 06 Jul 2014 15:17:10 -0400, Arne Vajhøj wrote:
>> Is is a MDB file or a MDF file?
>>
> MDB (unspotted finger trouble).

It happens.

> I haven't had anything to do with Sybase since version 11. That was just
> before MS bought a license for version 12, applied their own tweaks and
> started to sell it as SQL Server.

????

The last Sybase version MS got from Sybase was 4.x in 1992. They did
not get 10.x from 1993 / 11.x from 1995 / 12.x from 1999.

Arne





0
UTF
7/6/2014 11:10:46 PM
On Sun, 06 Jul 2014 19:10:46 -0400, Arne Vajhøj wrote:

> On 7/6/2014 3:30 PM, Martin Gregorie wrote:
>> On Sun, 06 Jul 2014 15:17:10 -0400, Arne Vajhøj wrote:
>>> Is is a MDB file or a MDF file?
>>>
>> MDB (unspotted finger trouble).
> 
> It happens.
> 
>> I haven't had anything to do with Sybase since version 11. That was
>> just before MS bought a license for version 12, applied their own
>> tweaks and started to sell it as SQL Server.
> 
> ????
> 
> The last Sybase version MS got from Sybase was 4.x in 1992. They did not
> get 10.x from 1993 / 11.x from 1995 / 12.x from 1999.
>
OK, if you say so. Are you sure they didn't get an initial license in '92 
and then re-licensed for a later version on '96 or therabouts? 

My recollection is that I was working with Sybase 11 in 95/96 on NCR UNIX 
boxes and that was when MS announced that it had bought a license for 
Sybase. Back in '92 I'd been involved in briefly looking at Sybase for a 
project we'd been designing as an alternative for using ICL mainframe 
kit: I'd didn't do any serious work with Sybase databases before the mid 
1990s and so the news that MS had bought a license in '92 would likely 
have gone straight over my head: most of the database stuff I did back 
then was with IDMS rather than relational databases.
  

-- 
martin@   | Martin Gregorie
gregorie. | Essex, UK
org       |
0
Martin
7/7/2014 11:26:01 AM
On Sun, 06 Jul 2014 19:06:30 -0400, Arne Vajhøj wrote:

> On 7/6/2014 6:37 PM, Martin Gregorie wrote:
>> On Sun, 06 Jul 2014 15:27:47 -0400, Arne Vajhøj wrote:
>>
>>> Contrary to the name SQLServer CE is not an edition of SQLServer.
>>>
>> Yes, I know that: I've read the summary. Presumably SQL Server CE is
>> now as dead as WinCE.
>>
>> I don't know what exactly  SQL Server Compact is, but it isn't SQL
>> Server CE. Maybe MS has (slightly) reused a name, but SQL Server
>> Compact seems to be a free-to-download replacement for Access - it
>> certainly uses the same paradigm of having mapping both metadata
>> (schema) and data into a single file with an .sdf extension.
>>
>>> SQLServer CE is an embedded database that are not very widely known,
>>> but it is actually quite nice. If I had to use an embedded database in
>>> .NET environment, then it would be my pick too.
>>>
>> Maybe SQL Server Compact is SQL Server CE recompiled to run on Windows
>> Server boxes?
> 
> SQLServer Compact is often abbreviated SQLServer CE.
> 
> I think:
>    CE = Compact Edition.
> 
> http://en.wikipedia.org/wiki/SQL_Server_Compact
>
Thanks for that: it clarifies things a lot. 

I use the LK8000 gliding navigation app, which runs on Win CE based 
Personal Navigation Assistants (PNAs), so I was reading the CE 
abbreviation and references to mobile devices as equating CE with Windows 
CE 5/6 and applications that run on it. Pardon my confusion.

So, it looks as though SQL Server Compact is intended to be pretty much a 
drop-in replacement[*] for Access, particularly for systems that use ODBC 
or JDBC as their DBMS interface. I'd wondered, too, if it might suffer 
from the general MS filesize limitation of 4GB. It does.
   
[*] if you ignore the differences between SQL dialects and, possibly, 
differences in the way commit works.
 

-- 
martin@   | Martin Gregorie
gregorie. | Essex, UK
org       |
0
Martin
7/7/2014 11:45:24 AM
On 7/7/2014 7:45 AM, Martin Gregorie wrote:
> On Sun, 06 Jul 2014 19:06:30 -0400, Arne Vajhøj wrote:
>> On 7/6/2014 6:37 PM, Martin Gregorie wrote:
>>> On Sun, 06 Jul 2014 15:27:47 -0400, Arne Vajhøj wrote:
>>>> SQLServer CE is an embedded database that are not very widely known,
>>>> but it is actually quite nice. If I had to use an embedded database in
>>>> .NET environment, then it would be my pick too.
>>>>
>>> Maybe SQL Server Compact is SQL Server CE recompiled to run on Windows
>>> Server boxes?
>>
>> SQLServer Compact is often abbreviated SQLServer CE.
>>
>> I think:
>>     CE = Compact Edition.
>>
>> http://en.wikipedia.org/wiki/SQL_Server_Compact
>>
> Thanks for that: it clarifies things a lot.

> So, it looks as though SQL Server Compact is intended to be pretty much a
> drop-in replacement[*] for Access,

It is.

>                                   particularly for systems that use ODBC
> or JDBC as their DBMS interface.

Not so.

SQLServer CE does not come with ODBC or JDBC at all.

It only comes with OLE DB (ADO) and ADO.NET providers.

>                              I'd wondered, too, if it might suffer
> from the general MS filesize limitation of 4GB. It does.

The general MS file size limitation is 16 TB for older Windows
and 256 TB for newer Windows.

(well it was 2/4 GB if we go back to Win9x in the 1990's)

But yes - SQLServer CE is limited to 4 GB.

I don't think it is that bad. If you need more space, then you
should probably go SQLServer anyway.

Arne



0
UTF
7/7/2014 9:50:19 PM
 The general MS file size limitation is 16 TB for older Windows and 256
> TB for newer Windows.
> 
> (well it was 2/4 GB if we go back to Win9x in the 1990's)
>
Yep - FAT32 limitation.
 
> But yes - SQLServer CE is limited to 4 GB.
> 
> I don't think it is that bad. If you need more space, then you should
> probably go SQLServer anyway.
>
True enough. 

I get the impression that FAT-32 is still alive and kicking as the 
default FS on a lot of small/medium flash drives, so they just might have 
retained the 4GB limit to make it easier to backup or transfer database 
files on these media.


-- 
martin@   | Martin Gregorie
gregorie. | Essex, UK
org       |
0
Martin
7/7/2014 10:34:28 PM
On 7/7/2014 6:34 PM, Martin Gregorie wrote:
>> But yes - SQLServer CE is limited to 4 GB.
>>
>> I don't think it is that bad. If you need more space, then you should
>> probably go SQLServer anyway.
>>
> True enough.
>
> I get the impression that FAT-32 is still alive and kicking as the
> default FS on a lot of small/medium flash drives, so they just might have
> retained the 4GB limit to make it easier to backup or transfer database
> files on these media.

http://blogs.msdn.com/b/stevelasker/archive/2006/11/13/doc-storage-in-sqlce-and-the-4gb-limit.aspx

indicates that it is the device ancestry that is the main cause.

Arne


0
UTF
7/7/2014 11:24:04 PM
Reply: