Table Creation DDL from Microsoft Access

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.

From stackoverflow Richard A

• 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

  iDevlop : this is not relevant !

  Corey Trager : @iDevlop - It *IS* relevant, but maybe I didn't explain it right. The original question asks whether there is a way to create script or must it be written by hand. If you follow my instructions, you let MS SQL Server generate the script for you. You'll still have to tweak it by hand for Access, but the SQL Server trick will get you 90% there.
  From Corey Trager

• 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.

  From Gareth

• 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.

  Peter : Not working for decimal fields

  Richard A : I would imagine that you could add: Case dbDecimal fldDataInfo = "number" into the case statement. As I said, I'm not VB programmer.
  From Richard A

• 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.

  From skamradt

• 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.

  Richard A : Thanks for the comment, but we need to get the data moved now. We don't have time to play with redesign at the moment.

  Draemon : You wouldn't *have* to redesign, even just writing the SQL by hand would be pretty quick and probably less hassle. It forces you to review things like column types too. I'd seriously consider it.
  From onedaywhen

• 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.

  From Michael OShea