CODEWITHSUNDEEP
vb.netms-accessindexingcopy
sinDizzy
sinDizzy

Reputation: 1364

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.

Upvotes: 1

Views: 4005

Answers (3)

amir
amir

Reputation: 1

DoCmd.TransferDatabase acExport, "Microsoft Access", CurrentDb().Name, acTable, "Table1", "TableToCreate", True

Upvotes: 0

Fionnuala
Fionnuala

Reputation: 91376

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

Upvotes: 1

iDevlop
iDevlop

Reputation: 25272

In Access VBA you can do it using DoCmd.TransferDatabase acExport, using the source database as target, providing (of course) a different destination table name.
I don't know if there is an equivalent to that DoCmd from vb.net, but that could be the easiest path.

Upvotes: 0

Related Questions