Adding a key to MS Access Table

[Art]

Hi everyone

I was hoping someone might be able to help me with this. I'm just starting to try to work with MS Access tables through VB.net. In Access I can take an existing table and add a new field with the type AutoNumber. I can then set this up as a key. Then if I go into the table I will see sequential numbers have been inserted into that field for me. Can I do this through VB.net

I'd appreciate any help or alternative suggestions

Thanks very much

Art

[harry]

Re: Adding a key to MS Access Table


Below is copy of an old post. This may help you get started.


Hi Frank,

I tried both your sample and the post by Josef on 02/13/2004, however as
weird as it appears, all solutions still return error: "Item is ReadOnly"

I searched net and all samples I found were same/similat to both your's and
MSDN

However, for your ref and anyone else pulling their hair out with this crazy
problem, I found (stumbled across) a solution...

..Item("Referen ce").ParentCata log = cat
..Item("Referen ce").Properties ("AutoIncrement ").Value = True

'and if you like to set seed + increment values...

..Item("Referen ce").Properties .Item("Seed").V alue = 1 'or whatever your
preference
..Item("Referen ce").Properties .Item("Incremen t").Value = 1 'or whatever your
preference

Regards
Harry



"Frank Hickman" <fhickman_nosp@ m_noblesoft.com > wrote in message
news:PNGdnYYZcr 6CVt_dRVn-uw@comcast.com. ..[color=blue]
> Try adding this line prior to setting the property...
>
> .Item("ContactI d").ParentCatal og = catDB;
>
> My VB is a little rusty so you may have to tweak it :)
>
>
> Sub CreateAutoNumbe rField(strDBPat h As String)
> Dim catDB As ADOX.Catalog
> Dim tbl As ADOX.Table
>
> Set catDB = New ADOX.Catalog
> ' Open the catalog.
> catDB.ActiveCon nection = "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
> "Data Source=" & strDBPath
>
> Set tbl = New ADOX.Table
> With tbl
> .Name = "Contacts"
> Set .ParentCatalog = catDB
> ' Create fields and append them to the
> ' Columns collection of the new Table object.
> With .Columns
> .Append "ContactId" , adInteger
> ' Make the ContactId field auto-incrementing.
> .Item("ContactI d").ParentCatal og = catDB;
> .Item("ContactI d").Properties( "AutoIncrement" ) = True
> .Append "CustomerID ", adVarWChar
> .Append "FirstName" , adVarWChar
> .Append "LastName", adVarWChar
> .Append "Phone", adVarWChar, 20
> .Append "Notes", adLongVarWChar
> End With
> End With
>
> ' Add the new Table to the Tables collection of the database.
> catDB.Tables.Ap pend tbl
>
> Set catDB = Nothing
> End Sub
>
> This solution was posted by Josef Blösl on 02/13/2004 in a reply to my[/color]
reply[color=blue]
> about the same subject. My solution was somewhat different so if the[/color]
above[color=blue]
> does not work for you, you may want to try that instead. Which was to go
> ahead and append the table to the catalog and then set the column[/color]
property.[color=blue]
> But his solution should work as appending the table essentially does this
> too.
>
> HTH
> --
> ============
> Frank Hickman
> NobleSoft, Inc.
> ============
> Replace the _nosp@m_ with @ to reply.
>
>
> "harry" <harry@nospam > wrote in message
> news:uNA2FOz$DH A.1464@tk2msftn gp13.phx.gbl...[color=green]
> > Previously posted on microsoft.publi c.data.oledb
> >
> > Hi,
> >
> > I'm trying to create AutoIncrement column in a new Access database table
> > via ADOX.
> > I am using MSDN sample code however I still receive error: Property[/color]
> 'Item'[color=green]
> > is 'ReadOnly'
> >
> > The line triggering the error is:
> > .Item("ContactI d").Properties( "AutoIncrement" ) = True
> >
> > I'm using MDAC 2.8 and VB.NET
> >
> > How do I create AutoIncrement column when creating new table using ADOX[/color][/color]
?[color=blue][color=green]
> >
> > Thanks
> > Harry
> >
> > This is the Sample I tried...
> >
> >[/color]
>[/color]
http://msdn.microsoft.com/library/de...cesstables.asp[color=blue][color=green]
> >
> > Sub CreateAutoNumbe rField(strDBPat h As String)
> > Dim catDB As ADOX.Catalog
> > Dim tbl As ADOX.Table
> >
> > Set catDB = New ADOX.Catalog
> > ' Open the catalog.
> > catDB.ActiveCon nection = "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
> > "Data Source=" & strDBPath
> >
> > Set tbl = New ADOX.Table
> > With tbl
> > .Name = "Contacts"
> > Set .ParentCatalog = catDB
> > ' Create fields and append them to the
> > ' Columns collection of the new Table object.
> > With .Columns
> > .Append "ContactId" , adInteger
> > ' Make the ContactId field auto-incrementing.
> > .Item("ContactI d").Properties( "AutoIncrement" ) = True
> > .Append "CustomerID ", adVarWChar
> > .Append "FirstName" , adVarWChar
> > .Append "LastName", adVarWChar
> > .Append "Phone", adVarWChar, 20
> > .Append "Notes", adLongVarWChar
> > End With
> > End With
> >
> > ' Add the new Table to the Tables collection of the database.
> > catDB.Tables.Ap pend tbl
> >
> > Set catDB = Nothing
> > End Sub
> >
> >
> >
> >
> >
> >[/color]
>
>[/color]







