468,557 Members | 2,512 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,557 developers. It's quick & easy.

Running DDL scripts

1
Hi,

I have more than 60 tables to be created in access. I have the "create table" scripts generated out of oracle.

Please let me know how to run these scripts and if they require any modifications to be done.

Thanks
BMG
Aug 23 '07 #1

✓ answered by JKing

I've never used Oracle before so I cannot say this with certainty but I believe the answer is yes it is possible. Though the syntax maybe slightly different and the scripts may require a little tweaking. Running DDL create table statements are done through the query editor. Create a new database. Create new query and close the select table popup. Switch the view to SQL and this is where you can add your create table statement.

Here's the syntax:
Expand|Select|Wrap|Line Numbers
  1. CREATE [TEMPORARY] TABLE table (field1 type [(size)] [NOT NULL] [WITH COMPRESSION | WITH COMP] [index1] [, field2 type [(size)] [NOT NULL] [index2] [, ...]] [, CONSTRAINT multifieldindex [, ...]])
  2.  
The CREATE TABLE statement has these parts:

Part Description
table The name of the table to be created.
field1, field2 The name of field or fields to be created in the new table. You must create at least one field.
type The data type of field in the new table.
size The field size in characters (Text and Binary fields only).
index1, index2 A CONSTRAINT clause defining a single-field index.
multifieldindex A CONSTRAINT clause defining a multiple-field index.

3 6750
JKing
1,206 Expert 1GB
I've never used Oracle before so I cannot say this with certainty but I believe the answer is yes it is possible. Though the syntax maybe slightly different and the scripts may require a little tweaking. Running DDL create table statements are done through the query editor. Create a new database. Create new query and close the select table popup. Switch the view to SQL and this is where you can add your create table statement.

Here's the syntax:
Expand|Select|Wrap|Line Numbers
  1. CREATE [TEMPORARY] TABLE table (field1 type [(size)] [NOT NULL] [WITH COMPRESSION | WITH COMP] [index1] [, field2 type [(size)] [NOT NULL] [index2] [, ...]] [, CONSTRAINT multifieldindex [, ...]])
  2.  
The CREATE TABLE statement has these parts:

Part Description
table The name of the table to be created.
field1, field2 The name of field or fields to be created in the new table. You must create at least one field.
type The data type of field in the new table.
size The field size in characters (Text and Binary fields only).
index1, index2 A CONSTRAINT clause defining a single-field index.
multifieldindex A CONSTRAINT clause defining a multiple-field index.
Aug 23 '07 #2
davem22101
1 Bit
Expand|Select|Wrap|Line Numbers
  1. Sub ImportDDL()
  2.  
  3.     Dim File    As Integer
  4.     Dim Data    As String
  5.     Dim Records As Long
  6.     Dim SQLstring    As String
  7.  
  8.     File = FreeFile()
  9.     Open "C:\temp\DDL_MSAccess_2021-05-10.sql" For Input As #File
  10.  
  11.     SQLstring = ""
  12.     While Not EOF(File)
  13.         Line Input #File, Data
  14.         SQLstring = SQLstring & Data
  15.         If InStr(Data, ";") > 0 Then
  16.             Debug.Print (SQLstring)
  17.             DoCmd.RunSQL (SQLstring)
  18.             SQLstring = ""
  19.             Records = Records + 1
  20.         End If
  21.     Wend
  22.     Close #File
  23.  
  24.     Debug.Print (Records)
  25.  
  26. End Sub
May 10 '21 #3
NeoPa
32,102 Expert Mod 16PB
That's clean & tidy code Dave, but I'm not sure it deals with the fundamental issue of the scripts being in an Oracle format rather than Access. There are many points of difference between the two.
May 18 '21 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Rolfe | last post: by
4 posts views Thread by Peter Otten | last post: by
1 post views Thread by Patrick | last post: by
4 posts views Thread by Nick Sinclair | last post: by
1 post views Thread by edovale | last post: by
6 posts views Thread by Ishpeck | last post: by
24 posts views Thread by Mark | last post: by
22 posts views Thread by V S Rawat | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.