Board index » delphi » How to alter db structure in Access

How to alter db structure in Access

Hi,

I write a program with Delphi 5 and database as Access 2k with ADO.  Now, I
want to alter the db structure (i.e. add some new fields) of one of the
tables in the mdb.  I try to use the following SQL statements:

ALTER TABLE table1 ADD COLUMN field1 TEXT(12), field2 TEXT(12), field3
TEXT(35)

After run this SQL statement with a function, the fields is created but the
properties (AllowZeroLength and UnicodeCompression) do NOT changed to my
wanted (Both values is True).

How do I change these properties with Delphi program?

Thanks.

 

Re:How to alter db structure in Access


Quote
"Zero" <shin...@mail.hongkong.com> wrote in message

news:3e3fed32$1@newsgroups.borland.com...

Quote
> Hi,

> I write a program with Delphi 5 and database as Access 2k with ADO.  Now,
I
> want to alter the db structure (i.e. add some new fields) of one of the
> tables in the mdb.  I try to use the following SQL statements:

> ALTER TABLE table1 ADD COLUMN field1 TEXT(12), field2 TEXT(12), field3
> TEXT(35)

> After run this SQL statement with a function, the fields is created but
the
> properties (AllowZeroLength and UnicodeCompression) do NOT changed to my
> wanted (Both values is True).

> How do I change these properties with Delphi program?

You can do it with ADOX (see
http://codecentral.borland.com/codecentral/ccweb.exe/listing?id=15292).
Note that most properties cannot be changed once the column has been
appended to the table.  For example, you cannot change a column's 'Jet
OLEDB:Compressed UNICODE Strings' property after it has been appended.  You
can only set it when you create the column.  However, you can change the
'Jet OLEDB:Allow Zero Length' property.

--

 A: Top-posters.
 Q: What is the most annoying thing on Usenet?

Re:How to alter db structure in Access


Hi,

This can be a pain in the ass, but it's rather easy to write something using
DAO 3.6 to 'sync' more databases.
As you can see, it can be done in Access so all you have to figure out how
to do that.

For example: you can change some properties, other properties you can't
change. But that doesn't have to be a problem: figure out which properties
they are, rename the old field, add the new field (with the desired
properties), add it to the tabledef, and use some query like "update
[yourtable] set newfield = oldfield", and delete the old field.

In fact: I have a working program that works with a graphic interface as
well in text mode (handy when using a telnet connection...) that does the
trick. Perhaps you would like to write your own, if it's too difficult, let
me know.

Mine works like the following: specify a directory containing new database
structures, specify a directory with 'old' database structures, and click et
voila... Databases, tables etc are created. It DOESN'T however update
referential constraints, and tables only (no queries) or VBA code. But if
you're interesed, let me know..

Greetings,

drs. mrX

Quote
"DRS" <d...@removethis.ihug.com.au> wrote in message

news:3e3ffd24@newsgroups.borland.com...
Quote
> "Zero" <shin...@mail.hongkong.com> wrote in message
> news:3e3fed32$1@newsgroups.borland.com...
> > Hi,

> > I write a program with Delphi 5 and database as Access 2k with ADO.
Now,
> I
> > want to alter the db structure (i.e. add some new fields) of one of the
> > tables in the mdb.  I try to use the following SQL statements:

> > ALTER TABLE table1 ADD COLUMN field1 TEXT(12), field2 TEXT(12), field3
> > TEXT(35)

> > After run this SQL statement with a function, the fields is created but
> the
> > properties (AllowZeroLength and UnicodeCompression) do NOT changed to my
> > wanted (Both values is True).

> > How do I change these properties with Delphi program?

> You can do it with ADOX (see
> http://codecentral.borland.com/codecentral/ccweb.exe/listing?id=15292).
> Note that most properties cannot be changed once the column has been
> appended to the table.  For example, you cannot change a column's 'Jet
> OLEDB:Compressed UNICODE Strings' property after it has been appended.
You
> can only set it when you create the column.  However, you can change the
> 'Jet OLEDB:Allow Zero Length' property.

> --

>  A: Top-posters.
>  Q: What is the most annoying thing on Usenet?

Other Threads