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

Question

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

I have answered your original question and then identified the command you committed. Now you are getting an error that I can not reproduce. I find changing the question to incorporate a valid answer and then asking a new question extremely rude. It effectively invalidates original answer. I will not play this game any more. — TnTinMn, Jul 27 '18 at 15:17
@TnTinMn: Calm down haha. It's just solved when I run it second time. I think it's a some kind of glitch etc .. IDK .. But now it works. :) Thank you very much, and I accepted your answer — Pretty_Girl3, Jul 28 '18 at 09:26

TnTinMn · Accepted Answer · 2018-07-26T19:25:49.320

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.

Thank you for your reply, but now it produces a another error Item cannot be found in the collection corresponding to the requested name or ordinal. So, is it basically saying that the column Key doesn't exists? But, since we create Key column in the .Append("Key", adInteger) how can it say like that? what's missing here? — Pretty_Girl3, Jul 27 '18 at 07:12
@Pretty_Girl3, you omitted the statement `.ParentCatalog = catDB` within the `With tblNew` block, — TnTinMn, Jul 27 '18 at 13:47
updated .. but it still get's errors `The connection cannot be used to perform this operation. It is either closed or invalid in this context.` — Pretty_Girl3, Jul 27 '18 at 14:41

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

1 Answers1