"Art" <arthlan@yahoo. com> wrote in message
news:E99CD7F0-BA9C-4171-BF0F-EA3D987C01EA@mi crosoft.com...[color=blue]
> Hi everyone,
>
> I was hoping someone might be able to help me with this. I'm just[/color]
starting to try to work with MS Access tables through VB.net. In Access I
can take an existing table and add a new field with the type AutoNumber. I
can then set this up as a key. Then if I go into the table I will see
sequential numbers have been inserted into that field for me. Can I do this
through VB.net?[color=blue]
>
> I'd appreciate any help or alternative suggestions.
>
> Thanks very much,
>
> Art[/color]

[harry]

Re: Adding a key to MS Access Table


Below is copy of an old post. This may help you get started.


Hi Frank,

I tried both your sample and the post by Josef on 02/13/2004, however as
weird as it appears, all solutions still return error: "Item is ReadOnly"

I searched net and all samples I found were same/similat to both your's and
MSDN

However, for your ref and anyone else pulling their hair out with this crazy
problem, I found (stumbled across) a solution...

..Item("Referen ce").ParentCata log = cat
..Item("Referen ce").Properties ("AutoIncrement ").Value = True

'and if you like to set seed + increment values...

..Item("Referen ce").Properties .Item("Seed").V alue = 1 'or whatever your
preference
..Item("Referen ce").Properties .Item("Incremen t").Value = 1 'or whatever your
preference

Regards
Harry



