Articles   Members Online: 3
-Article/Tip Search
-News Group Search over 21 Million news group articles.
Member Area
-Account Center
-Top 10 NEW!!
-Submit Article/Tip
-Forums Upgraded!!
-My Articles
-Edit Information
-Become a Member
-Why sign up!
-Chat Online!
-Indexes NEW!!
-Build your resume
-Find a job
-Post a job
-Resume Search
-Link to us
Visit Embarcadero
Embarcadero Community
Modifying Table Stucture in Access MDB Files With Delphi Turn on/off line numbers in source code. Switch to Orginial background IDE or DSP color Comment or reply to this aritlce/tip for discussion. Bookmark this article to my favorite article(s). Print this article
Without Access with the Microsoft Jet 4.0 ANSI SQL-92 Extens 15-Aug-03
Delphi 5.x
User Rating
No Votes
# Votes
DSP, Administrator
Reference URL:
			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)


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 
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 

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!!!).

Vote: How useful do you find this Article/Tip?
Bad Excellent
1 2 3 4 5 6 7 8 9 10


Share this page
Download from Google

Copyright © Mendozi Enterprises LLC