#129 Database#getFileFormat() fails for .mdb created by ADOX

[Gord Thompson]

(via http://sourceforge.net/p/ucanaccess/discussion/help/thread/22af6ee9/)

Steps to recreate:

Create an empty .mdb file by running the following VBScript using the 32-bit version of CSCRIPT.EXE

Option Explicit
Dim cat
Set cat = CreateObject("ADOX.Catalog")
cat.Create _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\Users\Public\adox2003.mdb;"
Set cat = Nothing

 
The following Java code

package com.example;

import com.healthmarketscience.jackcess.Database;
import com.healthmarketscience.jackcess.DatabaseBuilder;

import java.io.File;

public class Main {

    public static void main(String[] args) {
        String dbPath = "C:/Users/Public/adox2003.mdb";
        try (Database db = DatabaseBuilder.open(new File(dbPath))) {
            System.out.println("Database opened.");
            System.out.println("Checking database format ...");
            try {
                System.out.println(db.getFileFormat().toString());
            } catch (IllegalStateException ise) {
                System.out.println("  IllegalStateException: " + ise.getMessage());
            }
            System.out.println("Looking for System tables ...");
            for (String tblName : db.getSystemTableNames()) {
                System.out.println("  " + tblName);
            }
        } catch (Exception e) {
            e.printStackTrace(System.out);
        }
    }
}

 
produces

Database opened.
Checking database format ...
  IllegalStateException: Could not determine FileFormat (Db=adox2003.mdb)
Looking for System tables ...
  MSysACEs
  MSysObjects
  MSysQueries
  MSysRelationships

 
Note that even though the getFileFormat() call fails, Jackcess is still able to read the database.

The error goes away if we simply open the .mdb file in Access (which recognizes it as an "Access 2002-2003 database") and then immediately close Access.

[Gord Thompson]

Additional information:

The issue does not arise when ADOX uses the Microsoft.ACE.OLEDB.12.0 provider to create an .accdb file. In that case the Java code from the previous post does recognize the file format:

Database opened.
Checking database format ...
V2007 [VERSION_12]
Looking for System tables ...
  MSysACEs
  MSysComplexColumns
  MSysComplexType_Attachment
  MSysComplexType_Decimal
  MSysComplexType_GUID
  MSysComplexType_IEEEDouble
  MSysComplexType_IEEESingle
  MSysComplexType_Long
  MSysComplexType_Short
  MSysComplexType_Text
  MSysComplexType_UnsignedByte
  MSysObjects
  MSysQueries
  MSysRelationships

 
In addition, there might be a question of what the .mdb file version really is immediately after ADOX has created it. The act of opening the database in Access may in fact silently convert it to "Access 2002-2003 format" (from some other, possibly indeterminate, version) since the .mdb file does grow significantly when Access opens it.

[Venkata Swamy Karukuri]

Jackcess Team, (aganim, jahlborn, javajedi, mdelaurentis),

Can you please let us know if you any plans to fix this in near future?

Thanks
Venky
Original ticket creator: http://sourceforge.net/p/ucanaccess/discussion/help/thread/22af6ee9/

[James Ahlborn]

So, here's the situation. access 2000 and 2002/2003 have the same Jet version (version 4). when i did some testing a while back, the only thing i could find to distinguish an access 2000 db from an access 2002/2003 db was a database property called "AccessVersion". The database generated by the example code above does not include the AccessVersion property (or any database properties). so, at this point in time, i don't know how to identify the version of this file (or even if there is a meaningful difference here). since it is a jet 4 db, then it seems like access 2000 could be a legitimate file format value...?

[Gord Thompson]

Calling it an Access_2000-format database appears to be a reasonably safe bet. The file definitely can be opened in Access 2000; I just tried it. It cannot be opened in Access 97 ("Unrecognized database format"), but that is not surprising.

I believe that technically it is a "generic Jet4 database" (my own term) because after opening it in Access 2000 and immediately closing it the file grew from 64KB to 96KB. As mentioned in my previous post, it looks like Access is silently upgrading the file to a "real" Access version by adding missing properties and objects. When I run the above Java code against the file after being opened by Access 2000 (and immediately closed again)

• it is now recognized by Jackcess as an Access 2000 file, and
• it now has an [MSysAccessObjects] system table.

The console output is

Database opened.
Checking database format ...
  V2000 [VERSION_4]
Looking for System tables ...
  MSysAccessObjects
  MSysACEs
  MSysObjects
  MSysQueries
  MSysRelationships

[James Ahlborn]

That's kind of what i suspected. i don't have access 2000 anymore, so i had no way to test that.

so, it seems like it would be reasonable for getFileFormat() to return 2000 in this situation.

[James Ahlborn]

i guess an alternative would be to add a new FileFormat, something like GENERIC_JET4. this would be the most "accurate" response, but i'm not sure how useful/confusing that would be in general?

[Gord Thompson]

IMO that would actually be better than calling it an "Access_2000" database since it really isn't: it doesn't have a MSysAccessObjects table (and perhaps other Access-specific objects). It might cause an occasional case of confusion but I expect that they will be rare, and I'd prefer to err on the side of giving the "right" (as opposed to "easy") answer.

[Venkata Swamy Karukuri]

Standard JDBCODBC bridge and EasySoft type 2 JDBC drivers returns simply as
ACCESS, though.

On Wed, Nov 25, 2015 at 6:17 PM, Gord Thompson gordonthompson@users.sf.net
wrote:

IMO that would actually be better than calling it an "Access_2000"
database since it really isn't: it doesn't have a MSysAccessObjects table
(and perhaps other Access-specific objects). It might cause an occasional
case of confusion but I expect that they will be rare, and I'd prefer to
err on the side of giving the "right" (as opposed to "easy") answer.

---

• [bugs:#129] http://sourceforge.net/p/jackcess/bugs/129/
  Database#getFileFormat() fails for .mdb created by ADOX*

Status: open
Group: Unassigned
Created: Mon Nov 16, 2015 03:05 PM UTC by Gord Thompson
Last Updated: Sun Nov 22, 2015 03:58 AM UTC
Owner: nobody

(via http://sourceforge.net/p/ucanaccess/discussion/help/thread/22af6ee9/)

Steps to recreate:

Create an empty .mdb file by running the following VBScript using the
32-bit version of CSCRIPT.EXE

Option ExplicitDim catSet cat = CreateObject("ADOX.Catalog")cat.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Users\Public\adox2003.mdb;"Set cat = Nothing

The following Java code

package com.example;
import com.healthmarketscience.jackcess.Database;import com.healthmarketscience.jackcess.DatabaseBuilder;
import java.io.File;
public class Main {

public static void main(String[] args) {
    String dbPath = "C:/Users/Public/adox2003.mdb";
    try (Database db = DatabaseBuilder.open(new File(dbPath))) {
        System.out.println("Database opened.");
        System.out.println("Checking database format ...");
        try {
            System.out.println(db.getFileFormat().toString());
        } catch (IllegalStateException ise) {
            System.out.println("  IllegalStateException: " + ise.getMessage());
        }
        System.out.println("Looking for System tables ...");
        for (String tblName : db.getSystemTableNames()) {
            System.out.println("  " + tblName);
        }
    } catch (Exception e) {
        e.printStackTrace(System.out);
    }
}}

produces

Database opened.
Checking database format ...
IllegalStateException: Could not determine FileFormat (Db=adox2003.mdb)
Looking for System tables ...
MSysACEs
MSysObjects
MSysQueries
MSysRelationships

Note that even though the getFileFormat() call fails, Jackcess is still
able to read the database.

The error goes away if we simply open the .mdb file in Access (which
recognizes it as an "Access 2002-2003 database") and then immediately close
Access.

---

Sent from sourceforge.net because you indicated interest in
https://sourceforge.net/p/jackcess/bugs/129/

To unsubscribe from further messages, please visit
https://sourceforge.net/auth/subscriptions/

[Gord Thompson]

Standard JDBCODBC bridge and EasySoft type 2 JDBC drivers returns simply as
ACCESS, though.

Perhaps, but not only is that technically incorrect (because there is nothing in the database file that is specific to any version of Access), it is also not terribly helpful. The only case I could see for a plain "ACCESS" designation would be for a multi-platform access method like ODBC. For anything specifically designed to work with .mdb and .accdb files (like Jackcess is), just saying "ACCESS" is like just saying "GENERIC".

[James Ahlborn]

I went with the new FileFormat plan. I added FileFormat.GENERIC_JET4 which will be returned for databases with no secondary version information.

Fixed in trunk, will be in the 2.1.3 release.