"Frank Hickman" <fhickman_nosp@ m_noblesoft.com > wrote in message
news:PNGdnYYZcr 6CVt_dRVn-uw@comcast.com. ..[color=blue]
> Try adding this line prior to setting the property...
>
> .Item("ContactI d").ParentCatal og = catDB;
>
> My VB is a little rusty so you may have to tweak it :)
>
>
> Sub CreateAutoNumbe rField(strDBPat h As String)
> Dim catDB As ADOX.Catalog
> Dim tbl As ADOX.Table
>
> Set catDB = New ADOX.Catalog
> ' Open the catalog.
> catDB.ActiveCon nection = "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
> "Data Source=" & strDBPath
>
> Set tbl = New ADOX.Table
> With tbl
> .Name = "Contacts"
> Set .ParentCatalog = catDB
> ' Create fields and append them to the
> ' Columns collection of the new Table object.
> With .Columns
> .Append "ContactId" , adInteger
> ' Make the ContactId field auto-incrementing.
> .Item("ContactI d").ParentCatal og = catDB;
> .Item("ContactI d").Properties( "AutoIncrement" ) = True
> .Append "CustomerID ", adVarWChar
> .Append "FirstName" , adVarWChar
> .Append "LastName", adVarWChar
> .Append "Phone", adVarWChar, 20
> .Append "Notes", adLongVarWChar
> End With
> End With
>
> ' Add the new Table to the Tables collection of the database.
> catDB.Tables.Ap pend tbl
>
> Set catDB = Nothing
> End Sub
>
> This solution was posted by Josef Blösl on 02/13/2004 in a reply to my[/color]
reply[color=blue]
> about the same subject. My solution was somewhat different so if the[/color]
above[color=blue]
> does not work for you, you may want to try that instead. Which was to go
> ahead and append the table to the catalog and then set the column[/color]
property.[color=blue]
> But his solution should work as appending the table essentially does this
> too.
>
> HTH
> --
> ============
> Frank Hickman
> NobleSoft, Inc.
> ============
> Replace the _nosp@m_ with @ to reply.
>
>
> "harry" <harry@nospam > wrote in message
> news:uNA2FOz$DH A.1464@tk2msftn gp13.phx.gbl...[color=green]
> > Previously posted on microsoft.publi c.data.oledb
> >
> > Hi,
> >
> > I'm trying to create AutoIncrement column in a new Access database table
> > via ADOX.
> > I am using MSDN sample code however I still receive error: Property[/color]
> 'Item'[color=green]
> > is 'ReadOnly'
> >
> > The line triggering the error is:
> > .Item("ContactI d").Properties( "AutoIncrement" ) = True
> >
> > I'm using MDAC 2.8 and VB.NET
> >
> > How do I create AutoIncrement column when creating new table using ADOX[/color][/color]
?[color=blue][color=green]
> >
> > Thanks
> > Harry
> >
> > This is the Sample I tried...
> >
> >[/color]
>[/color]
http://msdn.microsoft.com/library/de...cesstables.asp[color=blue][color=green]
> >
> > Sub CreateAutoNumbe rField(strDBPat h As String)
> > Dim catDB As ADOX.Catalog
> > Dim tbl As ADOX.Table
> >
> > Set catDB = New ADOX.Catalog
> > ' Open the catalog.
> > catDB.ActiveCon nection = "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
> > "Data Source=" & strDBPath
> >
> > Set tbl = New ADOX.Table
> > With tbl
> > .Name = "Contacts"
> > Set .ParentCatalog = catDB
> > ' Create fields and append them to the
> > ' Columns collection of the new Table object.
> > With .Columns
> > .Append "ContactId" , adInteger
> > ' Make the ContactId field auto-incrementing.
> > .Item("ContactI d").Properties( "AutoIncrement" ) = True
> > .Append "CustomerID ", adVarWChar
> > .Append "FirstName" , adVarWChar
> > .Append "LastName", adVarWChar
> > .Append "Phone", adVarWChar, 20
> > .Append "Notes", adLongVarWChar
> > End With
> > End With
> >
> > ' Add the new Table to the Tables collection of the database.
> > catDB.Tables.Ap pend tbl
> >
> > Set catDB = Nothing
> > End Sub
> >
> >
> >
> >
> >
> >[/color]
>
>[/color]







"Art" <arthlan@yahoo. com> wrote in message
news:E99CD7F0-BA9C-4171-BF0F-EA3D987C01EA@mi crosoft.com...[color=blue]
> Hi everyone,
>
> I was hoping someone might be able to help me with this. I'm just[/color]
starting to try to work with MS Access tables through VB.net. In Access I
can take an existing table and add a new field with the type AutoNumber. I
can then set this up as a key. Then if I go into the table I will see
sequential numbers have been inserted into that field for me. Can I do this
through VB.net?[color=blue]
>
> I'd appreciate any help or alternative suggestions.
>
> Thanks very much,
>
> Art[/color]

[harry]

Re: Adding a key to MS Access Table

Also this old post may be useful...


