Create autoincrement Column in Access

[Antithott]

Hi,

Does anyone know a way, to create a ID field in a access database with the Autoincrement enabled thru code?

I been trying with this code :

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


Cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Databasesti)
Cat.ActiveConnection = Cn

objTable.Name = "PcOversigt"

objTable.Columns.Append("Id", DataTypeEnum.adInteger)
With objTable.Columns("Id")
.Properties("AutoIncrement").Value = True
End With

objTable.Keys.Append("PrimaryKey", KeyTypeEnum.adKeyPrimary, "Id")

Cat.Tables.Append(objTable)


Googling didnt help and i didnt seem to find any subject in tek-tips which could help

 

[earthandfire]

I'm sure that it is possible with ADO, but I have no idea how. It can quite easily be done with DAO - this shows how to use DAO through VB.NET.


thread796-1256103


Hope this helps.

 

[jebenson]

I made a change to your code - shown in red below - and it now works:

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


Cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp\db1.mdb")
Cat.ActiveConnection = Cn

objTable.Name = "PcOversigt"

[red]objTable.ParentCatalog = Cat[/red]

objTable.Columns.Append("Id", ADOX.DataTypeEnum.adInteger)

With objTable.Columns("Id")
.Properties("AutoIncrement").Value = True
End With

objTable.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "Id")

Cat.Tables.Append(objTable)

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day! Ye has a choice: talk like a pira

 

[Antithott]

Thanks a ton, Worked like a charm.

Any chance you could tell me why that line was nessecary?

 

[jebenson]

As to why...I'm not really sure. I found the answer at this MSDN page:

Creating and Modifying Access Tables

the code is in VB6 but it's easy to convert.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day! Ye has a choice: talk like a pira