none
How to Alter Table Microsoft Access using VB.NET RRS feed

  • Question

  • I have a table in a database that was sent out to our customers. I need to be able to change one field, CityName.

    Does anyone know the proper syntax to change "CityName Allow Zero Length Yes"

     

    I tried a couple of things but I get invalid Syntax

     

                    Dim SQL As String = "ALTER TABLE Accum "
                    SQL += "ALTER COLUMN A_ToCityName AllowZeroLength'yes'"

     

    Bill
    Thursday, December 6, 2007 11:07 PM

Answers

  • Hi Bill,

     

    Based on your post, my understanding of your question is that you need to modify the structure of the Access database.

     

    In this scenario you manipulate the text type field. We can use the MS ADOX library to handle the database creation and structure modification. Please read ADOX in Detail to get the more information about the ADOX library. Here is the code snippet to modify the text field to allow zero length. It should satisfy your requirement. Hope this helps.  

    Code Block

    Imports ADOX

    Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    Me.CreateAutoNumberField("d:\Northwind.mdb")

    End Sub

    Public Sub CreateAutoNumberField(ByVal strDBPath As String)

    Dim cnn As New ADOdb.Connection

    Dim catDB As New ADOX.Catalog

    cnn.Open("Provider='Microsoft.Jet.OLEDB.4.0';" & "Data Source= 'd:\Northwind.mdb';")

    Dim tbl As ADOX.Table

    catDB = New ADOX.Catalog ' Open the catalog.

    catDB.ActiveConnection = cnn

    tbl = catDB.Tables("Table2")

    Dim tc As ADOX.Column

    tc = tbl.Columns.Item("Tocityname")

    tc.Properties("Jet OLEDB:Allow Zero Length").Value = True

    catDB = Nothing

    End Sub

    End Class

     

     

     

    Best regards,

    Riquel

    Tuesday, December 11, 2007 9:46 AM
    Moderator

All replies

  • Hi Bill,

     

    Based on your post, my understanding of your question is that you need to modify the structure of the Access database.

     

    In this scenario you manipulate the text type field. We can use the MS ADOX library to handle the database creation and structure modification. Please read ADOX in Detail to get the more information about the ADOX library. Here is the code snippet to modify the text field to allow zero length. It should satisfy your requirement. Hope this helps.  

    Code Block

    Imports ADOX

    Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    Me.CreateAutoNumberField("d:\Northwind.mdb")

    End Sub

    Public Sub CreateAutoNumberField(ByVal strDBPath As String)

    Dim cnn As New ADOdb.Connection

    Dim catDB As New ADOX.Catalog

    cnn.Open("Provider='Microsoft.Jet.OLEDB.4.0';" & "Data Source= 'd:\Northwind.mdb';")

    Dim tbl As ADOX.Table

    catDB = New ADOX.Catalog ' Open the catalog.

    catDB.ActiveConnection = cnn

    tbl = catDB.Tables("Table2")

    Dim tc As ADOX.Column

    tc = tbl.Columns.Item("Tocityname")

    tc.Properties("Jet OLEDB:Allow Zero Length").Value = True

    catDB = Nothing

    End Sub

    End Class

     

     

     

    Best regards,

    Riquel

    Tuesday, December 11, 2007 9:46 AM
    Moderator
  • Hi Riquel Dong,

    Thanks for your idea.

    But i am facing problem with this implementation.

    I wanna add one column(MEMO) type with my access database table(2000).

    I need to set the properties Required = false and Allow Zero Length = true

    Its adding the column correctly but its not setting other properties(Allow Zero Length,Default etc).]

    And some times setting Nullable = Yes and some times setting Nullable = No

    Waiting for your suggestion.

    Thanks in advance.
    Rousseau Arulsamy

    For your reference here my code:

    Catalog catalog = new Catalog();
    ADODB.Connection adoDbConnection = new ADODB.Connection();
    string connectionString = this.GetConnectionString(this.filePath);
    catalog.let_ActiveConnection(connectionString);
    Table datasetTable;
    if (catalog != null && catalog.Tables.Count > 0)
    {
    try
    {
    datasetTable = catalog.Tables["DataSets"];
    if (!this.IsColumnExist(datasetTable.Columns, "UddString"))
    {
    Column uddStringColumn = new Column();
    uddStringColumn.ParentCatalog = catalog;
    uddStringColumn.Name = "UddString";
    uddStringColumn.Properties["Nullable"].Value = true;
    uddStringColumn.Properties["Default"].Value = "rousseau";
    uddStringColumn.Properties["Description"].Value = "rousseau";
    uddStringColumn.Properties["Jet OLEDB:Allow Zero Length"].Value = true;
    uddStringColumn.Type = DataTypeEnum.adLongVarWChar;

    datasetTable.Columns.Append("UddString", DataTypeEnum.adLongVarWChar,50);
    datasetTable.Columns.Refresh();
    catalog.Tables.Refresh();
    MessageBox.Show("Column Added Successfully");
    }
    }
    catch (Exception ex)
    {
    System.Diagnostics.Trace.WriteLine(ex.Message);
    }
    finally
    {
    catalog = null;
    }
    }

    private string GetConnectionString(string filePath)
    {
    return "Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + filePath ;
    }

    private bool IsColumnExist(Columns columns, string columnName)
    {
    bool isColumnExist = false;
    foreach (Column clm in columns)
    {
    if (string.Equals(clm.Name.ToString(),columnName))
    {
    isColumnExist = true;
    break;
    }
    }
    return isColumnExist;
    }


    Thursday, January 10, 2008 11:40 AM