ADOX - design changes in both Access & SQL Server
Posted on 2001-06-28
As my application goes thru revisions I need to make changes in the database structure. Originally the application used Access97 and these changes were accomplished with DAO methods.
Now the application has been changed to ADO to support Access and SQL Server. I have been attempting to do an upgrade with ADOX. I need to change a text field size from 50 to 200. I couldn't come up with a way to do this to the original column so I created a new column and copied the data. Since there is code that references the columns by index I needed to recreate all columns after this text field. I was almost successful however I hit a snag with the dynamic property "Default". I could not get this property to accept the value from the original column. Other properties (built-in & dynamic) worked fine. I also had problems with the foreign keys but have not researched that as much. It seems that this is a long way around for a simple change.
First is creating a new field the only way to change the size?
If not is there a way to reorder the columns so I don't have to recreate all columns after the changed colums?
I have already reviewed the ADOX examples in MSDN including the DefinedSize Property Example without sheding any light on this problem.