Issue while creating tables in ms access using VB.NET ADOX [modified]

[sivakumar mariappan]

Hi, While trying to create a group of tables in msaccess using VB.NET ADOX, i am facing the error "System.Runtime.InteropServices.COMException...at ADOX.columns.Append(Object item, DatatypeEnum Type, Int32 DefinedSize)". The coding is given below.. Public Function CreateAccessDatabaseandtables( _ ByVal DatabaseFullPath As String) As Boolean Dim bAns As Boolean Dim con As New ADODB.Connection Dim cat As New ADOX.Catalog() Dim ADOXtable As New ADOX.Table Dim ADOXindex As New ADOX.Index Try Dim sCreateString As String sCreateString = _ "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _ DatabaseFullPath cat.Create(sCreateString) bAns = True 'cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DatabaseFullPath con.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DatabaseFullPath) 'name table, append fields to table ADOXtable.Name = "ACN_CHARX" ADOXtable.Columns.Append("LOGICAL_DB_NAME", ADOX.DataTypeEnum.adVarWChar, 32) ADOXtable.Columns.Append("DIM_NAME", ADOX.DataTypeEnum.adVarWChar, 32) ADOXtable.Columns.Append("CHARX_NAME", ADOX.DataTypeEnum.adVarWChar, 128) ADOXtable.Columns.Append("CHARX_SEQ", ADOX.DataTypeEnum.adInteger) ADOXtable.Columns.Append("CHARX_TYPE", ADOX.DataTypeEnum.adVarWChar, 1) ADOXtable.Columns.Append("BITMAP", ADOX.DataTypeEnum.adInteger) 'append tables to database cat.Tables.Append(ADOXtable) ADOXtable.Name = "ACN_CHARX_INSTANCE" ADOXtable.Columns.Append("LOGICAL_DB_NAME", ADOX.DataTypeEnum.adVarWChar, 32) ADOXtable.Columns.Append("DIM_NAME", ADOX.DataTypeEnum.adVarWChar, 32) ADOXtable.Columns.Append("CHARX_NAME", ADOX.DataTypeEnum.adVarWChar, 128) ADOXtable.Columns.Append("CHARX_TABLE", ADOX.DataTypeEnum.adVarWChar, 128) ADOXtable.Columns.Append("CHARX_VALUE_COL", ADOX.DataTypeEnum.adVarWChar, 128) ADOXtable.Columns.Append("VALUES_UNIQUE", ADOX.DataTypeEnum.adInteger) ADOXtable.Columns.Append("CHARX_ORDER_COL", ADOX.DataTypeEnum.adVarWChar, 128) ADOXtable.Columns.Append("CHARX_ORDER", ADOX.DataTypeEnum.adInteger) ADOXtable.Columns.Append("CHARX_NUMERIC_COL", ADOX.DataTypeEnum.adVarWChar, 128) ADOXtabl

[Johan Hakkesteegt]

Does the exception tell which line of code exactly is throwing the error?

My advice is free, and you may get what you paid for.

[sivakumar mariappan]

No...Actually the first table "ACN_CHARX" is created successfully... when it crosses the line "ADOXtable.Name = "ACN_CHARX_INSTANCE" ", the first created table name is automatically renamed.. ie)from "ACN_CHARX" into "ACN_CHARX_INSTANCE". Then the control tried to create the first column of the second table "Logical_db_name"...Then it shows this error....

[Ashfield]

I expect you need to add

ADOXtable = New ADOX.Table

between each table. Probably the same for the index.

Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP

[sivakumar mariappan]

Thanks for your nice solution....Now it is working fine....In addition with this, could you please mention how to add constraints with table columns through coding?

[Ashfield]

No problem, easy mistake to make, done it myself but usually realise when running in debug and stepping through the code

sivakumar.mariappan wrote:

how to add constraints with table columns through coding?

No idea, I don't use access, but I bet google can help

Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP