How to modify column attributes with ADODX?

[hrdavidson]

Here is a brief summary of what I am trying to achieve:
My database is huge (many tables over 50k records) and I would like to spin off a select set of data. I want to spin off 2008 data, and in 3 months spin off 2009 data -- adding it to the 2008 spin off.

The primary keys in my tables are incremental autonumber. Now, the spin off of 2008 data is relatively easy - because the primary keys retain their relationships. When I go to add 2009 data to that set of tables, the ms access will want to provide new autonumber values - which will disrupt all the cascading relationships that depend on that unique key.

So, before I spin off the 2008 data, i want to change the key from autonumber to simply long integer - but still have it be the unique key and all the relationships to stay intact. Then I can move the data from one database to another on an annual basis.

So, I am happy to provide the snippet of code that so far has been unsuccessful for various reasons - but I first want to know if what I am trying to achieve is realistic and doable.

I greatly appreciate any thoughts on my dilemma!

thanks,

hrdavidson

[vb5prgrmr]

To answer your topic question... Use the alter table statement...

Then there are questions...

What do you meant by spinoff?
Why would it disrupt the relationship?

[hrdavidson]

I will investigate the "alter table" command - thanks.

by spinoff - I mean that the database is too large for the environment to handle so I want to cut the 2008 data from the "main database" and put it in a backup version. The backup version will still be accessible by a different front-end but retain all the same capabilities. Then, I will likely need to spinoff 3-6 months of data at a time - and, preferably, append to the "spin off" version. The key diff is that the main db is used over a network by 5-7 folks; the spinoff version would only be opened by one person at a time on an as-needed basis - where performance will not be an issue.

As for the relationships, I thought I had read where you can't change a field's attributes (especially from autonumber) without undoing the relationships and then reestablishing them after the change....maybe the alter table gets around this.

Thanks for your response!

Hillary