Copy Access table structure and constraints using C# or SQL

Question

Can someone tell me how to copy an MS Access table's table structure and all its constraints, keys, and etc. using .NET or SQL? I have searched and found a number of ways of copying the columns and types, but I need to bring across the constraints as well such as primary keys.

I have a database with a bunch of tables which I need to create copies of (structure only) with new names. This needs to be executed from within a C# application, but I do not care if it is done using ADO or SQL, just as long as I can find a way. Any tips would be greatly appreciated.

possible duplicate of [How to copy a table schema and constraints to a table of different database?](http://stackoverflow.com/questions/1600172/how-to-copy-a-table-schema-and-constraints-to-a-table-of-different-database) — Keith Nicholas, Apr 18 '12 at 01:52
Copying an access table to another access table? Or to another database system? — phoog, Apr 18 '12 at 03:20
Keith: Database is a Microsoft Access 2007. Also, the link you provided addresses the same issue for SQL Server, but it does not work for MS Access as there is no way to generate scrips for a table within Access (please correct me if I'm wrong). — sammy, Apr 18 '12 at 13:39
Phoog: I want to create a copy of an existing table within the same database. If the copy to be created already exists, I will delete it first, then generate a fresh copy. — sammy, Apr 18 '12 at 13:41

score 1 · Answer 1 · answered Apr 18 '12 at 01:50

1

I would use the upsize wizard with MS Access

http://support.microsoft.com/kb/237980

answered Apr 18 '12 at 01:50

Micah Armantrout

6,781
4
40
66

Micah: thanks for the link, but it details how to convert an older (97 & 2000) access database to SQL Server using external tools. The issue I am having is how to create a structural copy a table within a MS Access 2007 db from a C# application. – sammy Apr 18 '12 at 13:45
ah take a look at this http://stackoverflow.com/questions/905090/in-vb-net-c-how-do-i-copy-a-sql-table-schema-from-on-db-to-another – Micah Armantrout Apr 18 '12 at 14:23
does this work with MS Access? I'm doing a little digging and trying now but if you know already... – sammy Apr 18 '12 at 15:04

Fionnuala · Accepted Answer · 2012-04-19T09:14:08.260

1

You can "export" to the same database, which will create a copy with all indexes etc, but not relationships.

        // Start a new instance of Access for Automation
        oAccess = new Access.Application();

        // Open a database
        oAccess.OpenCurrentDatabase(@"z:\docs\test.accdb");
        oAccess.DoCmd.TransferDatabase(
            Access.AcDataTransferType.acExport,
            "Microsoft Access",
            @"z:\docs\test.accdb",
            Access.AcObjectType.acTable, 
            "table1",
            "newtable",true,false);

edited Apr 19 '12 at 09:14

answered Apr 18 '12 at 16:09

Fionnuala

90,370
7
114
152

I'll give it a try and get back to you. – sammy Apr 19 '12 at 01:30
You're awesome! Thanks a ton. For full disclosure, I had to add a .Net reference to my project for "Microsoft.Office.Interop.Access", version 12 as I'm using Office 2007. – sammy Apr 19 '12 at 02:44
I need to do a little more testing tomorrow to make sure it takes care of everything and then I'll mark your post as the answer. – sammy Apr 19 '12 at 02:45

Copy Access table structure and constraints using C# or SQL

2 Answers2

Linked

Related