MsAccess MdbTools with MFC and .NET

• Download SourceCode (.NET, MFC, Win32) - 1.5 MB
• Download Exes (.NET, MFC) and sample database - 950 KB

Introduction

I used to have problems with some Access 97 databases that went corrupt. Also I had instalation problems with the JET Engine for MsAccess. I found MdbTools, and could repair databases or at least get data out. Then I ported MdbTools to MFC and .NET (only read, not write support). I updated the sources to the latest version of mdbtools.

The MsAccess file structure

Pages

All the info of the mdb file is organized as pages. A page is a region in the file of 2k (Jet3) and 4k (Jet4).
So, the file len of MsAccess databases is a multiple of 2,048 (Jet3) or 4,096 (Jet4). For example, the Nwind.mdb file sample contains 1,548,288 / 2048 = 756 pages.
All pages are stored secuentially, and, as they are all the same size, page n starts at n * 2048 + 1 or n * 4096 + 1 in the file (considering n starting at 0).

For example, for a Jet3 database containing 5 pages (including 0 which is the Database Definition page):

Positions differ from Jet3 to Jet4, so all tables will be referred to Jet3.

Page Number	Starts at position (dec)	Size (dec)
0	0	2048
1	2048	2048
2	4096	2048
3	6144	2048
4	8192	2048

Page 0 - Database Definition Page

There is not much information about this page. What is known:

