How can I modify the schema for an Access DB in C#

Question

Hey all.

I have a project here the goal is to merge multiple Access DB's into one SQL Server db. Some of the records in the Access DB are autonumber, and will not be unique across all the DB's.

We are currently working on modifying those autonumber fields programmaticaly to being number fields. The adjusting and allowing access to cascade those changes through before appending all those tables through to SQL server.

Im aware of the getSchema() for access, but can we modify the schema to remove an autonumber from a field?

Failing that, does anyone have any brilliant ideas on how to accomplish our task? Or done anything similar?

Regards

Answer 1

You can use DDL:

ALTER TABLE Table1 ALTER COLUMN id long

EDIT re COMMENT

You need to Drop a relationship before you change the field type, but a primary index is fine:

ALTER TABLE Table2 DROP CONSTRAINT Table1Table2 

The constraint will probably be called something on these lines, but you can use ADOSchemas to get the names of the constraints (relationships).

Answer 2

Since you're kissing MS-Access goodbye, why not just edit the tables manually (right click on the relevant tables and design)? It might be faster than writing code to do the same.

If you really need the programmatic way to do things, ADOX was a rather mature API for manipulating schema objects and it was most feature complete for MS-Access. It's COM but you can use COM interop.

Buried in this page is some examples of using ADOX for adding columns http://www.codeguru.com/cpp/data/mfc_database/ado/article.php/c4343#_Toc1461080