sinDizzy sinDizzy - 16 days ago 5
Vb.net Question

Copy an Access Table with Indexes and Keys

I have an Access 2007 database and when I open it I can copy and paste a table within the Access application. I can choose to copy structure only OR structure and data. In my case I just need the structure. The copy retains the indexes and keys of the original. I need to do this with VB.NET. So far I've looked at many articles and am able to copy the tables structure by issuing a SQL statment

Dim dbCmdInsert As New OleDbCommand
With dbCmdInsert
.CommandText = "SELECT * INTO tmpTable FROM Trips WHERE 0=1;"
.CommandType = CommandType.Text
.Connection = connNavDb
Dim n As Integer = .ExecuteNonQuery
End With


This copied the structure with no records which is what I want but the indexes and keys are lost. I cant seem to find good guidance on accomplishing this.

Answer

Very roughly indeed:

    Dim oAccess As New Access.Application

    ''Open a database
    oAccess.OpenCurrentDatabase("z:\docs\test.accdb")
    ''"Import" table 
    oAccess.DoCmd.TransferDatabase(
        Access.AcDataTransferType.acExport, "Microsoft Access", "z:\docs\test.accdb", _
        Access.AcObjectType.acTable, "table1", "newtable1", True, False)

See also c#: Copy Access table structure and constraints using C# or SQL