From (hex)	To (hex)	Len (dec)	Format	Description
0x0	0x0	1	Byte	Page type - For the database definition page type is 0
0x1	0x13	19		Unknown
0x14	0x17	4	LE-Int	Jet version of the database (0 for Jet3, 1 for Jet4, 2 for Jet5, 3 for Access 2010
0x18	0x3D	38		Unknown
0x3E	0x41	4	LE-Int	RC4 Key used to encrypt pages (if the database is encrypted)
0x42	0x50	14	Byte	Password (masked)
0x51	0x7FF	1967		Unknown

LE-Int: Refers to Little Endian Int which means that the number is stored from the least significant bit to the most significant bit (LSB-MSB). For example, the number 0xA1B2C3D4 is stored as: D4 C3 B2 A1.

The first byte of each page identifies the page type:

• 0x00 Database definition page. (Always page 0)
• 0x01 Data page
• 0x02 Table definition
• 0x03 Intermediate Index pages
• 0x04 Leaf Index pages
• 0x05 Page Usage Bitmaps (extended page usage)

Page 1

Keeps track of allocated pages in the database.

Page 2 - Table definition page for MSysObjects

MsAccess databases include system tables that contain information about the database itself. They all start with the prefix MSys.
Page 2 contains the Table definition (Page type 0x02) of the table MSysObjects. This table contains all the objects names and (more important), the page number location of them.

The structure of a Table Definition page is:

From (hex)	To (hex)	Len (dec)	Format	Description
0x0	0x0	1	Byte	Page type - For the table definition page type is 2
0x1	0x7	8		Unknown
0x8	0x9	2	LE-Int	Page len
0xC	0xF	4	LE-Int	Number of rows
0x17	0x18	2	LE-Int	Number of variable columns
0x19	0x1A	2	LE-Int	Number of columns
0x1B	0x1E	4	LE-Int	Number of indexes
0x1F	0x22	4	LE-Int	Number of index entries
0x23	0x26	4		Pages usage bitmask
0x27	0x2A	4	LE-Int	Pages with free space bitmask

Column definitions start at 0x2B + (Number of index entries * 8). Column properties go first. Then go column names (in the same order that properties).
Column properties have a fixed size of 18 bytes.

Assuming "Number of index entries" = 2 (for MSysObjects), column properties start at 0x3B. The number of columns was retrieved before, so you must read 18 * numcols bytes.

From (hex)	To (hex)	Len (dec)	Format	Description
0x3B	0x3B	1	Byte	Column type
0x3C	0x3C	1	Byte	Column Number
0x3D	0x3E	2	LE-Int	Variable col number
0x3F	0x40	2	LE-Int	Row col number
0x41	0x45	5		Unknown
0x46	0x46	1	Byte	Precision
0x47	0x47	1	Byte	Scale
0x48	0x48	1	Byte	Flags. Include isfixed, is long autonumeric, uuid auto
0x49	0x4A	2	LE-Int	Column fixed offset
0x4B	0x4C	2	LE-Int	Column size offset

After the properties, the name of the columns appear. The first byte contains the size and then you read that amount of bytes (the name). So, column names can´t be bigger than 256 chars
Column names are stored in compressed unicode.

For example, for MSysObjects there are 17 columns, so 0x3B adds to 17 * 18 = 16D The first column is Id (len 2)

From (hex)	To (hex)	Len (dec)	Format	Description
0x16D	0x16D	1	Byte	Column name size
0x16E	0x16F	2	Unicode Text	Column Name

After the columns, the indexes definition follow.

Data allocation

Data is stored in data pages (page type 1) with the structure:

From (hex)	To (hex)	Len (dec)	Format	Description
0x0	0x0	1	Byte	Page Type (1 for this page)
0x1	0x1	1	Byte	Unknown
0x2	0x3	2	LE-Int	Free space in this page
0x4	0x7	4	LE-Int	Page pointer to page definition
0x8	0x9	2	LE-Int	Number of records

After that information, there are offsets of 2 bytes to records (there are 2 * num_records bytes of this items). After that, records start.
To know record len you substract next offset to previous offset.

In the table definition there is a field called Pages usage bitmask:
The 3 first bytes of that item are the page and the last byte the row of the page usage bitmap.
For example, in the sample database, the bitmap is located in Page 6, row 0.

Bitmap data is 133 bytes long and contain:

From (hex)	To (hex)	Len (dec)	Format	Description
0x0	0x0	1	Byte	Map type (0 or 1)
0x1	0x4	4	Byte	Base page for which this map applies
0x5	0x85	128	Byte	Each bit contains allocation of this table (boolean). Allocation can be set to 128 pages after base page

So, this basic allocation cannot hold more than 128 pages. As pages are of size 2048, that gives: 128 * 8 * 2048 = 2Mb. For bigger databases the field Pages usage bitmask points to a record containing an map identifier (1) of 1 byte and secuentially pointers of 4 bytes to pages of type 5 that contain bitmaps.

The code

Solution structure

The solution contains 5 projects:

• MdbToolsLib: Contains the GLib (C Libraries) and libmdb (Access). It also contains classes to interface with projects MFCMdbTools and NETMdbTools. It uses STL.
• MFCMdbTools: The idea of this Dll is to act as a layer between MFC and MdbToolsLib. Most of the code is CString conversion
• NETMdbTools: The idea of this Dll is to act as a layer between .NET and MdbToolsLib. It uses C++/CLI.
• MFCMdbToolsTestApp: MFC test app that uses MFCMdbTools.
• NETMdbToolsTestApp: C# test app that uses NETMdbTools. It includes export functionality.

Naming conventions

There are 3 projects which share the same functionality (MdbToolsLib, MFCMdbTools, NETMdbTools) for different libraries (C/C++, MFC, .NET).
So, there are 3 prefixes, one for each library:

• MdbToolsLib: The prefix is MdbLib. For example, MdbLibDatabase
• MFCMdbTools: The prefix is MFCMdb. For example, MFCMdbDatabase
• NETMdbTools: The prefix is NETMdb. For example, NETMdbDatabase

MdbToolsLib

Class MdbLibDatabase

CMdbLibDatabase()	Initializes the MdbTools handler as 0 so that you can check if a database is loaded.
void* mdb;	Private variable that contains the MdbHandle (struct) of the database. It is declared as void so that you don't need the MdbTools.h header.
int Open(const char* strname);	Opens the database and assigns Mdbhandle. Returns a 0 for error, other for success.
void Close();	If there is an open database, it closes all tables handles and closes the Mdbhandle database.
void LoadTables();	Loads the database catalog and creates CMdbLibTable objects for each one in the tables array.
map<char*, CMdbLibTable, cmp_str> tables;	This is the array of tables. cmp_str is a comparison to find items sorted by char*

Class MdbLibTable

CMdbLibTable()	Initializes the MdbTableDef object (tdef) and the name (char*) as 0.
char* Name;	Contains the name of the table. It is allocated and assigned in CMdbLibDatabase::LoadTables() and deleted in ClearTableColumns()
map<char*, CMdbLibColumn, cmp_str> columns;	This is the array with the columns of the table. It is loaded in LoadTableColumns()
map<char*, CMdbLibValue, cmp_str> cvalues;	This is the array with the values for iterating the table rows. It is loaded and allocated in LoadTableColumns() but values are assigned in NextItem()
void LoadTableColumns();	Loads columns and values (columns and cvalues arrays). Allocates memory for both. Binds cvalue data to col so that it is assigned when row changes
void ClearTableColumns();	Frees the MdbTableDef tdef variable, and all cvalues allocated space for row data.
int NextItem();	Executes the mdb_fetch_row and returns if eof. Values are assigned inside mdb_fetch_row by the bind_ptr.
void MoveFirst();	Executes the mdb_rewind_table so that record cursor moves to the first row.
void* mdb;	MdbHandle variable of the database. Declared as void to avoid Mdbtools.h dependency.
void* tdef;	This is the MdbTools handle for the table. It is assigned in LoadTableColumns().
BOOL IsSystem;	If the table is system table (name starts with MSys).
void* GetEntryByName(char* name);	Finds the entry in the catalog by table name. This entry is neccesary to perform the mdb_read_table.

Class MdbLibColumn

enum MdbLibColType	Contain the column type constants. It mimics MdbTools column type constants. They exist to provide independence from MdbTools.h.
char* Name;	Column name. It is allocated and assigned in CMdbLibTable::LoadTableColumns().
MdbLibColType Type;	Column type.
int Size;	Column fiexd size.
int IsInt();	Boolean used to group all integer types for formatting porpuses.
int IsNumDec();	Boolean used to group all float types for formatting porpuses.
int IsBinary();	Boolean used to group all binary (memo, OLE, etc) types for formatting porpuses.
int IsDate();	Returns if the column type is date (for formatting porpuses).

Class MdbLibValue

char* value;	This is the buffer where the column value is stored when iterating rows. It is assigned to bind_ptr in CMdbLibTable::LoadTableColumns().
int len;	This is real len of the data stored in value. It is assigned to len_ptr in CMdbLibTable::LoadTableColumns().
int GetIntValue();	Returns the value stored in "value" variable as int.
double GetDoubleValue();	Returns the value stored in "value" variable as double. To do so it eliminates the exponent and multiplies accordingly.
void GetTimeValue(SYSTEMTIME* st);	Returns the date in SYSTEMTIME format.

Libraries

The original MdbTools uses GLib, so, there are dependencies and libglib is needed to run the application.
I had the idea of replacing GLib with STL and native windows functions, but then, it would be a lot of work and besides, when MdbTools sourcecode improves, I would have a difficult time upgrading.

The project is in VS 2010 and uses the latest MFC and .NET 4 but it can be easily ported the older versions of Visual Studio.
To compile MFC and .NET apps, first compile GLib and copy the .lib in the glib folder of the app.

History

• 2012-01-13: Improve article with some MsAccess format detail.
• 2011-12-20:
  
  
  • Made a common .lib interface (including GLib) for both .NET and MFC and put it all into one single project.
  • Added support for Binary fields (OLE). Improved support for numbers and dates
  • In the .NET app added export to DAO database to repair corrupt databases
• 2011-11-21. GLib library now links into dlls.
• 2011-11-14. First version that uses glib dlls.

Licence and sourcecode

The licence for this project is LGPL which basically means that you can use the library in any project (even commercial apps) but if you make modifications to the library itself you must publish them. The sources were taken from https://github.com/brianb/mdbtools.

To do

• Add support for other database objects (queries, modules, forms, etc).
• Add ATL/COM interface for MdbTools.
• Add write support.

Acknowledgements

To the authors of mdbtools: Brian Bruns, Karl Nyberg, Georg Bauer, Carl Seutter, Trevor Harrison, Brent Johnson, Tim Nelson, David Mansfield, Jeff Smith, Steve Langasek, Rene Engelhard, Vincent Fourmond, Tim Retout, Nirgal Vourgere