"Miha Markic" <miha at rthand com> wrote in message
news:OTbtt4$4DH A.2392@TK2MSFTN GP10.phx.gbl...[color=blue]
> Hi,
>
> The first thing to change is identityseed/step on DataTable to negative
> values.
> Read also this:
>
> HOW TO: Retrieve an Identity Value from a Newly Inserted Record from
> SQL Server by Using Visual C# .NET
>
>[/color]
http://support.microsoft.com/default...Product=vcSnet[color=blue]
> and
> Managing an @@IDENTITY Crisis
>
>[/color]
http://msdn.microsoft.com/library/de...anidcrisis.asp[color=blue]
> --
> Miha Markic - RightHand .NET consulting & software development
> miha at rthand com
> www.rthand.com
>
>
> "harry" <harry@nospam > wrote in message
> news:OXeEOy$4DH A.2576@TK2MSFTN GP11.phx.gbl...[color=green]
> > Hi,
> >
> > I am unable to insert new rows into an SQL table with a Identity Column[/color]
> (The[color=green]
> > Identity column is also the Primary Key).
> >
> > The table is a new empty table, and the Identity column is set to Seed=1[/color]
> and[color=green]
> > Increment=1
> >
> > The error I receive when attempting to insert 3 new rows is:
> > Column RecNum is contrained to be unique. Value '1' is already present.
> >
> > These are the values being inserted.
> >
> > 0 1188110 False 3
> > 1 1216300 False 4
> > 2 1182714 False 3,4
> >
> > The first Column with IDENTITY value=0 is inserted however as it is the
> > first row it's Identity is set to 1. Therefore the subsequent row with
> > Identity=1 triggers exception I guess?
> >
> > How do I solve this? Shouldn't ADO.NET manage this bu itself?
> >
> > Thanks
> > Harry
> >
> >
> >
> >[/color]
>
>[/color]





"Art" <arthlan@yahoo. com> wrote in message
news:E99CD7F0-BA9C-4171-BF0F-EA3D987C01EA@mi crosoft.com...[color=blue]
> Hi everyone,
>
> I was hoping someone might be able to help me with this. I'm just[/color]
starting to try to work with MS Access tables through VB.net. In Access I
can take an existing table and add a new field with the type AutoNumber. I
can then set this up as a key. Then if I go into the table I will see
sequential numbers have been inserted into that field for me. Can I do this
through VB.net?[color=blue]
>
> I'd appreciate any help or alternative suggestions.
>
> Thanks very much,
>
> Art[/color]

[harry]

Re: Adding a key to MS Access Table

Also this old post may be useful...


"Miha Markic" <miha at rthand com> wrote in message
news:OTbtt4$4DH A.2392@TK2MSFTN GP10.phx.gbl...[color=blue]
> Hi,
>
> The first thing to change is identityseed/step on DataTable to negative
> values.
> Read also this:
>
> HOW TO: Retrieve an Identity Value from a Newly Inserted Record from
> SQL Server by Using Visual C# .NET
>
>[/color]
http://support.microsoft.com/default...Product=vcSnet[color=blue]
> and
> Managing an @@IDENTITY Crisis
>
>[/color]
http://msdn.microsoft.com/library/de...anidcrisis.asp[color=blue]
> --
> Miha Markic - RightHand .NET consulting & software development
> miha at rthand com
> www.rthand.com
>
>
> "harry" <harry@nospam > wrote in message
> news:OXeEOy$4DH A.2576@TK2MSFTN GP11.phx.gbl...[color=green]
> > Hi,
> >
> > I am unable to insert new rows into an SQL table with a Identity Column[/color]
> (The[color=green]
> > Identity column is also the Primary Key).
> >
> > The table is a new empty table, and the Identity column is set to Seed=1[/color]
> and[color=green]
> > Increment=1
> >
> > The error I receive when attempting to insert 3 new rows is:
> > Column RecNum is contrained to be unique. Value '1' is already present.
> >
> > These are the values being inserted.
> >
> > 0 1188110 False 3
> > 1 1216300 False 4
> > 2 1182714 False 3,4
> >
> > The first Column with IDENTITY value=0 is inserted however as it is the
> > first row it's Identity is set to 1. Therefore the subsequent row with
> > Identity=1 triggers exception I guess?
> >
> > How do I solve this? Shouldn't ADO.NET manage this bu itself?
> >
> > Thanks
> > Harry
> >
> >
> >
> >[/color]
>
>[/color]





