Copy an Access Table with Indexes and Keys

Question

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.

score 1 · Accepted Answer · edited May 23 '17 at 11:59

1

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

edited May 23 '17 at 11:59

Community

1
1

answered Jul 01 '12 at 08:45

Fionnuala

90,370
7
114
152

I have not thought of this way. But does it requires Access installed or just the PIA assemblies? – Steve Jul 01 '12 at 08:53
I am not sure, but the PIA seems to include the Access Object Library (http://msdn.microsoft.com/en-us/library/15s06t57(v=vs.80).aspx), which was the only library I needed for the test. – Fionnuala Jul 01 '12 at 09:11
My hopes have fallen to zero. Look here http://stackoverflow.com/questions/370407/is-office-2007-pia-deployment-possible-without-office-2007 – Steve Jul 01 '12 at 09:31
It is not too difficult to get information about an Access table from the connection object using schemas. You can create the table easily enough by just copying, then all you need are the indexes, yesno? Are notes in c# any use to you? – Fionnuala Jul 01 '12 at 09:46
Yes, thanks @Remou, I know how to do with schemas, I liked your idea because that required very little code compared to schema handling. Thanks again – Steve Jul 01 '12 at 10:17
Right before I went to sleep I found the article on this method. I will test today. Only drawback here is that in my routine I am already connected to the DB via an oledb connection to do other stuff. I'll have to disconnect, copy the tables, reconnect and finish the process. – sinDizzy Jul 01 '12 at 18:57

score 0 · Answer 2 · answered Jul 01 '12 at 08:30

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.

score 0 · Answer 3 · edited Sep 21 '17 at 02:31

0

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

edited Sep 21 '17 at 02:31

Nathan Tuggy

2,237
27
30
38

answered Sep 21 '17 at 01:43

amir

1

2

Welcome to Stack Overflow - nice to have you. Please read [How to answer](https://stackoverflow.com/help/how-to-answer). – Axel Sep 21 '17 at 01:58

Copy an Access Table with Indexes and Keys

3 Answers3