472,995 Members | 1,910 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,995 software developers and data experts.

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
Nov 20 '05 #1
12 3501

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("Reference").ParentCatalog = cat
..Item("Reference").Properties("AutoIncrement").Va lue = True

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

..Item("Reference").Properties.Item("Seed").Value = 1 'or whatever your
preference
..Item("Reference").Properties.Item("Increment").V alue = 1 'or whatever your
preference

Regards
Harry

"Frank Hickman" <fhickman_nosp@m_noblesoft.com> wrote in message
news:PN********************@comcast.com...
Try adding this line prior to setting the property...

.Item("ContactId").ParentCatalog = catDB;

My VB is a little rusty so you may have to tweak it :)
Sub CreateAutoNumberField(strDBPath As String)
Dim catDB As ADOX.Catalog
Dim tbl As ADOX.Table

Set catDB = New ADOX.Catalog
' Open the catalog.
catDB.ActiveConnection = "Provider=Microsoft.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("ContactId").ParentCatalog = catDB;
.Item("ContactId").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.Append tbl

Set catDB = Nothing
End Sub

This solution was posted by Josef Blösl on 02/13/2004 in a reply to my reply about the same subject. My solution was somewhat different so if the above 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 property. 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:uN**************@tk2msftngp13.phx.gbl...
Previously posted on microsoft.public.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 'Item'
is 'ReadOnly'

The line triggering the error is:
.Item("ContactId").Properties("AutoIncrement") = True

I'm using MDAC 2.8 and VB.NET

How do I create AutoIncrement column when creating new table using ADOX ?
Thanks
Harry

This is the Sample I tried...

http://msdn.microsoft.com/library/de...cesstables.asp

Sub CreateAutoNumberField(strDBPath As String)
Dim catDB As ADOX.Catalog
Dim tbl As ADOX.Table

Set catDB = New ADOX.Catalog
' Open the catalog.
catDB.ActiveConnection = "Provider=Microsoft.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("ContactId").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.Append tbl

Set catDB = Nothing
End Sub






"Art" <ar*****@yahoo.com> wrote in message
news:E9**********************************@microsof t.com... 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

Nov 20 '05 #2

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("Reference").ParentCatalog = cat
..Item("Reference").Properties("AutoIncrement").Va lue = True

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

..Item("Reference").Properties.Item("Seed").Value = 1 'or whatever your
preference
..Item("Reference").Properties.Item("Increment").V alue = 1 'or whatever your
preference

Regards
Harry

"Frank Hickman" <fhickman_nosp@m_noblesoft.com> wrote in message
news:PN********************@comcast.com...
Try adding this line prior to setting the property...

.Item("ContactId").ParentCatalog = catDB;

My VB is a little rusty so you may have to tweak it :)
Sub CreateAutoNumberField(strDBPath As String)
Dim catDB As ADOX.Catalog
Dim tbl As ADOX.Table

Set catDB = New ADOX.Catalog
' Open the catalog.
catDB.ActiveConnection = "Provider=Microsoft.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("ContactId").ParentCatalog = catDB;
.Item("ContactId").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.Append tbl

Set catDB = Nothing
End Sub

This solution was posted by Josef Blösl on 02/13/2004 in a reply to my reply about the same subject. My solution was somewhat different so if the above 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 property. 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:uN**************@tk2msftngp13.phx.gbl...
Previously posted on microsoft.public.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 'Item'
is 'ReadOnly'

The line triggering the error is:
.Item("ContactId").Properties("AutoIncrement") = True

I'm using MDAC 2.8 and VB.NET

How do I create AutoIncrement column when creating new table using ADOX ?
Thanks
Harry

This is the Sample I tried...

http://msdn.microsoft.com/library/de...cesstables.asp

Sub CreateAutoNumberField(strDBPath As String)
Dim catDB As ADOX.Catalog
Dim tbl As ADOX.Table

Set catDB = New ADOX.Catalog
' Open the catalog.
catDB.ActiveConnection = "Provider=Microsoft.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("ContactId").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.Append tbl

Set catDB = Nothing
End Sub






