sqlvb.netadox

How to set Adox.table auto increment property to a column?


The .Item("Key").Properties("AutoIncrement") = True is not setting the column type to autoincrement number. It says it's read only, but it's in the microsoft official website. This was in the older non updated version of microsoft docs https://learn.microsoft.com/en-us/previous-versions/office/developer/office2000/aa164917(v=office.10) Seems like it doesn't work now for Visual studio 2012 vb.net How to set the column "key" as an auto increment number?

Error

Property 'Item' is 'ReadOnly'

Imports ADOX 
Imports ADOX.DataTypeEnum

   Private Sub Button11_Click(sender As Object, e As EventArgs) Handles Button11.Click

            Dim DB1_file_name As String = "\DB3.mdb"
            Dim catDB As ADOX.Catalog
            Dim tblNew As ADOX.Table
            Dim catstring As String

            catDB = New ADOX.Catalog
            ' Open the catalog.
            'catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.UserAppDataPath & "\DB1.mdb"
            catstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.UserAppDataPath & DB1_file_name
            catDB.Create(catstring)
            'catDB.ActiveConnection = catstring

            tblNew = New ADOX.Table
            ' Create a new Table object.
            With tblNew
                .Name = "Contacts"


                With .Columns
                    .Append("Key", adInteger)
                    .Item("Key").Properties("AutoIncrement") = True
                    .Append("FirstName", adVarWChar)
                    .Append("LastName", adVarWChar)
                    .Append("Phone", adVarWChar)
                    .Append("Notes", adLongVarWChar)

                End With
            End With

            ' Add the new Table to the Tables collection of the database.
            catDB.Tables.Append(tblNew)

            catDB = Nothing
        End Sub

P.S: Updated code - still gets errors

The connection cannot be used to perform this operation. It is either closed or invalid in this context.

Imports ADOX 
Imports ADOX.DataTypeEnum

   Private Sub Button11_Click(sender As Object, e As EventArgs) Handles Button11.Click

            Dim DB1_file_name As String = "\DB3.mdb"
            Dim catDB As ADOX.Catalog
            Dim tblNew As ADOX.Table
            Dim catstring As String

            catDB = New ADOX.Catalog
            ' Open the catalog.
            'catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.UserAppDataPath & "\DB1.mdb"
            catstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.UserAppDataPath & DB1_file_name
            catDB.Create(catstring)
            'catDB.ActiveConnection = catstring

            tblNew = New ADOX.Table
            ' Create a new Table object.
            With tblNew
                .Name = "Contacts"
                .ParentCatalog = catDB

                With .Columns
                    .Append("Key", adInteger)
                    .Item("Key").Properties("AutoIncrement").Value = True
                    .Append("FirstName", adVarWChar)
                    .Append("LastName", adVarWChar)
                    .Append("Phone", adVarWChar)
                    .Append("Notes", adLongVarWChar)

                End With
            End With

            ' Add the new Table to the Tables collection of the database.
            catDB.Tables.Append(tblNew)

            catDB = Nothing
        End Sub

Solution

  • The original code that you used as a basis for your VB.Net code was written in VBA.

    .Item("Key").Properties("AutoIncrement") = True
    

    This statement is assigning True to the default property of the ADOX.Property returned by the left side of the assignment statement. This syntax is valid for VBA but not VB.Net. The default property of a ADOX.Property object is its Value property.

    You have a few options to correct this. The most clear method would be to explicitly specify that you want to assign the Value property.

    .Item("Key").Properties("AutoIncrement").Value = True
    

    or

    Dim prop As ADOX.Property = .Item("Key").Properties("AutoIncrement")
    prop.Value = True
    

    You could also use this syntax for referencing the default property.

    .Item("ContactId").Properties("AutoIncrement")() = True
    

    In .Net, default properties are typically referred to as the indexer property and take an integer argument. The COM based default property does not require an argument, but to tell the VB compiler that you want to reference it, you need the extra () without any enclosed argument.

    For more info, see: How to: Declare and Call a Default Property in Visual Basic.