"Art" <arthlan@yahoo. com> wrote in message
news:E99CD7F0-BA9C-4171-BF0F-EA3D987C01EA@mi crosoft.com...[color=blue]
> Hi everyone,
>
> I was hoping someone might be able to help me with this. I'm just[/color]
starting to try to work with MS Access tables through VB.net. In Access I
can take an existing table and add a new field with the type AutoNumber. I
can then set this up as a key. Then if I go into the table I will see
sequential numbers have been inserted into that field for me. Can I do this
through VB.net?[color=blue]
>
> I'd appreciate any help or alternative suggestions.
>
> Thanks very much,
>
> Art[/color]

[Cor Ligthert]

Re: Adding a key to MS Access Table

Hi Art,

Yes however keep in mind that when you are working with a dataset the real
number is given at the moment the dataset is updated in the database.
(Because the dataset is disconnected when you are working with it).

When you than "fill" it back from the database in your dataset you get the
real number.

Cor

[Cor Ligthert]

Re: Adding a key to MS Access Table

Hi Art,

Yes however keep in mind that when you are working with a dataset the real
number is given at the moment the dataset is updated in the database.
(Because the dataset is disconnected when you are working with it).

When you than "fill" it back from the database in your dataset you get the
real number.

Cor

[Art]

Re: Adding a key to MS Access Table

Harry

Thanks very much! I haven't yet tried to implement the stuff in your response. Since I'm fairly new at VB.net, I seem to get "bonus" information everytime I post a question. I was just "barely" familiar with how to deal with MS Access tables, and the information you provided showed me much more than I had asked. So thanks again

Art

[Art]

Re: Adding a key to MS Access Table

Harry

Thanks very much! I haven't yet tried to implement the stuff in your response. Since I'm fairly new at VB.net, I seem to get "bonus" information everytime I post a question. I was just "barely" familiar with how to deal with MS Access tables, and the information you provided showed me much more than I had asked. So thanks again

Art

[Art]

Re: Adding a key to MS Access Table

Cor

Thanks for the warning! As I'm fairly new to this, it's likely that I would have spent some time trying to figure out why my data didn't look right

Art

[Art]

Re: Adding a key to MS Access Table

Cor

Thanks for the warning! As I'm fairly new to this, it's likely that I would have spent some time trying to figure out why my data didn't look right

Art

[Paul Clement]

Re: Adding a key to MS Access Table

On Thu, 8 Apr 2004 13:51:05 -0700, Art <arthlan@yahoo. com> wrote:

¤ Hi everyone,
¤
¤ I was hoping someone might be able to help me with this. I'm just starting to try to work with MS Access tables through VB.net. In Access I can take an existing table and add a new field with the type AutoNumber. I can then set this up as a key. Then if I go into the table I will see sequential numbers have been inserted into that field for me. Can I do this through VB.net?
¤
¤ I'd appreciate any help or alternative suggestions.
¤

You can use Access SQL DDL:

ALTER TABLE Table4 ADD COLUMN IDField COUNTER CONSTRAINT PrimaryKey PRIMARY KEY


Paul ~~~ pclement@amerit ech.net
Microsoft MVP (Visual Basic)

[Paul Clement]

Re: Adding a key to MS Access Table

On Thu, 8 Apr 2004 13:51:05 -0700, Art <arthlan@yahoo. com> wrote:

¤ Hi everyone,
¤
¤ I was hoping someone might be able to help me with this. I'm just starting to try to work with MS Access tables through VB.net. In Access I can take an existing table and add a new field with the type AutoNumber. I can then set this up as a key. Then if I go into the table I will see sequential numbers have been inserted into that field for me. Can I do this through VB.net?
¤
¤ I'd appreciate any help or alternative suggestions.
¤

You can use Access SQL DDL:

ALTER TABLE Table4 ADD COLUMN IDField COUNTER CONSTRAINT PrimaryKey PRIMARY KEY


Paul ~~~ pclement@amerit ech.net
Microsoft MVP (Visual Basic)