ADOX used to change a Column's Property? (Resolved)

[Tec-Nico]

Originally I just wanted to add more space to a comment field on an Access 97 using code

Why? I am helping a guy who doesn't have VB installed in his computer and he is working on a DataBase with the help of a DB manager I programmed for him.

But recently he found out one of the fields (Comment) was too short for him... So I tried using another VB project I developed. This little thing executes SQL statements in Access 97 databases.

The problem is that in Access 97 databases the statement "ALTER TABLE tablename ALTER COLUMN columnname" doesn't work.

So I searched the net for a way to get this done and I found out this one: Link

The problem is that I cannot set the attributes. My little project runs well but whenever I try to set the attributes of the Columns it will crash with this message:

Run-Time Error '3265'

Item cannot be found in the collection corresponding to the requested name or ordinal.

It will highlight "cat.Tables.Append tbl" but when I comment the line ".Columns(Column.Name).Attributes = Column.Attributes" it will throw an error...

The error tells me the following:

Run-time error '-2147467259(80004005)':

The field 'MyTable.Comments' cannot contain a Null value because the Required property for this field is set to True. Enter a value in this field.

Obviously, some Comments are in blank since you can simply save the data without a comment. I am going crazy here!!

Please help me... I attach the project I am working on.

P.S. Thanks to Manavo for the CCommonDialogs and FolderCallBack

[Tec-Nico]

Ok, I found something even more strange...

Why do I have to do this for it to work?

.Properties("Nullable").Value = Not Column.Properties("Nullable").Value

Also.. Why won't it save the default value?

(To make it work you have to add that "Not" I showed before and comment the line that says "comment me")

[Tec-Nico]

I have read that changing columns on the fly is very difficult and I have also seen in the forums that setting the Default property in an ADOX Column in Access 97 (I don't know in the other versions) will not work.

So... I came up with this solution which is a bit like the Visual Data Manager. It has some problems yet (Like letting you write down the Database location and using it right away) but it allows some changes to the DataBase (I haven't tested adding new things) which is the real objective.

I will add it to the CodeBank when I solve the other problems.

[Vishalgiri]

Wow Thats Great... i mean it is great to solve problem by own