20

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.

tzot
  • 92,761
  • 29
  • 141
  • 204
Richard A
  • 2,783
  • 2
  • 22
  • 34

6 Answers6

23

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.

Richard A
  • 2,783
  • 2
  • 22
  • 34
  • 1
    I would imagine that you could add: Case dbDecimal fldDataInfo = "number" into the case statement. As I said, I'm not VB programmer. – Richard A Nov 11 '09 at 02:22
  • I had to use Dim fldDef As Object (not Field) to get it working in Access2007 – DJDave Dec 22 '13 at 16:00
  • You may also need to add a module reference lest you receive an "user defined type not defined" error. This is done in the VB editor via the menu path Tools > References . In my case the needed module was a "Microsoft DAO" object library. This was in MS Access 2016. – Justin Sep 07 '18 at 23:52
5

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

Corey Trager
  • 22,649
  • 18
  • 83
  • 121
  • 1
    @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. – Corey Trager Sep 10 '09 at 02:08
2

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.

Michael OShea
  • 397
  • 2
  • 7
  • The above link is broken. This might be helpful: http://www.oracle.com/technetwork/database/migration/access-084991.html – Somu Jul 15 '14 at 14:59
  • 1
    That won't work anymore, SQLDeveloper uses java 8, which does not support ODBC bridge. – notzippy May 19 '15 at 16:57
  • [Oracle Application Express Application Migration Guide](https://docs.oracle.com/cd/E11882_01/appdev.112/e12509/appmgr.htm#AEMIG104) – user10186832 Mar 26 '21 at 20:58
1

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.

Gareth
  • 311
  • 3
  • 6
  • Yes! It works. It requires a bit of patience, until you get the message "Successfully exported 'tablename'". I exported a table to Sqlite3 and to Firebird with success. Well, to none of the fields in the new table even a "NOT NULL" constraint was added. In both destination databases I therefore copied the DDL, dropped the table, edited the DDL and then recreated the table again with extra constraints. – Dobedani Nov 13 '18 at 10:32
1

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.

skamradt
  • 15,366
  • 2
  • 36
  • 53
0

A bit late to the party, but I use RazorSQL to generate DDL for Access databases.

Stareagle
  • 1