ADOX: Copy table structure with C#?

[Guest]

Hello,

I am working with an Access database file and want to create a new file with
the same table structure. Creating the new file was no problem, also creating
new tables with adjacent keys worked well.
But copying the indexes is a pain.

My testing table has 4 indexes and the code runs 4 times through the loop,
just as expected.
There is no error when try to append the Index to the new table.
But the follwing code throws an exception "Index already exists" in the last
line, in which I append the table to my catalog class.

Am I doing something wrong? Do you have any ideas?

Is there a more elegant way to copy a DB structure.

Thank you in advance,
Jens Hellmann

--- Code ---

private void copytable( string pTableName )
{
ADOX.Column lNewCol;
ADOX.Table lNewTable;
ADOX.Table lSrcTable;
ADOX.Key lNewKey;
ADOX.Index lNewIndex;

lNewTable = new ADOX.Table();
lNewTable.Name = pTableName;

// Gets the ADOX.Table object where Name equals pTableName
// This is the original table from the existing database.
lSrcTable = getTablepTableName);

// Copy Columns
for (int i = 0; i < lSrcTable.Columns.Count; i++)
{
lNewCol = new ADOX.Column();
lNewCol.Name = lSrcTable.Columns.Name;
lNewCol.Type = lSrcTable.Columns.Type;
lNewCol.DefinedSize = lSrcTable.Columns.DefinedSize;

lNewTable.Columns.Append(lNewCol, lNewCol.Type, lNewCol.DefinedSize);
}

// Copy Keys
for (int i = 0; i < lSrcTable.Keys.Count; i++)
{
lNewKey = new ADOX.Key();

lNewKey.Name = lSrcTable.Keys.Name;
lNewKey.Type = lSrcTable.Keys.Type;

lNewTable.Keys.Append(lNewKey.Name,lNewKey.Type,
lSrcTable.Keys.Columns[0].Name,"","");
}

// Copy Indexes
for ( int i = 0; i < lSrcTable.Indexes.Count; i++ )
{
lNewIndex = new ADOX.Index();

lNewIndex.Clustered = lSrcTable.Indexes.Clustered;
lNewIndex.IndexNulls = lSrcTable.Indexes.IndexNulls;
lNewIndex.Name = lSrcTable.Indexes.Name;
lNewIndex.PrimaryKey = lSrcTable.Indexes.PrimaryKey;
lNewIndex.Unique = lSrcTable.Indexes.Unique;

lNewTable.Indexes.Append( lNewIndex.Name,
lSrcTable.Indexes.Columns[0].Name );
Debug.WriteLine( "SCHLEIFE i : " + i.ToString() );
}

Catalog.Tables.Append(lNewTable);
}

 

[Paul Clement]

¤ Hello,
¤
¤ I am working with an Access database file and want to create a new file with
¤ the same table structure. Creating the new file was no problem, also creating
¤ new tables with adjacent keys worked well.
¤ But copying the indexes is a pain.
¤
¤ My testing table has 4 indexes and the code runs 4 times through the loop,
¤ just as expected.
¤ There is no error when try to append the Index to the new table.
¤ But the follwing code throws an exception "Index already exists" in the last
¤ line, in which I append the table to my catalog class.
¤
¤ Am I doing something wrong? Do you have any ideas?
¤
¤ Is there a more elegant way to copy a DB structure.
¤

When recreating the structure I would use Jet SQL DDL instead:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acintsql.asp


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Guest]

Thank you Paul, I'll have a look at that.