Running DDL scripts

[bmg]

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

[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:

CREATE [TEMPORARY] TABLE table (field1 type [(size)] [NOT NULL] [WITH COMPRESSION | WITH COMP] [index1] [, field2 type [(size)] [NOT NULL] [index2] [, ...]] [, CONSTRAINT multifieldindex [, ...]])

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.

[davem22101]

Sub ImportDDL()

    Dim File    As Integer
    Dim Data    As String
    Dim Records As Long
    Dim SQLstring    As String

    File = FreeFile()
    Open "C:\temp\DDL_MSAccess_2021-05-10.sql" For Input As #File
    
    SQLstring = ""
    While Not EOF(File)
        Line Input #File, Data
        SQLstring = SQLstring & Data
        If InStr(Data, ";") > 0 Then
            Debug.Print (SQLstring)
            DoCmd.RunSQL (SQLstring)
            SQLstring = ""
            Records = Records + 1
        End If
    Wend
    Close #File

    Debug.Print (Records)
    
End Sub

[NeoPa]

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.