Create MSAccess Table based on excel column definition

The data in the worksheet(s) is insufficient to create a table with the attributes you want.  There are different types of numbers, Long, Integer, Single, Double, Boolean, Currency.

First: Your MSAccessTable DDL sheet contains duplicated column names (fact_text2, for example). You would have to sort that out prior to creating a table ...

I'd import that worksheet into a table, and then query that table to determine how to create your new Access table. Let's say you import that to a table named "tmpDDL"

Dim rst As DAO.Recordset
Set rst = Currentdb.OpenRecordset("SELECT * FROM tmpDDL")

Dim tdf As New DAO.TableDef
Dim tdfs As DAO.TableDefs
Dim dbs As DAO.Database

Set dbs = CurrentDB
Set tdfs = Currentdb.Tabledefs

tdf.Name = "YourTableName"

Do until rst.EOF  
  Select Case rst("MSAccessDataType")
    Case "Text"
      tdf.Fields.Append tdf.CreateField(rst("ColumnName"), dbText, "50")
    Case "Number"
      tdf.Fields.Append tdf.CreateField(rst("ColumnName"), dbDouble)
    Case "Date"
      tdf.Fields.Append tdf.CreateField(rst("ColumnName"), dbDate)
    Case Else
      tdf.Fields.Append tdf.CreateField(rst("ColumnName"), dbText, "50")
  End Select
  rst.MoveNext
Loop

tdf.Fields.Refresh
tdfs.Append tdf

tdfs.Refresh

Set tdfs = Nothing
Set tdf = Nothing
Set dbs = Nothing

Select allOpen in new window

FYI: Mark is right, you can't create different Numeric Types from your data, but you could default them all to a specific type (like dbDouble, as I used in my example) if you'd like. If you need to create specific numeric types, however, you'd have to have more information.

In Access, the default type is Long

Dear Scott,
Many thanks this as a very good solution.
Just one enhancement, what would the code look like to add a Description for each field?
I would add a column in my excel 'FieldDescription' and provide a description for each fields.
Many thanks
Best Regards
Martin

Description is an attribute/property of the field. You'd have to add the field, then create the Description property (it doesn't exist by default), and then fill that property with your value. Here's the loop structure from the code above. I've modified the "Text" case statement, and left the rest for you to work with:

Do Until rst.EOF
        Dim fld As New DAO.Field
        Dim prp As DAO.Property
        Select Case rst("MSAccessDataType")
            Case "Text"
                fld.Name = rst("ColumnName")
                fld.Type = dbText
                fld.Size = "50"
                Set prp = fld.CreateProperty("Description", dbText, rst("FieldDescription"))
                fld.Properties.Append prp
                tdf.Fields.Append fld
            Case "Number"

            Case "Date"
                tdf.Fields.Append tdf.CreateField(rst("ColumnName"), dbDate)
            Case Else
                tdf.Fields.Append tdf.CreateField(rst("ColumnName"), dbText, "50")
        End Select
        rst.MoveNext
    Loop

If you want to specify a table with an external list, then you should have three columns/tuples:
(name, data type, length)
with the length data being optional, only used for text field types.

The data type part of the tuple should be able to specify the following.  You do not have to use these exact words.
Byte
Integer - 16 bits
Long - 32 bits
Single
Double
Currency
Decimal - for storing those 96 bit variant variables.  Huge numbers.
Date/Time - While actually Double data, Access handles the field special (mostly formatting)
Boolean -  what is actually used when you see Yes/No in the table wizard dialog
Text - you must specify the length for this field type.
Memo
Hyperlink - for URL data. While actually text data, Access handles the link jumping for you.

Note: there is an AutoNumber attribute that allows you to define your primary key field.  This can either be a long integer or ReplicationID - for GUID values.  The ReplicationID sub-type is required for multiple database replication.

Note: This is not a complete list of field data types, but should cover the majority of cases.

Dear Scott,
Please see my implementatiom, creation of the table column works, but setting the Description Property for a table column field fails:
I get an exception at:  
daoField.Properties.Append daoField.CreateProperty("Description", dbText, daoRecordSet("MSAccess_Field_Comment"))
Runtime Error 3219

Dim daoRecordSet As DAO.Recordset 


Dim daotableDefinition As New DAO.TableDef 
Dim daotableDefinitions As DAO.TableDefs 
Dim daoDataBase As DAO.Database 


Public Sub CreateEKYCBACTable() 
Set daoRecordSet = CurrentDb.OpenRecordset("SELECT * FROM MSAccessDataTypeMapping") 
Set daoDataBase = CurrentDb 
Set daotableDefinitions = CurrentDb.TableDefs 

daotableDefinition.Name = "MyNewCreatedTable" 

Do Until daoRecordSet.EOF 
    Dim daoField As New DAO.Field 

    Select Case daoRecordSet("MSAccess_Target_Field_type") 
    Case "dbText" 
        daoField.Name = daoRecordSet("Target_Field") 
        daoField.Type = dbText 
        'daoField.Size = "50" 
    Case "dbInteger" 
        daoField.Name = daoRecordSet("Target_Field") 
        daoField.Type = dbInteger 
        'daoField.Size = "50" 
    Case "dbDate" 
        daoField.Name = daoRecordSet("Target_Field") 
        daoField.Type = dbDate 
        'daoField.Size = "50" 
    Case "dbBoolean" 
        daoField.Name = daoRecordSet("Target_Field") 
        daoField.Type = dbBoolean 
        'daoField.Size = "50" 
    Case Else 
        daoField.Name = daoRecordSet("Target_Field") 
        daoField.Type = dbText 
        'daoField.Size = "50" 
    End Select 
        daotableDefinition.Fields.Append daoField 
