Create MSAccess Table based on excel column definition

Dear Experts,
I have column definitions in excel. I want to create a MS Access table based on the column definition in excel. I want to create the Table structure: Field Name, Field type based on the definitions in excel. I Do NOT want to import data. The excel provides the DDL. Is there an easy way or is the solution to create a create table statement based on the excel? Does some one probably have a VBA script in case this is only solvable by script?
I have attached the excel sheet with the DDL for the MS Access Table, in sheet: 'MSAccessTable DDL', I have the attribute definition. The goal is to have a MS Access table with the according attributes - column 'ComunName' in the excel and the data type, 'column' in excel
Vlookup.xlsx
mruffAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

aikimarkCommented:
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.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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

Open in new window

0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

aikimarkCommented:
In Access, the default type is Long
0
mruffAuthor Commented:
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
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
0
aikimarkCommented:
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.
0
mruffAuthor Commented:
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

Open in new window

0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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 ...
0
mruffAuthor Commented:
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

Open in new window

0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
mruffAuthor Commented:
Dear Scott,
Would you by chance have this code example
That would be great
THX
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You just have to reorder the way you add the Field's Description.

After your loop through the recordset and add the fields, refresh the TableDefs collection, and the loop BACK through the recordset and add the Description to the Field. Sor AFTER line 42 in your code sample above, create another loop, and this time set the fld variable to the TableDef's Field:

Do Until rst.EOF
  Set fld = tdf.Fields(rst("FieldName")
  ' create the Property here
  ' append the Property to the Field's Properties collection
  tdf.Fields.Refresh
  rst.MoveNext
Loop
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mruffAuthor Commented:
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

Open in new window

0
mruffAuthor Commented:
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
0
mruffAuthor Commented:
Thx Scott! Works very well now!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.