none
How to add a new table to an access accdb database using vb.net RRS feed

  • Question

  • Hei,

    I am working on a small program for an update of a existing database.
    I am trying to add a New table to a Access .accdb database using pure vb.net code.
    The code I have now is as follows:

    Dim databaseName As String = m_Path &"/XXXXXXXXXXX.accdb"

                    Dim Cn As ADODB.Connection, Cat As ADOX.Catalog, objTable As ADOX.Table
    
                    Cn = New ADODB.Connection
                    Cat = New ADOX.Catalog
                    objTable = New ADOX.Table
    
                    'Open the connection
                    Cn.Open("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
                               databaseName & ";Jet OLEDB:Database Password=" & "nnnnnnn;")
    
                    'Open the Catalog
                    Cat.ActiveConnection = Cn
    
                    'Create the table
                    objTable.Name = tableName
    
                    'Create and Append new fields to the "tableName" Columns Collection
                    objTable.Columns.Append("RecipeCounter", DataTypeEnum.adInteger)
                    objTable.Keys.Append("PrimaryKey", KeyTypeEnum.adKeyPrimary, "RecipeCounter")
                    objTable.Columns.Append("Preparations", DataTypeEnum.adVarWChar, 255)
    
                    'Append the newly created table to the Tables Collection
                    Cat.Tables.Append(objTable)
    
                    ' clean up objects
                    objTable = Nothing
                    Cat = Nothing
                    Cn.Close()
                    Cn = Nothing
     

    The code adds the table allright with the two columns as specified, but the problem is I want the "Preparations" column to have the "LongText" datatype and the text format should be "Rich Text" format.

    I am using Visual Studio 2010 with Visual basic.

    I would be very grateful if somebody could point me in the right direction
    Kind regards


    levesen



    Thursday, February 6, 2014 9:20 PM

Answers

  • Hi again,
    The 'adLongVarWChar' did the trick, and it looks like I do not have to format the column to 'Rich Text Format' either. This column stores the content of a Rich text box (together with pictures), and works rather well without the formating.
    Thanks a lot for helping me out, I really appreciate it.


    levesen


    • Marked as answer by levesen Friday, February 7, 2014 6:55 PM
    • Edited by levesen Friday, February 7, 2014 6:56 PM
    Friday, February 7, 2014 6:55 PM

All replies

  • Hello,

    >> the "Preparations" column to have the "LongText" datatype

    The long text is renamed to be memo and should be mapped to DataTypeEnum.adLongVarWChar type.

    >>the text format should be "Rich Text" format.

    The format of the column in Access should be determined by Format property of the Table Column (e.g. On/Off, Yes/No, etc.) when viewing the table in design mode. This property is not available through ADOX since it's specific to Access.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, February 7, 2014 8:23 AM
    Moderator
  • look at this sample on our website. 

    http://www.vb-tips.com/CreateAccdb.ASPX


    Success
    Cor

    Friday, February 7, 2014 11:22 AM
  • Hi again,
    The 'adLongVarWChar' did the trick, and it looks like I do not have to format the column to 'Rich Text Format' either. This column stores the content of a Rich text box (together with pictures), and works rather well without the formating.
    Thanks a lot for helping me out, I really appreciate it.


    levesen


    • Marked as answer by levesen Friday, February 7, 2014 6:55 PM
    • Edited by levesen Friday, February 7, 2014 6:56 PM
    Friday, February 7, 2014 6:55 PM