4

I need to run DDL script to create tables to import data into Microsoft Access. I have the DDL script already made and it has worked in Oracle SQL Developer already, but I'm looking to do it in Access as well.

I can't seem to find an easy way to do it. It looks like previous versions of Access had this feature.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
lmb57
  • 41
  • 3
  • 1
    What error are you getting? DDL is a part of Access. – random_answer_guy Jun 28 '16 at 15:06
  • 2
    Be aware that DDL written for Oracle SQL will almost certainly require some tweaking before it will work with Access SQL. – Gord Thompson Jun 28 '16 at 15:14
  • 1
    Also, Access will only execute one DDL statement at a time. So if your script includes multiple statements, you'll have to break out each one, translate it to Access SQL, and execute it. You can't give Access multiple statements to execute as a single batch. – HansUp Jun 28 '16 at 15:58
  • 1
    If the Oracle tables exist, it should be easier to create a DSN to the Oracle database, and import those tables (with or without the data they contain) into Access. – HansUp Jun 28 '16 at 16:17

1 Answers1

8

Probably the easiest way to run a DDL statement in Access 2013 is to choose

CREATE > Query Design

from the ribbon, hit Esc to dismiss the "Show Tables" dialog, click the "Data Definition" button on the ribbon

DDL.png

type or copy the DDL statement into the query pane, then click the "Run" button on the ribbon (big red exclamation point).

Community
  • 1
  • 1
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • 1
    With this concept, it is possible to create an Access query object that will modify the database schema. I've got a Make Table query that after use can now be followed by a DDL query to `DROP TABLE tmp_Data`. – Ben Jun 20 '19 at 20:40