ADOX: Copy table structure with C#?

5 Jul 2006 6:17 PM

Sensei76

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[i].Name;
        lNewCol.Type = lSrcTable.Columns[i].Type;
        lNewCol.DefinedSize = lSrcTable.Columns[i].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[i].Name;
        lNewKey.Type = lSrcTable.Keys[i].Type;

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

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

        lNewIndex.Clustered = lSrcTable.Indexes[i].Clustered;
        lNewIndex.IndexNulls = lSrcTable.Indexes[i].IndexNulls;
        lNewIndex.Name       = lSrcTable.Indexes[i].Name;
        lNewIndex.PrimaryKey = lSrcTable.Indexes[i].PrimaryKey;
        lNewIndex.Unique     = lSrcTable.Indexes[i].Unique;

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

    Catalog.Tables.Append(lNewTable);
}

6 Jul 2006 3:31 PM

Paul Clement

On Wed, 5 Jul 2006 11:17:04 -0700, Sensei76 <Sense***@discussions.microsoft.com> wrote:

¤ 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)

6 Jul 2006 3:52 PM

Sensei76

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