5

I have a MS Access file containing hundred of tables, I should create these tables using C# at runtime. So I should generate a script and use that query inside C# to create the tables. Is there a way that MS Access can generate this SQL script automatically?

Best regards

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
Ehsan Akbar
  • 6,977
  • 19
  • 96
  • 180
  • 2
    An Access database is just a file, so you don't really need to run DDL code to create your tables. You can just distribute your application with a database file that already has the empty tables (and relationships) defined. When it runs for the first time your application can copy that file to an appropriate location on the user's system. – Gord Thompson Jul 12 '14 at 13:32
  • The users shouldn't see the empty access file can i put it in my application as it compiled ? – Ehsan Akbar Jul 12 '14 at 13:37
  • 2
    @EA The users will see it anyway. – ZippyV Jul 12 '14 at 13:37
  • You know i just want to create an access file in memory ,and do all operation on it in memory after that i will send that by email ,and remove that from memory – Ehsan Akbar Jul 12 '14 at 13:39
  • 1
    An Access database must exist in a filesystem *somewhere*. The only way you could "create an access file in memory" would be to create it on a RAM drive which is (1) extremely rare these days, and (2) not something that would be guaranteed to exist on a user's machine anyway. – Gord Thompson Jul 12 '14 at 13:55

2 Answers2

4

No, Access itself cannot automatically create DDL (CREATE TABLE ...) code like SQL Server can. It is entirely possible that some third-party product might be able to scan through an Access database and write DDL statements for each table, but recommendations for such a third-party product would be off-topic on Stack Overflow.

Also, as mentioned in the comments to the question, creating an empty database file and then creating each table "from scratch" via DDL is not really necessary for an Access database. Since an Access database is just a file you can distribute your application with a database file that already contains the empty tables (and other database objects as required).

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
0

You can use an SSIS package to generate the create table command. Start a new SSIS package. Add a connection manager for the Access database. Then add a connection manager for a SQL Server database. When you configure the dataflow task. Select the Access database as the source, then the SQL server as the destination. When choosing the table or view for the destination hit the [New] button and you will get the table creation script from the Access table DDL translated to MS SQL Server.

Mike
  • 1