Modifying Table Stucture in Access MDB Files With Delphi Without Access with the Microsoft Jet 4.0 ANSI SQL-92 Extensions

Problem/Question/Abstract:

Sometimes it is necessary to modify the features of an Access MDB Field; while this is a common need, it is not very well known that this can be accomplished with minimal effort, and that it is also possible to do it while in runtime, to change e.g. the length of a text field (both reducing and enlarging) without data loss and without having to create a temporary coulumn to store the data, unlike Interbase or BDE based databases.

How do I modify programmatically the fields inside an Access MDB File from Delphi without loosing my datas? (e.g. the length of a string field)

Answer:

Do you ever needed to change thelength of a text field inside a database?
In the near past this caused a lot of work, but Microsoft has given us a chance to modify the whole structure of an MDB database at runtime without using MS ACCESS.

The technology underlying the exposed techniques is the “MS Jet Engine 4.0 Extensions”, freely available on the Microsoft website and installed with the ADO Executable (MDAC_TYP.EXE + JET ENGINE).

Unlike BDE-Based Databases and Interbase, you can accomplish the task of resizing a field  in seconds.

For example, if you want to enlarge from 30 to 50 chars the “Description” Field of the “Catalog” table in an Access 2000 Database, all you need is to instantiate an ADOQuery, point it to the file with the connection property, executing the query after having filled the SQL property (which is a TStringList of course) with this lines:
Alter Table Catalog Alter Column Description Text(50);

You could think that it is not possible to reduce the size… That’s not true; you only have to use another query before the first.

The following syntax could be normally admitted by ADO only if no DataLoss is involved, so e.g. if there is no data truncated in the application of a smaller length.

To avoid this problem, simply launch the query this way:

Update Catalog set Description = left(Description, 10)

Alter Table Catalog Alter Column Description Text(10);

This way all the exceeding characters are truncated under your control previously ancd the resizing operation is admitted (of course take care not to destroy precious datas or violating primary keys!!!).