"Art" <ar*****@yahoo.com> wrote in message
news:E9**********************************@microsof t.com... 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

Nov 20 '05 #3
Also this old post may be useful...
"Miha Markic" <miha at rthand com> wrote in message
news:OT**************@TK2MSFTNGP10.phx.gbl...
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

http://support.microsoft.com/default...Product=vcSnet and
Managing an @@IDENTITY Crisis

http://msdn.microsoft.com/library/de...anidcrisis.asp --
Miha Markic - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com
"harry" <harry@nospam> wrote in message
news:OX**************@TK2MSFTNGP11.phx.gbl...
Hi,

I am unable to insert new rows into an SQL table with a Identity Column (The
Identity column is also the Primary Key).

The table is a new empty table, and the Identity column is set to Seed=1

and
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



"Art" <ar*****@yahoo.com> wrote in message
news:E9**********************************@microsof t.com... 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

Nov 20 '05 #4
Also this old post may be useful...
"Miha Markic" <miha at rthand com> wrote in message
news:OT**************@TK2MSFTNGP10.phx.gbl...
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

http://support.microsoft.com/default...Product=vcSnet and
Managing an @@IDENTITY Crisis

http://msdn.microsoft.com/library/de...anidcrisis.asp --
Miha Markic - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com
"harry" <harry@nospam> wrote in message
news:OX**************@TK2MSFTNGP11.phx.gbl...
Hi,

I am unable to insert new rows into an SQL table with a Identity Column (The
Identity column is also the Primary Key).

The table is a new empty table, and the Identity column is set to Seed=1

and
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



"Art" <ar*****@yahoo.com> wrote in message
news:E9**********************************@microsof t.com... 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

Nov 20 '05 #5
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
Nov 20 '05 #6
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
Nov 20 '05 #7
Art
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
Nov 20 '05 #8
Art
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
Nov 20 '05 #9
Art
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
Nov 20 '05 #10
Art
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
Nov 20 '05 #11
On Thu, 8 Apr 2004 13:51:05 -0700, Art <ar*****@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 ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)
Nov 20 '05 #12
On Thu, 8 Apr 2004 13:51:05 -0700, Art <ar*****@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 ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)
Nov 20 '05 #13

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Jamie Fryatt | last post by:
Hi everyone, here's what id like to do. I have a table with 2 fields, name and value I need to be able to add multiple records quickly, for example I need to add name value abc 1...
11
by: Bobbak | last post by:
Hello All, I have these tables (lets call it ‘EmpCalls', ‘EmpOrders', and ‘Stats') that each contain the list of EmployeeIDs, I want to be able to create a Module in which I could call in my VB...
5
by: Sami | last post by:
Please bear with me, and if you answer this question, please do it step by step. I am new at Access, not at all sophisticated. I am using Office XP. This will need to be read in Access for...
2
by: Robin S. | last post by:
This is an "Add product" form. The user will enter a ProductNo (catalog number), select a Product Class (from cascading combo boxes) and then click a button to create the product. When a...
3
by: news.microsoft.com | last post by:
Hi, I am having trouble adding new rows to an empty MS Access table. I manually created the table in MS access named server and created two columns Date & Time. When I run the following code I...
11
by: Art | last post by:
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...
8
by: TORQUE | last post by:
Hi, I am having some trouble with recording a field on a form into my Table after formatting it to calculate several fields on the form. If i just put the amount in the field and have it linked...
7
by: Miro | last post by:
Im a VB Newbie so I hope I'm going about this in the right direction. I have a simple DB that has 1 Table called DBVersion and in that table the column is CurVersion ( String ) Im trying to...
2
by: Steve | last post by:
I have zero experience with ODBC. If I have an Access frontend connected to a SQL Database using ODBC, are the tables connected like a frontend/backend Access database where the the tables you see...
0
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
0
tracyyun
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
2
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
4
NeoPa
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
3
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
3
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
0
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
0
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
3
SueHopson
by: SueHopson | last post by:
Hi All, I'm trying to create a single code (run off a button that calls the Private Sub) for our parts list report that will allow the user to filter by either/both PartVendor and PartType. On...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.