Table Creation DDL from Microsoft Access

Question

Is there any easy way to retrieve table creation DDL from Microsoft Access (2007) or do I have to code it myself using VBA to read the table structure?

I have about 30 tables that we are porting to Oracle and it would make life easier if we could create the tables from the Access definitions.

Answer 1

I've done this:

There's a tool for "upsizing" from Access to SQL Server. Do that, then use the excellent SQL Server tools to generate the script.

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

Answer 2

You can use the export feature in Access to export tables to an ODBC data source. Set up an ODBC data source to the Oracle database and then right click the table in the Access "Tables" tab and choose export. ODBC is one of the "file formats" - it will then bring up the usual ODBC dialog.

Answer 3

Thanks for the other suggestions. While I was waiting I wrote some VBA code to do it. It's not perfect, but did the job for me.

Option Compare Database
Public Function TableCreateDDL(TableDef As TableDef) As String

         Dim fldDef As Field
         Dim FieldIndex As Integer
         Dim fldName As String, fldDataInfo As String
         Dim DDL As String
         Dim TableName As String

         TableName = TableDef.Name
         TableName = Replace(TableName, " ", "_")
         DDL = "create table " & TableName & "(" & vbCrLf
         With TableDef
            For FieldIndex = 0 To .Fields.Count - 1
               Set fldDef = .Fields(FieldIndex)
               With fldDef
                  fldName = .Name
                  fldName = Replace(fldName, " ", "_")
                  Select Case .Type
                     Case dbBoolean
                        fldDataInfo = "nvarchar2"
                     Case dbByte
                        fldDataInfo = "number"
                     Case dbInteger
                        fldDataInfo = "number"
                     Case dbLong
                        fldDataInfo = "number"
                     Case dbCurrency
                        fldDataInfo = "number"
                     Case dbSingle
                        fldDataInfo = "number"
                     Case dbDouble
                        fldDataInfo = "number"
                     Case dbDate
                        fldDataInfo = "date"
                     Case dbText
                        fldDataInfo = "nvarchar2(" & Format$(.Size) & ")"
                     Case dbLongBinary
                        fldDataInfo = "****"
                     Case dbMemo
                        fldDataInfo = "****"
                     Case dbGUID
                        fldDataInfo = "nvarchar2(16)"
                  End Select
               End With
               If FieldIndex > 0 Then
               DDL = DDL & ", " & vbCrLf
               End If
               DDL = DDL & "  " & fldName & " " & fldDataInfo
               Next FieldIndex
         End With
         DDL = DDL & ");"
         TableCreateDDL = DDL
End Function


Sub ExportAllTableCreateDDL()

    Dim lTbl As Long
    Dim dBase As Database
    Dim Handle As Integer

    Set dBase = CurrentDb

    Handle = FreeFile

    Open "c:\export\TableCreateDDL.txt" For Output Access Write As #Handle

    For lTbl = 0 To dBase.TableDefs.Count - 1
         'If the table name is a temporary or system table then ignore it
        If Left(dBase.TableDefs(lTbl).Name, 1) = "~" Or _
        Left(dBase.TableDefs(lTbl).Name, 4) = "MSYS" Then
             '~ indicates a temporary table
             'MSYS indicates a system level table
        Else
          Print #Handle, TableCreateDDL(dBase.TableDefs(lTbl))
        End If
    Next lTbl
    Close Handle
    Set dBase = Nothing
End Sub

I never claimed to be VB programmer.

Answer 4

You might want to look into ADOX to get at the schema information. Using ADOX you can get things such as the keys, views, relations, etc.

Unfortunately I am not a VB programmer, but there are plenty of examples on the web using ADOX to get at the table schema.

Answer 5

30 tables? I'd do it by hand: the opportunity to reengineer the design to take advantage of a far superior SQL product would be too good to miss.

Answer 6

Use Oracle's SQL Developer Migration Workbench.

There's a full tutorial on converting Access databases to Oracle available here. If its only the structures you're after, then you can concentrate on section 3.0.