Author: Massimo Brini
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!!!).
|
