How can I get all the data types specific for a certain version of MS Access or/and every versions of MS Access?

Question

Hello! I am trying to list data types from Microsoft Access 2000-2007 (depending on the MS Access database version) in a combobox for a C# program. I want my program to be capable of opening MS Access 2000-2007 databases. If I open a MS Access 2003 I wish my program to be capable of using the datatypes of any data specific to MS Access 2003 and list those data types in a combobox. If I open a MS Access 2007 database it will be for datatypes of any data for MS Access 2007... I was wondering if I could use ACEDAO for that (is ACEDAO only compatible for MS Access 2007?). Also, how can I get all the data types specific for a certain version of MS Access or/and every versions of MS Access (2000-2007 in my case)?

Answer 1

ACEDAO is backwardly compatible with all previous versions of Jet. But the compatibility is provided in different ways. For instance, if you look directly at the object model of ACEDAO, you'll see that there's both a Field data type and a Field2 data type. Within Access 2007, you don't see that. My surmise is that the Field data type is for MDBs (Jet 4 and earlier), and the Field2 data type is for ACCDB, because the ACCDB has new data types that have properties and methods lacking in the earlier field type.

For instance, the Field2 data type has the AppendOnly property that is used in append-only memo fields, a new feature in ACE. Another new property is IsComplex, which I'm guessing is there to support the new multi-value data type. There's also LoadFromFile and SaveToFile methods that I don't recognize the purpose of.

Now, that's just one object in the DAO library where there are variations depending on what file format you're using. I haven't looked for other such variations, but they might be there.

So, you'd still need to know what applied to different versions, even though ACEDAO is going to give you a superset that will be able to operate on all older versions. If you only need to work with ACCDB and Jet 4 MDBs, I'd think ACEDAO is going to suffice.

But, I'm just guessing based on what I understand it is that you want to do.

Answer 2

As far as I can tell the ACEDAO version of DataTypeEnum lists all data types that have everr been available (though you'd have to look for a variation of dbMemo to be able to distinguish a Hyperlink if you consider this to be a distinct data type.

However, it also lists additional types it seemingly does not support: dbfloat (21) as distinct from dbDouble (7); dbTime (22) as distinct from dbDate (8); dbNumeric (19) as distinct from dbDouble (20); distinct types for dbVarBinary, dbBinary and dbLongBinary.

Also, I don't think it is possible to interrogate a particular version of the engine at run time to enumerate the data types supports.

Therefore, I don't think simply listing the types from ACEDAO enumeration will be very useful. Rather, I think you need to know in advance the data types each version of the engine supports then select the appropriate set at run time.

Jet 4.0 data types (Access2000 to 2003 inclusive) and the various synonyms can be found here; it should also give some hints about which types are new to Jet 4.0 i.e. to help you support earlier versions of the engine.

ACE (Access2007) added an Attachment type; it also added multivalued ("complex") types which you will need to decide for yourself whether these are distinct types (or merely variations on existing data types) based on what you are trying to achieve.