[b]        daoField.Properties.Append daoField.CreateProperty("Description", dbText, daoRecordSet("MSAccess_Field_Comment")) [/b]
    daoRecordSet.MoveNext 
Loop 

daotableDefinition.Fields.Refresh 
daotableDefinitions.Append daotableDefinition 
daotableDefinitions.Refresh 
Set daotableDefinitions = Nothing 
Set daotableDefinition = Nothing 
Set daoDataBase = Nothing

Select allOpen in new window

Create the Property and then Append it:

Dim prp As DAO.Property
Set prp = fld.CreateProperty(blah blah)
fld.Properties.Append prp

Also, do this BEFORE you append the Field to the TableDef ...

Dear Scott,
Yes I had this solution before, but still the same error 'Runtime Error 3219' on line:
daoField.Properties.Append daoPoperty

Public Sub Createable() 
Set daoRecordSet = CurrentDb.OpenRecordset("SELECT * FROM  MSAccessDataTypeMapping") 
Set daoDataBase = CurrentDb 
Set daotableDefinitions = CurrentDb.TableDefs 

daotableDefinition.Name = "MyNewCreatedTable" 

Do Until daoRecordSet.EOF 
    Dim daoField As New DAO.Field 
    Dim daoPoperty As DAO.Property 

    Select Case daoRecordSet("MSAccess_Target_Field_type") 
    Case "dbText" 
        daoField.Name = daoRecordSet("Target_Field") 
        daoField.Type = dbText 
        'daoField.Size = "50" 
    Case "dbInteger" 
        daoField.Name = daoRecordSet("Target_Field") 
        daoField.Type = dbInteger 
        'daoField.Size = "50" 
    Case "dbDate" 
        daoField.Name = daoRecordSet("Target_Field") 
        daoField.Type = dbDate 
        'daoField.Size = "50" 
    Case "dbBoolean" 
        daoField.Name = daoRecordSet("Target_Field") 
        daoField.Type = dbBoolean 
        'daoField.Size = "50" 
    Case Else 
        daoField.Name = daoRecordSet("Target_Field") 
        daoField.Type = dbText 
        'daoField.Size = "50" 
    End Select 
    Set daoPoperty = daoField.CreateProperty("Description", dbText, daoRecordSet("MSAccess_Field_Comment")) 
    daoField.Properties.Append daoPoperty 
    daotableDefinition.Fields.Append daoField 
    daoRecordSet.MoveNext 
Loop 

daotableDefinition.Fields.Refresh 
daotableDefinitions.Append daotableDefinition 
daotableDefinitions.Refresh 
Set daotableDefinitions = Nothing 
Set daotableDefinition = Nothing 
Set daoDataBase = Nothing 
End Sub

Select allOpen in new window

Sorry, it's been a while since I've done that.

You have to create and append the Table to the TableDefs collection FIRST, then go back through and add the Description value to the individual Fields.

Dear Scott,
Would you by chance have this code example
That would be great
THX

So my code would look like this, the new loop part is highlighted

Do Until daoRecordSet.EOF 
    Dim daoField As New DAO.Field  

    Select Case daoRecordSet("MSAccess_Target_Field_type") 
    Case "dbText" 
        daoField.Name = daoRecordSet("Target_Field") 
        daoField.Type = dbText 
        'daoField.Size = "50" 
    Case "dbInteger" 
        daoField.Name = daoRecordSet("Target_Field") 
        daoField.Type = dbInteger 
        'daoField.Size = "50" 
    Case "dbDate" 
        daoField.Name = daoRecordSet("Target_Field") 
        daoField.Type = dbDate 
        'daoField.Size = "50" 
    Case "dbBoolean" 
        daoField.Name = daoRecordSet("Target_Field") 
        daoField.Type = dbBoolean 
        'daoField.Size = "50" 
    Case Else 
        daoField.Name = daoRecordSet("Target_Field") 
        daoField.Type = dbText 
        'daoField.Size = "50" 
    End Select 
    daotableDefinition.Fields.Append daoField 
    daoRecordSet.MoveNext 
Loop 


daotableDefinition.Fields.Refresh 
daotableDefinitions.Append daotableDefinition 
daotableDefinitions.Refresh 

[b]daoRecordSet.MoveFirst
Do Until daoRecordSet.EOF
    Dim daoPoperty As DAO.Property 
	Set daoField = daotableDefinition.Fields(daoRecordSet("Target_Field"))
  ' create the Property here
    Set daoPoperty = daoField.CreateProperty("Description", dbText, daoRecordSet("MSAccess_Field_Comment")) 
  ' append the Property to the Field's Properties collection
    daoField.Properties.Append daoPoperty 
  daotableDefinition.Fields.Refresh
  daoRecordSet.MoveNext
Loop[/b]

Set daotableDefinitions = Nothing 
Set daotableDefinition = Nothing 
Set daoDataBase = Nothing 
End Sub

Select allOpen in new window

this is the new code part

daoRecordSet.MoveFirst
Do Until daoRecordSet.EOF
    Dim daoPoperty As DAO.Property
      Set daoField = daotableDefinition.Fields(daoRecordSet("Target_Field"))
  ' create the Property here
    Set daoPoperty = daoField.CreateProperty("Description", dbText, daoRecordSet("MSAccess_Field_Comment"))
  ' append the Property to the Field's Properties collection
    daoField.Properties.Append daoPoperty
  daotableDefinition.Fields.Refresh
  daoRecordSet.MoveNext
Loop

Thx Scott! Works very well now!