Table Creation Ddl from Microsoft Access

Table Creation DDL from Microsoft Access

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.

Can Access generate CREATE TABLE script code like SQL Server can?

No, Access itself cannot automatically create DDL (CREATE TABLE ...) code like SQL Server can. It is entirely possible that some third-party product might be able to scan through an Access database and write DDL statements for each table, but recommendations for such a third-party product would be off-topic on Stack Overflow.

Also, as mentioned in the comments to the question, creating an empty database file and then creating each table "from scratch" via DDL is not really necessary for an Access database. Since an Access database is just a file you can distribute your application with a database file that already contains the empty tables (and other database objects as required).

MS Access CREATE TABLE WITH COMPRESSION syntax?

My problem turned out to be a syntax error that wasn't reflected properly in my original question.

However, solving that problem revealed that the documentation for MS Access CREATE TABLE on MSDN https://msdn.microsoft.com/en-us/library/office/ff837200.aspx is incorrect regarding the sequence of attributes for the CREATE TABLE statement. According to the documentation, the syntax is:

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

but in fact, [WITH COMPRESSION | WITH COMP] must appear before [NOT NULL] or you get a syntax error.

Additionally, it's not possible to execute the CREATE TABLE statement using the WITH COMPRESSION attribute from a query directly within MS Access. You have to either use VBA or (as in my case) an external program via OleDbConnection.

access: getting create table string

More or less as you have it:

CREATE TABLE example (
         id INT,
         data text(100)
       );

You may wish to check out DAO data types: http://allenbrowne.com/ser-49.html

Automatically Generate SQL from existing MS Access table

For free for 30 days (then $30) you can give DBWScript a go, looks like its what you are asking for, although not in native Access GUI or programmatically

Microsoft Access DDL for new bigint fields

This is not possible as of January 2019. Using the Query Parameters dialog in a design screen, it's possible to get an exhaustive list of Access SQL types and and (by using sql view) their DDL equivalents:

There is no Access SQL data type available which corresponds to the field type Large Number.

---

Related Topics