• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 407
  • Last Modified:

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
0
mruff
Asked:
mruff
  • 7
  • 6
  • 3
1 Solution
 
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

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

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 7
  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now